Solved

SQL Query, Lenel Onguard

Posted on 2013-06-04
7
2,424 Views
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):

http://forums.securityinfowatch.com/showthread.php?10054-Integrating-with-Lenel-OnGuard-via-SQL-or-Other


thanks.
0
Comment
Question by:freezingHot
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:lcohan
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, b.id as badge_num, b.active
from emp e inner join badge b  on b.empid = e.id


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

http://forums.securityinfowatch.com/showthread.php?10054-Integrating-with-Lenel-OnGuard-via-SQL-or-Other
0
 
LVL 1

Author Comment

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

thanks.
0
 
LVL 39

Accepted Solution

by:
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:

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

--2.
select b.id as badge_num, b.active from badge b where b.active = 1


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

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

--to get a list with inactive badges:

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

--to get a full list run:


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


Just please double check my logic against badge table in the SQL database to make sure I remembered active column correctly
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Closing Comment

by:freezingHot
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.
0
 
LVL 1

Author Comment

by:freezingHot
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:

http://cdn.lenel.com/oaap/DataConduIT.pdf


thanks again.
0
 
LVL 39

Expert Comment

by:lcohan
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
>>
0
 
LVL 1

Author Comment

by:freezingHot
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:

badge
  ID
  EMPID
  Status
  PIN (varbinary(50)) <- i'm assuming they encrypt the PIN somehow

table for employees:

emp
  ID
  lastName
  firstName
  midName

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
Viewers will learn how to use the Hootsuite Dashboard.

777 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