Getting Data In

Time difference between first and last events of a search

cafissimo
Communicator

Hello, I would like to know if and how is it possible to find and put in a field the difference (in time: seconds, hours or minutes does not matter) between the first and the last event of a certain search.

Thanks in advance and kind regards,

Luca Caldiero Consoft Sistemi S.p.A.

Tags (3)
1 Solution

ftk
Motivator

You could do something like

your search | stats max(_time) as maxtime min(_time) as mintime | eval difference=maxtime-mintime | eval difference=strftime(difference,"%d:%H:%M:%S")

Which would output the difference in days:hours:minutes:seconds

You could leave the last eval with the strftime off and difference will be in seconds.

View solution in original post

ktaylor07
New Member

source = WinEventLog:Security EventCode=4768 user="joe.blow" | stats range(_time) as difference | eval hours = difference/3600

I wish I could get a weekly breakdown and total......,source = WinEventLog:Security EventCode=4768 user="joe.blow" | stats range(_time) as difference | eval hours = difference/3600

Now if I could only do it over multiple days......

0 Karma

Ron_Naken
Splunk Employee
Splunk Employee

You could probably use the "transaction" command's built-in duration calculation to measure the time between events. A couple quick searches to grab the first and last events will alleviate any worries about how many events you can store in a transaction. Try something like this:

sourcetype=mydata | head 1 | append [search sourcetype=mydata | tail 1] | transaction sourcetype maxspan=-1 maxpause=-1

This should yield a transaction with a duration field (in seconds) that defines the measurement you're looking for. I use maxspan=-1 and maxpause=-1 to disable the respective segmentation -- ensuring the two events are combined into a single transaction, despite their distance from one another.

HTH
Ron

cafissimo
Communicator

Thank you, I'll test it asap.

0 Karma

ftk
Motivator

You could do something like

your search | stats max(_time) as maxtime min(_time) as mintime | eval difference=maxtime-mintime | eval difference=strftime(difference,"%d:%H:%M:%S")

Which would output the difference in days:hours:minutes:seconds

You could leave the last eval with the strftime off and difference will be in seconds.

Ron_Naken
Splunk Employee
Splunk Employee

The reason the above search doesn't work is because of the second eval expression. The difference between two EPOCH timestamps doesn't yield an EPOCH timestamp, so you can't use strftime on the "difference". The first eval expression should properly measure the seconds between events.

Lowell
Super Champion

BTW, you could use: | stats range(_time) as difference and skip a step. Another thought, instead of using strftime, you could always used a sequence of eval and divisions to break the value down to days/hours/minutes/seconds and then concatenate whatever string format you want. It's messy, but it should get the job done.

0 Karma

sideview
SplunkTrust
SplunkTrust

Yep - strftime is just interpreting the difference as though it were a time around Jan 1st 1970, so it will naturally incorporate the timezone offset when it displays the time; it has no idea that you're really looking for a 'duration' quantity. I cant think of a way in general to normalize away the timezone delta, but you can of course subtract it in the search before giving it to strftime, if you happen to know the number yourself..

0 Karma

cafissimo
Communicator

I've tried your search and I get 01:04:50:02 as difference, but the real differnce should be 03:50:02. Consider that my clock is GMT+1. Could that be an issue maybe?

Dates are:
01/21/2011 01:21:27 PM
01/21/2011 5:11:29 PM

If I leave the last eval the value in seconds (13802) is absolutely correct!

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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