Splunk Search

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

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

 

Thanks in Advance 

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@vrmandadi 

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

View solution in original post

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

0 Karma

vrmandadi
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)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

@vrmandadi 

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

0 Karma

vrmandadi
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 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@vrmandadi 

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

vrmandadi
Builder

Thank You..this worked

0 Karma

richgalloway
SplunkTrust
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.
0 Karma

richgalloway
SplunkTrust
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.
0 Karma

vrmandadi
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

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...