topic Re: Find earliest and latest event per day for a time range in Dashboards & Visualizations

Find earliest and latest event per day for a time range

conor_splunk — Thu, 07 Aug 2014 23:23:02 GMT

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.

Re: Find earliest and latest event per day for a time range

somesoni2 — Fri, 08 Aug 2014 00:54:33 GMT

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,"%+")

Re: Find earliest and latest event per day for a time range

gkanapathy — Fri, 08 Aug 2014 01:05:40 GMT

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

Re: Find earliest and latest event per day for a time range

somesoni2 — Fri, 08 Aug 2014 02:18:15 GMT

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.

Re: Find earliest and latest event per day for a time range

gkanapathy — Fri, 08 Aug 2014 05:02:22 GMT

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

Re: Find earliest and latest event per day for a time range

conor_splunk — Mon, 28 Sep 2020 17:17:37 GMT

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