Splunk Search

## How do I find the average time (by day) of an event?

New Member

I have a search that returns the time of the first instance of a specific event (field "firstaction") by date (field "ldate").

results:

ldate firstaction
2019-12-30 09:00:00.000
2019-12-31 07:00:00.000

What I want is the average time (value) of all the results.... or in this case 08:00:00.000

"|stats avg(firstaction) " doesn't return anything.

Also, only days that have a value should be averaged.

I thought about breaking out the value of the hours, minutes and seconds and converting them to a sum of seconds... then averaging the sum of seconds by day and then converting them back to a time value... but that seems overly complex and I can't be the only person that needs to know the average time of the first occurrence of something by day and alert if it falls outside a standard deviation.

Any thoughts (besides purchasing behavioral analytics)?

Tags (1)
1 Solution
Builder

If you add this to the end of a search that returns the interesting raw events, it will give you the average time the first event of each day is seen in the data:

``````| eval "Average Event Time"=strftime(avg_event_time, "%H:%M")
| stats earliest(_time) as FirstAppearance by _time
| bucket _time span=1d
| stats avg(FirstAppearance) AS avg_FirstAppearance
| eval "Average First Appearance"=strftime(avg_FirstAppearance, "%H:%M") | table "Average First Appearance"
``````
Ultra Champion
``````| makeresults
| eval _raw="ldate firstaction
2019-12-29 06:00:00.000
2019-12-30 09:00:00.000
2019-12-31 07:00:00.000"
| table ldate firstaction
| rename COMMENT as "this is sample you provided"
| rename COMMENT as "from here, the logic"
| eval temp=substr(firstaction,1,8)
| convert dur2sec(temp)
| stats mean(temp) as firstaction_avg
| eval firstaction_avg = tostring(firstaction_avg,"duration")
``````

Since the search result is a character string once, it needs to be changed to time .
This is the query to convert from your search results.

Esteemed Legend

Like this:

``````search yadda yadda yadda
| eval time=strftime(strptime(time, "%H:%M:%S.%3M"), "%H%M%S%3M")
| stats min(time) AS firstaction BY ldate
| stats avg(firstaction)
| fieldformat firstaction = replace(firstaction, "^(\d\d)(\d\d)(\d\d)", "\1:\2:\3.")
``````
Builder

``````| eval time=strftime(strptime(_time, "%H:%M:%S.%3M"), "%H%M%S%3M")
``````
Esteemed Legend

I hate your data; try this:

``````search yadda yadda yadda
| stats earliest(time) as firstaction by ldate
| rex field=time mode=sed "s/[:\.]//g"
| stats avg(firstaction)
| fieldformat firstaction = replace(firstaction, "^(\d\d)(\d\d)(\d\d)", "\1:\2:\3.")
``````
Builder

@woodcock It's not my data, or my question, just trying to learn from Master Yoda. I didn't follow the data in the question either, so I was using this to derive an answer...

``````| makeresults
| eval data="2-Jan-20 16:00:00,10;2-Jan-20 16:30:00,14;1-Jan-20 15:35:00,10;1-Jan-20 17:34:00,14;3-Jan-20 16:50:00,10;3-Jan-20 17:34:00,14"
| makemv data delim=";"
| mvexpand data
| rex field=data "(\s|\n?)(?<data>.*)"
| makemv data delim=","
| eval _time=strptime(mvindex(data,0),"%d-%b-%y %H:%M:%S"),
ErrorCount=mvindex(data,1)
| fields _time ErrorCount
| eval "Average Event Time"=strftime(avg_event_time, "%H:%M")
| stats earliest(_time) as FirstAppearance by _time
| bucket _time span=1d
| stats avg(FirstAppearance) AS avg_FirstAppearance
| eval "Average First Appearance"=strftime(avg_FirstAppearance, "%H:%M") | table "Average First Appearance"
``````
Esteemed Legend

So do you have a working solution or are you still having some trouble?

SplunkTrust

Give this a try

``````search yadda yadda yadda | stats earliest(time) as firstaction by ldate
| convert dur2sec(firstaction)
|stats avg(firstaction) as firstaction
| eval firstaction=tostring(firstaction,"duration")
``````
Builder

If you add this to the end of a search that returns the interesting raw events, it will give you the average time the first event of each day is seen in the data:

``````| eval "Average Event Time"=strftime(avg_event_time, "%H:%M")
| stats earliest(_time) as FirstAppearance by _time
| bucket _time span=1d
| stats avg(FirstAppearance) AS avg_FirstAppearance
| eval "Average First Appearance"=strftime(avg_FirstAppearance, "%H:%M") | table "Average First Appearance"
``````
Get Updates on the Splunk Community!

#### Adoption of RUM and APM at Splunk

Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

#### March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

#### Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...