Splunk Search

How Can I use Stats Avg() On a Datetime Field?

vtsguerrero
Contributor

I have this indexed field which is read by splunk as a string, I need the average length, but the data has no Day, month or year, just time.

Example data :
03:20:15
02:45:07
03:12:00
04:05:23

How do I convert these so Splunk can get the average??

Thanks in advance!

Tags (3)
0 Karma
1 Solution

acharlieh
Influencer

One method could be to use the convert command to change the field from a duration into to seconds, then average with some form of stats, and if necessary use the tostring function in an eval to change back to duration format.

... | convert dur2sec(field) | stats avg(field) as field | eval field=tostring(round(field),"duration")

Now with any solution there are some assumptions of your data, with this convert method, it assumes that

  • No fractional seconds are present e.g. "01:02:03.456" would not work
  • Days are specified as "D+" e.g. "1+0:0:1" will work but "1:0:0:1" will not
  • Hours and minutes respect their respective moduli (0-23 hours, 0-59 minutes)

If your data doesn't conform to this, then you could craft your own regular expression and use rex command to pull out the pieces and use an eval to combine into seconds:

... | rex field=field "^(?<dur_h>\d+):(?<dur_m>\d+):(?<dur_s>\d+(?:\.\d+)?)$" | eval dur = (dur_h*60 + dur_m)*60 + dur_s | stats avg(dur) as field 

View solution in original post

acharlieh
Influencer

One method could be to use the convert command to change the field from a duration into to seconds, then average with some form of stats, and if necessary use the tostring function in an eval to change back to duration format.

... | convert dur2sec(field) | stats avg(field) as field | eval field=tostring(round(field),"duration")

Now with any solution there are some assumptions of your data, with this convert method, it assumes that

  • No fractional seconds are present e.g. "01:02:03.456" would not work
  • Days are specified as "D+" e.g. "1+0:0:1" will work but "1:0:0:1" will not
  • Hours and minutes respect their respective moduli (0-23 hours, 0-59 minutes)

If your data doesn't conform to this, then you could craft your own regular expression and use rex command to pull out the pieces and use an eval to combine into seconds:

... | rex field=field "^(?<dur_h>\d+):(?<dur_m>\d+):(?<dur_s>\d+(?:\.\d+)?)$" | eval dur = (dur_h*60 + dur_m)*60 + dur_s | stats avg(dur) as field 

vtsguerrero
Contributor

Thanks a lot @acharlieh ♦ !
Worked perfectly!
Just tryin' now to get the difference from the last head 1 event duration to the average duration.
With the average and the last event I shall get the deviation to generate a red, yellow or green status.
Thanks!

0 Karma

pradeepkumarg
Influencer

May be do an eval and convert them to total seconds into a new field.. and do average on the new field.

0 Karma

vtsguerrero
Contributor

What function could I use to convert em with an eval? I mean, I dont have the full date...

0 Karma
Get Updates on the Splunk Community!

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Pack your bags (and maybe your dancing shoes)—Cisco Live is heading to San Diego, June 8–12, 2025, and Splunk ...

Splunk App Dev Community Updates – What’s New and What’s Next

Welcome to your go-to roundup of everything happening in the Splunk App Dev Community! Whether you're building ...

The Latest Cisco Integrations With Splunk Platform!

Join us for an exciting tech talk where we’ll explore the latest integrations in Cisco &#43; Splunk! We’ve ...