Splunk Search

How do I calculate the total time of employees from security card system?

scottrunyon
Contributor

I would like to create a report to verify when and how long each employee is in the building. Splunk indexes data from the Security system that supplies a cvs file nightly. I am running into a problem because each reader has entry and exit side but the employee can come in one door and exit a totally different door. Is there a way to correlate entry and exit for an employee, calculate the duration of that stay and then calculate the total of time that the employee is in the building, assuming that the first event is an entry, second event is exit, third event is entry, forth event is exit, etc.?

Indexed data look like this -

Timestamp, EventTable, extractedEventType, Controller, Full Name
2018-08-23 06:02:50.247,Events_268,515-0    ,VertX A-Interface 0-Reader 1, Barney Rubble
2018-08-23 07:14:53.500,Events_268,515-0    ,VertX B - V100 0 - Reader 2, Fred Flintstone
2018-08-23 09:19:05.897,Events_268,515-0    ,VertX A-Interface 0-Reader 1, Barney Rubble
2018-08-23 10:29:17.097,Events_268,515-0    ,VertX B - V100 4 - Reader 1, Fred Flintstone
2018-08-23 10:55:40.503,Events_268,515-0    ,VertX A-Interface 0-Reader 2 , Fred Flintstone
2018-08-23 10:59:22.877,Events_268,515-0    ,VertX B - V100 4 - Reader 1, Barney Rubble
2018-08-23 14:56:45.613,Events_268,515-0    ,VertX A-Interface 0-Reader 1 , Barney Rubble
2018-08-23 15:44:36.363,Events_268,515-0    ,VertX B - V100 0 - Reader 2, Fred Flintstone

What I would like to create is a report that shows

Date                  Full Name                   Total Time
2018-08-23      Barney Rubble               7.5 hours
2018-08-23      Fred Flintstone               8.0 hours
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@scottrunyon,

Try

"Your search"
|table _time, EventTable, extractedEventType, Controller, FullName|eval date=strftime(_time,"%d-%m-%Y")|sort FullName,_time
|streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
|streamstats count as rownum by FullName,date
|eval diff=_time-prev_time|fillnull value=0  diff|eval mod=rownum%2
|stats sum(eval(if(mod==0,diff,null()))) as total by FullName,date|eval total=round(total/3600,2)

EDIT

Updated to include conditions from the comment (e.g. remove small intervals)

  |inputlookup reader.csv|rename "Full Name" as FullName|eval _time=strptime(Timestamp,"%Y-%m-%d %H:%M:%S.%3N")
  |table _time, EventTable, extractedEventType, Controller, FullName|eval date=strftime(_time,"%d-%m-%Y")|sort FullName,_time|reverse
  |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
  |eval diff=prev_time-_time|fillnull value=999  diff|where diff>=5|fields - prev_time,diff|reverse
  |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
  |streamstats count as rownum by FullName,date
  |eval diff=_time-prev_time|fillnull value=0  diff|eval mod=rownum%2
  |stats sum(eval(if(mod==0,diff,null()))) as total by FullName,date|eval total=round(total/3600,2)
Happy Splunking!

View solution in original post

nyoung_splunk
Splunk Employee
Splunk Employee

In my recent case,
i just used the simpe evals and took when they entered work to when they left work,so (last - first).

|eval time=strftime(_time,"%c"), date=strftime(_time,"%x")
|streamstats earliest(_time) as "first" latest(_time) as "last" by date
|eval total=(last-first)
|eval hours=round(toatl/3600/2
|stats max(hours) by date "Full Name"

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@scottrunyon,

Try

"Your search"
|table _time, EventTable, extractedEventType, Controller, FullName|eval date=strftime(_time,"%d-%m-%Y")|sort FullName,_time
|streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
|streamstats count as rownum by FullName,date
|eval diff=_time-prev_time|fillnull value=0  diff|eval mod=rownum%2
|stats sum(eval(if(mod==0,diff,null()))) as total by FullName,date|eval total=round(total/3600,2)

EDIT

Updated to include conditions from the comment (e.g. remove small intervals)

  |inputlookup reader.csv|rename "Full Name" as FullName|eval _time=strptime(Timestamp,"%Y-%m-%d %H:%M:%S.%3N")
  |table _time, EventTable, extractedEventType, Controller, FullName|eval date=strftime(_time,"%d-%m-%Y")|sort FullName,_time|reverse
  |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
  |eval diff=prev_time-_time|fillnull value=999  diff|where diff>=5|fields - prev_time,diff|reverse
  |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
  |streamstats count as rownum by FullName,date
  |eval diff=_time-prev_time|fillnull value=0  diff|eval mod=rownum%2
  |stats sum(eval(if(mod==0,diff,null()))) as total by FullName,date|eval total=round(total/3600,2)
Happy Splunking!

scottrunyon
Contributor

Renjith, overall that query works. However, I ran into a slight problem. Some of the totals were way off, showing too little time. When I researched why, I found out that sometimes when an employee badges in or out, they get distracted and the door shuts. They then badge again on the same door, causing the time to be only a few seconds. Is there way to not see the second entry if it was within 5 seconds on the same card reader by user?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@scottrunyon,

Try if this works. I used your data as a csv and added an extra for Barney by adding 4 secs like
2018-08-23 14:56:49.613,Events_268,515-0 ,VertX A-Interface 0-Reader 1 , Barney Rubble

 |inputlookup reader.csv|rename "Full Name" as FullName|eval _time=strptime(Timestamp,"%Y-%m-%d %H:%M:%S.%3N")
 |table _time, EventTable, extractedEventType, Controller, FullName|eval date=strftime(_time,"%d-%m-%Y")|sort FullName,_time|reverse
 |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
 |eval diff=prev_time-_time|fillnull value=999  diff|where diff>=5|fields - prev_time,diff|reverse
 |streamstats current=f window=1 last(_time) as prev_time by FullName,date reset_on_change=true
 |streamstats count as rownum by FullName,date
 |eval diff=_time-prev_time|fillnull value=0  diff|eval mod=rownum%2
 |stats sum(eval(if(mod==0,diff,null()))) as total by FullName,date|eval total=round(total/3600,2)
Happy Splunking!
0 Karma

scottrunyon
Contributor

Renjith,
Adding the new lines solved the issue.

Thank you very much for the solution.

Regards,

Scott

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@scottrunyon,
Glad that it worked. You can vote (like) on the comment or I added the answer so tat you can upvote. Might be useful for others who have same requirements

Happy Splunking!
0 Karma

scottrunyon
Contributor

Renjith,

This works!! I was hung up trying to use transaction command.

Thank you.

0 Karma
Get Updates on the Splunk Community!

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...