topic Re: How to take an average of a time log? in Splunk Search

How to take an average of a time log?

amaralt808 — Tue, 12 Sep 2023 23:02:58 GMT

Hello peeps,

Currently I have a list of processing times. And I am trying to create a dashboard that shows the average time, max time, and the count of how many times that action is processed.

index=IndexA | stats avg(cTotal) max(cTotal) count(cTotal) by name | sort 10 -count(cTotal)

Totaltime is a given header and an example is

When I run the script, I get accurate results for count and max, but the avg is not currently working.

I'm thinking it's because of the time string (hours, min, sec, milisec) but if anyone has any advice on how to make the average work, I would love to hear it!

Re: How to take an average of a time log?

PickleRick — Fri, 08 Jul 2022 19:08:31 GMT

Count is easy - splunk can count anything 😉

max probably shows you lexicographic max. If you have leading zeros it will accidentally be the same as arithmetic max but if you didn't, 012:34:56 would be "less" than 9:12:23.

avg doesn't work because you can't calculate numeric functions on strings. You have to parse the strings to numbers first.

Re: How to take an average of a time log?

amaralt808 — Fri, 08 Jul 2022 19:15:07 GMT

Hello @PickleRick,

That makes sense, do you have a suggestion about how I can pass that time format as a string?

I tried this code previously,

index=IndexA
| eval cTotal = strftime(strptime(totaltime, "%H:%M:%S.%f"), "%S.%f")
| stats avg(cTotal) max(cTotal) count(cTotal) by name
| sort 10 -count(cTotal)

But I realized that the cTotal only has the seconds and miliseconds.

Is there like a best practice to convert "%H:%M:%S.%f" format into a string?

Sorry about this, I am beginning my journey into Splunk

Re: How to take an average of a time log?

PickleRick — Fri, 08 Jul 2022 19:49:02 GMT

You had a pretty decent idea with strptime. But unnecessarily you're trying to render it back to string with strftime. At this point just gomwith strptime and let it be a number.

Then you can do your calculations (max, avg) and only at the end do strftime/convert/tostring to get a human-readable string.

The rule of thumb is that if you're doing anything with time, you want to have it as a number of seconds (with absolute timestamps as number of seconds since epoch, with duration-oriented fields, just duration expressed in seconds). This way you can easily manipulate those fields.

Only at the end - for presentation - you might want to render them to strings.

Re: How to take an average of a time log?

amaralt808 — Fri, 08 Jul 2022 20:45:33 GMT

Thank you @PickleRick,

I have another question, I don't know if I did this correctly.

Here is my code
index=IndexA
| eval cTotal = strptime(totaltime, "%H:%M:%S.%f")
| stats avg(cTotal) max(cTotal) count(cTotal) by name
| sort 10 -count(cTotal)

Here are my results with the strip time and the same process in a different search with the actual time.

The times in the new search don't seem to match.
Any help would be appreciated

Re: How to take an average of a time log?

amaralt808 — Fri, 08 Jul 2022 21:27:27 GMT

Re: How to take an average of a time log?

PickleRick — Sun, 10 Jul 2022 09:33:42 GMT

Indeed. I forgot that if you don't have the date part specified in your strptimed time string, splunk will assume "today". Which makes the values quite large 🙂

I'm not sure there is a ready-made function for converting string-expressed durations to number. You might want to split the string by colon and multiply each number by 60 and 3600 respectively.

Re: How to take an average of a time log?

amaralt808 — Tue, 12 Jul 2022 18:47:48 GMT

Thank you!

I followed your advice and manually split them up by doing math.

Here is the code that ended up working.

| eval cTotal = strptime(totaltime, "%H:%M:%S.%f")
| rex field=totaltime "(?<totaltimeH>\d+):(?<totaltimeM>\d+):(?<totaltimeS>.*)" | eval totaltime=totaltimeS + 60 * (totaltimeM + 60 * (totaltimeH)) |
stats avg(totaltime) max(totaltime) count(totaltime) by name
| sort 10 -count(totaltime)