Dashboards & Visualizations
Highlighted

How to find the first and last daily occurrence in an access log?

Explorer

I want to get the first time and last time per day that a person identified by an unique CARDID shows up in an access log. The log is in the format shown below. I want to be able to chart it showing the average access times for people and to highlight people who are significantly outside the normal range (second part is optional). If I have a list of employees in a CSV file, I would like to be able to pull the report based on teamID.

I tried: http://answers.splunk.com/answers/149904/find-earliest-and-latest-event-per-day-for-a-time-range.htm... but that is asking for a single user and I was unable to edit for my use.

Based on the post above I tried the following command: but the first and last were 00:00
host="pfcacu" sourcetype =csv |table time CARDID | bucket time span=1d | stats earliest(time) as First latest(_time) as Last by "Card Number" | eval First=strftime(First,"%H:%M") | eval Last=strftime(Last,"%H:%M")

1,Access Granted,3,LOCATION_NAME,UNIT2,SITE_NAME,11/13/2014 15:39:00,000,CARD_ID,Fname,Lname #2,,,,,,,,,,,,,,,,,,
1,Access Granted,2,LOCATION_NAME,UNIT4,SITE_NAME,11/13/2014 15:40:18,000,CARD_ID,Fname,Lname ,,,,,,,,,CS,,,,,,,,,
1,Access Granted,2,LOCATION_NAME,UNIT4,SITE_NAME,11/13/2014 15:41:11,000,CARD_ID,Fname,Lname #2,,,,,,,,,,,,,,,,,,
1,Access Granted,3,LOCATION_NAME,UNIT3,SITE_NAME,11/13/2014 15:43:25,000,CARD_ID,Fname,Lname ,,,,,,,,,,,,,,,,,,
Tags (3)
Highlighted

Re: How to find the first and last daily occurrence in an access log?

Legend

Here is "the first time and last time per day that a person identified by an unique CARD_ID shows up in an access log"

host="pfcacu" sourcetype =csv 
|  stats earliest(_time) as First latest(_time) as Last by CARD_ID
| eval First=strftime(First,"%H:%M") | eval Last=strftime(Last,"%H:%M")

To chart the time - assuming that by "time" you mean the time range defined by the first and last access:

host="pfcacu" sourcetype =csv 
|  stats earliest(_time) as First latest(_time) as Last by CARD_ID
|  eval TotalTimeInMinutes=round((Last-First)/60,0)
| lookup user_lookup CARD_ID output user_name
| chart avg(TotalTimeInMinutes) as TotalTime by user_name
| eventstats avg(TotalTime) as AvgTime

Both of these searches are designed to be run for a single day. To run over multiple days, you need to decide - do you want to see the average across multiple days, or do you want to see each day separately? Assuming that you want to see the average across multiple days:

host="pfcacu" sourcetype =csv 
|  eval day=round(_time/86400,0)
|  stats earliest(_time) as First latest(_time) as Last by CARD_ID day
|  eval TotalTimeInMinutes=round((Last-First)/60,0)
| lookup user_lookup CARD_ID output user_name
| chart avg(TotalTimeInMinutes) as TotalTime by user_name
0 Karma
Highlighted

Re: How to find the first and last daily occurrence in an access log?

Explorer

Really close to what I need.

host="pfcacu" sourcetype =csv date_month=november date_mday=14
 |  eval day=round(_time/86400,0)
 |  stats earliest("Alarm Date Time") as "Entry Time" latest("Alarm Date Time") as "Exit Time" by "Card Number", "Last Name", "First Name"

And I get something like this:

01450   Jane    Doe 11/14/2014 08:57:00 11/14/2014 17:03:13
01452   Jill    Moe 11/14/2014 06:51:00 11/14/2014 15:57:00
01453   Jeff    Boe 11/14/2014 09:00:00 11/14/2014 15:41:58
01456   John    Roe 11/14/2014 08:29:00 11/14/2014 17:29:37
01457   Pat Bro 11/14/2014 08:17:16 11/14/2014 13:29:26

I have tried to chart the entry times using: timechart max(First) by "Card Number" but I get no results.

0 Karma
Highlighted

Re: How to find the first and last daily occurrence in an access log?

Legend

The reason that you get nothing may be that you have no field named "First".
Also, the timechart command uses the internal _time field for the X-axis. If you have already manipulated the data so that there is no _time field, it isn't going to work.
If you want to chart the entry times, try this:

host="pfcacu" sourcetype =csv date_month=november date_mday=14
|  stats earliest("Alarm Date Time") as "Entry Time" latest("Alarm Date Time") as "Exit Time" by "Card Number", "Last Name", "First Name"
| eval entryTime=strptime("Entry Time","%x %X")
| eval exitTime=strptime("Exit Time","%x %X")
| eval duration=exitTime-entryTime
| xyseries entryTime "Card Number" duration
0 Karma
Highlighted

Re: How to find the first and last daily occurrence in an access log?

SplunkTrust
SplunkTrust

Give this a try

host="pfcacu" sourcetype =csv date_month=november 
  |  eval swipetime=strftime(_time,"%H.%M") | timechart span=1d min(swipetime) as Entry max(swipetime) as Exit by _time,CARD_ID 

This should give you Entry and Exit time for every CART_ID in with Hour and Minute in decimal which can be plotted.

sample output

_time              CARD_ID1:Entry      CARD_ID1:Exit     CARD_ID2:Entry      CARD_ID2:Exit....
11/14/2014 00:00      8.20             17.30                   6.20                15.30 .....

where 8.20 is 08:20 AM.

View solution in original post