Dashboards & Visualizations

Find earliest and latest event per day for a time range

conor_splunk
Path Finder

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.

0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee
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")

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee
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
Path Finder

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
Splunk Employee
Splunk Employee

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

0 Karma

somesoni2
Revered Legend

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
Revered Legend

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,"%+")
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...