How to convert values for a time field to find max and average values with stats?

[chvnc]

We have a field with data
00 00:01:00.209
00 00:00:59.540
00 00:00:10.528
00 00:00:10.014
00 00:00:10.010
00 00:00:09.263
00 00:00:02.422

Using strftime, strptime can't be formatted, and without formatting, it's not working to find an average.

[MuS · Accepted solution]

Hi chvnc,

You can take this run everywhere example:

| gentimes start=-1 
| eval foo="00 00:01:00.209" 
| eval myFoo=strptime(foo, "00 %H:%M:%S.%3N") 
| stats count avg(myFoo) AS avg_foo by foo 
| eval new_foo=strftime(avg_foo, "00 %H:%M:%S.%3N")

strptime is used to parse a time stamp represented by a string and return an epoch time. strftime is used to create a human readable time stamp based on an epoch time.
More details are in the docs http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/CommonEvalFunctions#Date_and_Time_...

Hope this helps ...

cheers, MuS

[chvnc]

index=u2 sourcetype=ema earliest=-5m | fields ExecuteTime, Target | eval Execute_Time=strptime(ExecuteTime,"00 %H:%M:%S.%3N") | timechart avg(Execute_Time) as avg_duration by Target | eval avg_duration=strftime(avg_duration,"%H:%M:%S.%3N")

trying to write the query like this, but the last eval statement for strftime seems to not be working, the avg_duration is showing in epoch time only. Can you help in this?

[MuS]

Yeah, I thought about that as well but here is another solution: The epoch time after strptime will be starting at the current day midnight, so just subtract this from the value and you will get your seconds 😉

Try this run everywhere command:

| gentimes start=-1 
| eval foo="00 00:01:00.209" 
| eval myFoo=strptime(foo, "00 %H:%M:%S.%3N") - relative_time(now(), "-0d@d")
| stats count avg(myFoo) AS avg_foo by foo, myFoo 

cheers, MuS

[chvnc]

Thanks Man that worked

[MuS]

You're welcome 🙂

[pradeepkumarg]

What did you try so far? How does your strptime command look like? What do the first two zeros signify ?