Splunk Search

## How to get the time difference between consecutive events of a host and then do the average of it?

Builder

I am using the below search to first get the difference in time everytime I see an event which has boot timestamp in it and using it first get the difference and then get the average of it by host.I am able to get the result correctly if I do one host per search like host=abc but if I use a wildcharacter for all hosts then I see the results are different (host=*) .I am assuming someother hosts having the events at same time is causing the issue .How to get the correct results for all hosts at a time .

I get the time value as 11:50:58.59 if I use only host=abc but when I want to list all hosts (host=*.)for host abc I see value 00:18:18.67

index=abc "Boot timestamp" host=abc
| eval _time=strptime(Boot_Time,"%Y-%m-%d %H:%M:%S")
| reverse
| delta _time as difference_secs
| table _time difference_secs host
| stats avg(difference_secs) as average by host | eval average=round(average,2)
| eval time=tostring(average, "duration")

is it possible to get all hosts average or it can be only individual .

Labels (1)
• ### other

1 Solution
SplunkTrust

Here is an example search you can run that will create a random set of events for 5 hosts and calculate the difference between each pair of events per host and then calculate the average for each host.

You can run this in the search window and see how it works.

If this does not reflect what you are trying to do, please clarify and provide some data examples.

``````| makeresults
``` Create 5 hosts ```
| eval host=split("A,B,C,D,E",",")
| mvexpand host
``` For each host create a random number of events up to 8 ```
| eval count=random() % 6 + 2
| eval events=mvrange(0, count, 1)
| mvexpand events
``` Now calculate a time for each host/event going back in time ```
| eval _time=now() - (random() % 43200) - (events * 86400)
| sort - _time
``` Calculate difference between each PAIR of events ```
| streamstats window=2 global=f range(_time) as range by host
``` Format the number of seconds as Days, Hours. Mins, Secs ```
| eval diff=tostring(range, "duration")
``` Now calculate the average for each host excluding the first event as it has 0 value ```
| stats list(_time) as BootTimes list(range) as range list(diff) as diff avg(eval(if(range>0, range, null()))) as Avg by host
``` Some formatting for ease of reading ```
| eval BootTimes=strftime(BootTimes, "%F %T")
| eval Avg=tostring(round(Avg), "duration")``````
SplunkTrust

Hi

1st it's not a best practice to replace _time with some other values. Of course you can and should do it time but time but only if there is no other way.

Your issue with multiple host is command delta. It just calculates values based on _time/Boot_Time without connection to an individual host. For that reason it works with one host but not with many.

You could try something like this with several hosts

``````index=abc "Boot timestamp" host=*
| eval btEpoch=strptime(Boot_Time,"%Y-%m-%d %H:%M:%S")
| stats range(btEpoch) as difference_secs by host
| stats avg(difference_secs) as average by host
| eval average=round(average,2)
| eval time=tostring(average, "duration")``````

If there are many "Boot timestamp" events then maybe streamstats with some window/reset parameter could work better?

r. Ismo

Builder

@isoutamo   I did try your search but it is working if there are only two  events of Boot time...if there are more than two then its showing wrong value .I am not sure how streamstats work...can you let me know how it works.

Thanks

Tags (1)
SplunkTrust

Note that when using streamstats with window=X you MUST use global=f when splitting by a field, otherwise it will not keep separate range values for each host.

If you have 3 events, what calculations do you expect to occur between the 3 boot time values?

Using the example given with window=2 will calculate the difference between any pair of values, but note my comment above about using global=f

Builder

@bowesmana @isoutamo @richgalloway @@I am sorry if I have not explained it correctly...But I am looking to find difference between all the events with boot time for each host..it might be two or three or ten depending on time range selected..So for instance if a host A has 10 events and host B has 6 events and host C has 5 events I want to  find firstly the time difference between each event for a particular host and then do the average of it ..I get correct value if I do this for single host but when I do same by selecting all hosts then it doesn't shows wrong value

SplunkTrust

Here is an example search you can run that will create a random set of events for 5 hosts and calculate the difference between each pair of events per host and then calculate the average for each host.

You can run this in the search window and see how it works.

If this does not reflect what you are trying to do, please clarify and provide some data examples.

``````| makeresults
``` Create 5 hosts ```
| eval host=split("A,B,C,D,E",",")
| mvexpand host
``` For each host create a random number of events up to 8 ```
| eval count=random() % 6 + 2
| eval events=mvrange(0, count, 1)
| mvexpand events
``` Now calculate a time for each host/event going back in time ```
| eval _time=now() - (random() % 43200) - (events * 86400)
| sort - _time
``` Calculate difference between each PAIR of events ```
| streamstats window=2 global=f range(_time) as range by host
``` Format the number of seconds as Days, Hours. Mins, Secs ```
| eval diff=tostring(range, "duration")
``` Now calculate the average for each host excluding the first event as it has 0 value ```
| stats list(_time) as BootTimes list(range) as range list(diff) as diff avg(eval(if(range>0, range, null()))) as Avg by host
``` Some formatting for ease of reading ```
| eval BootTimes=strftime(BootTimes, "%F %T")
| eval Avg=tostring(round(Avg), "duration")``````
Builder

Thank You..this worked

SplunkTrust

Using streamstats window=2 as described in the first reply will give you the difference between adjacent events.  You than can use stats avg() to get the average of those differences.

---
If this reply helps you, Karma would be appreciated.
SplunkTrust

Use the streamstats command to look at only two events.

``````index=abc "Boot timestamp" host=*
| eval btEpoch=strptime(Boot_Time,"%Y-%m-%d %H:%M:%S")
| streamstats window=2 range(btEpoch) as difference_secs by host
| stats avg(difference_secs) as average by host
| eval average=round(average,2)
| eval time=tostring(average, "duration")``````

---
If this reply helps you, Karma would be appreciated.
Builder

@richgalloway  I dont want to look at just 2 events..I want to look at all the events in that selected time range and then do the difference and average for each host

SplunkTrust

On @richgalloway 's example it use two sequential events by host and calculates difference between those times as you have asked. As you have used filter "Boot timestamp" we have assumed that there is not any other events than those.

So I think that this should be work as you have asked or otherwise there are something what we didn't know about your events.

Get Updates on the Splunk Community!

#### 3 Ways to Make OpenTelemetry Even Better

My role as an Observability Specialist at Splunk provides me with the opportunity to work with customers of ...

#### What's New in Splunk Cloud Platform 9.2.2406?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2406 with many ...

#### Enterprise Security Content Update (ESCU) | New Releases

In August, the Splunk Threat Research Team had 3 releases of new security content via the Enterprise Security ...