If you’ve read some of my previous posts you would know by now that something I started splunking for security, and even environmental, purposes a long while back was badge data from badge entry devices. The use of this data can provide many different use cases for security analysts and intellignece programs, here is one specific use case I outlined at the start of this year that addressed an issue that made national headlines:
https://joshd.ca/using-splunk-to-catch-pesky-employees-outsourcing-their-job
And then here’s a post where I detailed a environmental use case:
https://joshd.ca/server-room-temps-and-the-human-effect-basic-reporting-with-splunk
So the question I’ve gotten a few times in the past is how to do this. Well I cant speak for everyone and the badge access systems they use but I can detail how to do it with Lenel OnGuard systems, because its really quite simple.
Some notes on the configuration of the system I’ll be referring to:
- Single server instance where the Lenel OnGuard software suite is installed
- Installed on Windows
- Backed by a MSSQL Express instance
There are also a couple prerequisites:
- You must have access to install software on the same machine where the Level OnGuard software suite is installed.
- You must have access to the “AccessControl” database that the Lenel software creates within MSSQL
Step 1. Install python locally on the machine
Step 2. Install the Splunk Universal Forwarder and set it up to connect and forward events to your Splunk indexer.
Step 3. Copy the attached python script found at the bottom of this article (rename it to .txt) to $SPLUNK_HOME$\bin\scripts and make it executable
Step 4. Edit the script and change the username and password variables
Step 5. Edit the inputs.conf file to create a scripted input stanza similar to the one below which will execute the newly copied script every 5 minutes:
[script://$SPLUNK_HOME\bin\scripts\lenel_onguard_access_events.py]
disabled = false
index = badge_access
interval = 300
sourcetype = badge_event
NOTE: The script is written so that at time of execution it will query for all events 5mins old and newer so that no events are missed since the last execution. If you wish to change this interval you will need to make changes to the python script as well.
Step 6. Restart the Splunk UF and watch the events start to flow.
Now I can’t predict if you’re going to run in to any errors or not but one issue I could potentially see is if your database tables have events stored in a different format than the one configured by the installation of my software suite. I dont expect this to happen though because it would be silly for Lenel to make any major changes to their schemas from install to install, so I just expect it to be a default schema amongst the majority of installations.
However if a situation does occur where the SELECT statements are not properly formatted, then the more time consuming process that I performed was:
1. Turn on profiling on the SQL Server, or use a free tool, good discussion about that can be found here:
http://stackoverflow.com/questions/123781/logging-all-queries-on-a-sql-server-2008-express-database
2. Open Lenel OnGuard and start to generate reports. The SELECT’s will now start to fire and the profiling engine should display them.
3. Extract those SELECT calls and add them to the script, note the format of the output of the SELECT.
Enjoy and please comment if there are any issues or question. This post will be updated in the coming days with dashboards and searches I’ve prebuilt for the Lenel OnGuard badge data.
Attachments:
lenel_onguard_access_events.py