topic Re: Calculate average time between events for a series with a unique identifier in Splunk Search

Calculate average time between events for a series with a unique identifier

mikfro — Fri, 25 Aug 2023 07:44:34 GMT

We have logs of images created in a series, like below. They are identified by a unique series id, the number of events for each series is variable.

time_1 image_number:1 series_id:99999
time_2 image_number:2 series_id:99999
time_3 image_number:3 series_id:99999
time_n image_number:n series_id:99999

I need to calculate the average time for an image created, i.e. the total time (time_n - time_1)/n for each series. We have thousands of series every day.

Any tips on how I can achieve this?

Re: Calculate average time between events for a series with a unique identifier

bowesmana — Fri, 25 Aug 2023 08:17:03 GMT

Well, you can do this

your search... | stats count range(_time) as duration by series_id | eval avg=duration/count

but that will give you a misleading average, as if you have 4 events in your example, created at

1pm, 2pm, 3pm, 4pm

then the range is 3 hours, so the average is 45 minutes, but if the message is written AFTER the image is created, it won't take account of the duration of image 1.

Note: This assumes you have a field called series id extracted from the data.

If not, extract it with

| rex "series_id:(?<series_id>\d+)"

Re: Calculate average time between events for a series with a unique identifier

ITWhisperer — Fri, 25 Aug 2023 09:21:39 GMT

Assuming time_1 is when the image is started and time_n is when the image is complete, then the average image completion time can be calculated like so.

| stats range(_time) as duration by series_id | stats avg(duration) as average_image_creation_time