Find earliest and latest event per day for a time range

[conor_splunk]

I would like to find the first and last event per day over a given time range. So far I have figured out how to find just the first and last event for a given time range but if the time range is 5 days I'll get the earliest event for the first day and the last event on the last day. I'm just using the _time field to sort the date. I've tried the following.

index="security" user="123456" EventCode=4624 OR EventCode=4634 earliest=-7d@d | stats earliest(_time) AS Earliest, latest(_time) AS Latest | eval FirstEvent=strftime(Earliest,"%+") | eval LastEvent=strftime(Latest,"%+")

This produces kind of what I want except over the 7 day time range.

FirstEvent                      LastEvent
Fri Aug 1 11:10:25 EST 2014     Fri Aug 8 05:57:06 EST 2014

If someone could help me figure out how to do it day by day like below that would be excellent.

FirstEvent                      LastEvent
Fri Aug 1 11:10:25 EST 2014     Fri Aug 1 16:57:06 EST 2014
Fri Aug 2 11:10:25 EST 2014     Fri Aug 2 16:57:06 EST 2014
Fri Aug 3 11:10:25 EST 2014     Fri Aug 3 16:57:06 EST 2014
Fri Aug 4 11:10:25 EST 2014     Fri Aug 4 16:57:06 EST 2014

And so on.

[gkanapathy · Accepted solution]

index="security" user="123456" EventCode=4624 OR EventCode=4634 earliest=-7d@d 
| bucket _time span=1d
| stats earliest(_time) as First latest(_time) as Last
  by _time
| eval First=strftimet(First,"%+") | eval Last=strftime(Last,"%+")

I prefer not to use date_* fields (and instead use _time) because those are not time-zone corrected, and they are not guaranteed to be on every event. (They will only be on events where a timestamp is extracted; ones that inherit a timestamp from a previous event or get a current timestamp won't have them.)

---

Above is wrong. It needs to be:

index="security" user="123456" EventCode=4624 OR EventCode=4634 earliest=-7d@d 
| bucket _time span=1d as day
| stats earliest(_time) as First latest(_time) as Last
  by day
| eval First=strftimet(First,"%H:%M:%S") | eval Last=strftime(Last,"%H:%M:%S") | eval day=strftime(day,"%Y/%m/%d")

[conor_splunk]

Neither of these quite worked for me but put me on the right track. I ended up doing what gkanapathy had without the bucket.

index="security" user="123456" EventCode=4634 OR EventCode=4624)
| eval Date=strftime(_time, "%d/%m/%Y")
| stats earliest(_time) AS Earliest, latest(_time) AS Latest by Date
| eval Logon=strftime(Earliest,"%+")
| eval Logoff=strftime(Latest,"%+")
| table Date, Logon, Logoff

[gkanapathy]

You are right, need to copy _time to a new field when bucketing it.

[somesoni2]

That is true about date_* fields. Here, wouldn't all _time values will be transform into %Y/%m/%d 00:00 AM, and earliest and latest value of _time will be same.

[somesoni2]

Try something like this

index="security" user="123456" EventCode=4624 OR EventCode=4634 earliest=-7d@d | stats earliest(_time) AS Earliest, latest(_time) AS Latest by date_mday | eval FirstEvent=strftime(Earliest,"%+") | eval LastEvent=strftime(Latest,"%+")

Above should work for Windows security logs. Alternatively, this can be used too.

index="security" user="123456" EventCode=4624 OR EventCode=4634 earliest=-7d@d | eval date_mday=strftime(_time,"%F") | stats earliest(_time) AS Earliest, latest(_time) AS Latest by date_mday | eval FirstEvent=strftime(Earliest,"%+") | eval LastEvent=strftime(Latest,"%+")