how to get average of time field?

[asifhj]

I have following values in a field(CPU)

000 00:00:00.00

000 00:00:00.03

000 00:00:43.18

000 00:00:20.69

000 00:00:00.04

000 00:00:00.01

000 00:00:00.03

I tried the following query to extract

...| eval t = strftime(strptime(CPU,"+%Q %H:%M:%S.%N"),"%H:%M:%S.%N") | stats avg(t) by host

But unable to get the results

And suggestions?

Regards

Asif J.

[martin_mueller]

You can do this to convert your duration strings into seconds:

... | eval tmp = replace(replace(duration, "\.\d+$", ""), "\s+", "+") | convert dur2sec(tmp) as converted | fields - tmp | eval converted = converted + tonumber("0.".replace(duration, "^.*\.", ""))

There's a bit of format changing required before convert dur2sec() will accept the string, but it'll work. Once you have the durations in seconds you can do any calculation you want, including stats avg(converted).

[marina_rovira]

Hi there!

I have the same question, but I didn't get your answer. What should be the value of tmp?
I don't understand either the tonumber function.

These are some of the durations I have, the field is called "Time spent" and I would like to calculate the average for a specific search like for especific customer that I've selected before (search customer=X).
00:01:00
00:02:00
00:05:00
00:03:00
00:04:00
00:06:00
00:00:00
00:07:00
(this is how I search for a specific value for this field Tspent="00:01:00", I supouse it is duration)

Thanks!

[sundareshr]

You should get the expected result with this

... | eval dur = replace(Tspent, "\.\d+$", "") | convert dur2sec(dur) | stats avg(dur) as avgTspent

[marina_rovira]

Yes I have! Thank you, the last question about this, trying if you can help me with this last step 😄

The output is 00:15:04.502712, is there some way that I can remove the ms and show it as 00:15:04 ?

Thank you a lot!

[asifhj]

Durations?

[martin_mueller]

Are those values timestamps or durations?