topic Re: How to get the time difference between consecutive events of a host and then do the average of it? in Splunk Search

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

vrmandadi — Fri, 23 Sep 2022 00:37:43 GMT

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

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

isoutamo — Fri, 23 Sep 2022 07:05:10 GMT

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

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

r. Ismo

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

vrmandadi — Fri, 23 Sep 2022 11:37:48 GMT

@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

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

richgalloway — Fri, 23 Sep 2022 11:53:17 GMT

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

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

vrmandadi — Fri, 23 Sep 2022 12:06:28 GMT

@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

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

isoutamo — Fri, 23 Sep 2022 19:22:27 GMT

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.

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

bowesmana — Sat, 24 Sep 2022 03:55:41 GMT

@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

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

vrmandadi — Sat, 24 Sep 2022 16:00:21 GMT

@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

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

richgalloway — Sun, 25 Sep 2022 00:24:55 GMT

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.

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

bowesmana — Mon, 26 Sep 2022 01:04:46 GMT

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

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

vrmandadi — Mon, 26 Sep 2022 13:44:35 GMT

Thank You..this worked