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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...