SQL Query, Lenel Onguard

Posted on 2013-06-04
Last Modified: 2013-11-15
good day.

can someone please help me with simple queries running against a Lenel, Onguard database?

I would like to perform the following (data reading only):

1.  retrieve a list of users and their badge number (last name, first name, badge #)
2.  find out if the badge # is currently active

your help would be greatly appreciated.

i did find this article, however, it isn't exactly what i need (especially finding out if the badge is active):

Question by:freezingHot
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40

Expert Comment

ID: 39219960
Before providing any query support what database are you using to support your product - SQL or ORACLE? As far as I'm aware OnGuard 2010 supports SQL 2008, and Oracle 11.

Actualy on bothe DBs you would need to run a query like below and that would give you bothe answers in one shot:

select e.lastname, e.firstname, as badge_num,
from emp e inner join badge b  on b.empid =

You could also subscribe and post questions about speciffic queries at the forum you mentioned above.

Author Comment

ID: 39223300
it is a microsoft sql d/b with onguard 2010.

LVL 40

Accepted Solution

lcohan earned 500 total points
ID: 39223481
Then the query above must work in SQL - just please double check to make sure I got the active field right from badge table (sorry - I currently do not have the OnGuard environment up and running.)

if you need the two record sets exactly like in your posting please run:

select e.lastname, e.firstname, as badge_num
from emp e inner join badge b  on b.empid =

select as badge_num, from badge b where = 1

--to get a list of emp names with active badges:

select e.lastname, e.firstname, as badge_num,
from emp e inner join badge b  on b.empid = and = 1

--to get a list with inactive badges:

select e.lastname, e.firstname, as badge_num,
from emp e inner join badge b  on b.empid = and = 0

--to get a full list run:

select e.lastname, e.firstname, as badge_num,
      case when = 0 then 'Inactive'
            when = 1 then 'Active'
            else 'unknown' end as badge_status
from emp e inner join badge b  on b.empid =

Just please double check my logic against badge table in the SQL database to make sure I remembered active column correctly
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part


Author Closing Comment

ID: 39223531
thanks for the prompt response - I don't have access to the badge table either; however, i should be able to return the table schema, if needed.

have a good one.

Author Comment

ID: 39223642
i think the field name for the badge status is "Status."  Active = 1 like you said.

i found a dataConduIT document on Onguard.  if anyone needs something like this in the future, this document seems to have the field names:

thanks again.
LVL 40

Expert Comment

ID: 39223722
"Status" indeed - sorry I missed (forgot) that...

Find all active badges that are APB exempt:
select * from Lnl_Badge where Status=1 and APBExempt = TRUE

Author Comment

ID: 39247909
i was able to view a Lenel database (onguard 2013).  the data conduit document is fine; however, the tables name are a bit different.

the table for badges:

  PIN (varbinary(50)) <- i'm assuming they encrypt the PIN somehow

table for employees:


thought i would post in case anyone else needs the info.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question