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

[dpoloche]

I want to get the first time and last time per day that a person identified by an unique CARD_ID 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 team_ID.

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 CARD_ID | 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 ,,,,,,,,,,,,,,,,,,

[somesoni2 · Accepted solution]

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.

[lguinn2]

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

[dpoloche]

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.

[lguinn2]

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