Physical access compared to Computer Access

Contributed to a discussion on LinkedIn in the Splunk users group in response to "What's the coolest thing you've done with Splunk?"  

--- Original posts ---
I recently took door access logs and produced the avg time an employee gets to work. Created other dashboard panels showing weekend and off hour access as well as what door they most often use.

-- Response to format of door access logs ---
Sonitrol and sadly I only get the logs weekly in .csv format. They are easy to ingest into Splunk..
(The csv format looks like)
Time Activity Panel Panel Location Mod Door Door Location Last Name First Name
6/17/2013 7:25 Entry granted 5946 CORPX 5 1 ELEVATOR 2 BOB SMITH

I would like to expand on the discussion and share the dash boards and the search text behind each dashboard. In the next blog post I can break down the searches if there is enough interest.


Dashboard panel for average start time

search text:

index=door_access first_name="*" last_name="Warren" 

| eval lowerlast=upper(last_name) | eval lowerfirst=upper(first_name) 

| bucket _time span=1d as day 

| stats earliest(_time) as start_time by lowerfirst, lowerlast  

| stats avg(start_time) as avgstart_time by lowerlast,lowerfirst 

| eval avgstart_timef=strftime(avgstart_time, "%I:%M %p")  

| sort - avgstart_timef  

| table lowerfirst,lowerlast,avgstart_timef

Elevator use panel

(please forgive the double data entries)

search text:
index=door_access door_name=elevator* first_name="*" last_name="Warren*"  
| eval WeekDay=strftime(_time, "%a")  
| table _time, WeekDay,door_name

Weekend Access:

search text:
index=door_access first_name="*" last_name="Smith" 
| eval WeekDay=strftime(_time, "%a")   
| search WeekDay="Sat" OR WeekDay="Sun" 
| table _time, WeekDay, door_name

Denied Access:

search text:
index=door_access activity="*denied*" first_name="*" last_name="Smith"  
| eval WeekDay=strftime(_time, "%a")   
| table _time, WeekDay,door_name, activity

If you have gotten this far, thanks for reading.  Currently I am working on how to correlate this physical access info with workstation logons.  I work in a two-factor authentication world which uses cached credentials in conjunction with the token. I am finding it difficult to harvest exactly when a user logs into their work station in the morning..  Does any one have ideas?  I have tried using eventid=4624 with out luck.  

BTW Randy Franklin's Ultimate Windows Security is a great resource for understanding Windows Event Codes. Check it out. I have read this page 4624 way too many times.

Ross Warren, CISSP, GCIH, GSEC