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

[scottrunyon]

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

[nyoung_splunk]

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"

[renjith_nair · Accepted solution]

@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)

[scottrunyon]

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?

[renjith_nair]

@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)

[scottrunyon]

Renjith,
Adding the new lines solved the issue.

Thank you very much for the solution.

Regards,

Scott

[renjith_nair]

@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

[scottrunyon]

Renjith,

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

Thank you.