Splunk Search

How to take an average of a time log?

amaralt808
Path Finder

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!

Labels (3)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

amaralt808
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

amaralt808
Path Finder

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.

amaralt808_0-1657313124967.png

 

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

amaralt808
Path Finder

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)

0 Karma

amaralt808
Path Finder

amaralt808_1-1657315635801.png

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...