Splunk Search

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

chvnc
Explorer

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.

0 Karma
1 Solution

MuS
Legend

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

View solution in original post

MuS
Legend

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
Explorer
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?

0 Karma

MuS
Legend

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

0 Karma

chvnc
Explorer

Thanks Man that worked

0 Karma

MuS
Legend

You're welcome 🙂

0 Karma

pradeepkumarg
Influencer

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...