Splunk Search

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

drmorgan78
New Member

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

search yadda yadda yadda | stats earliest(time) as firstaction by 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)
0 Karma
1 Solution

mydog8it
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"

View solution in original post

to4kawa
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"
| multikv forceheader=1
| 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.

0 Karma

woodcock
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.")
0 Karma

mydog8it
Builder

@woodcock I'm trying to follow your SPL, but getting stuck. Should line 2 read:

| eval time=strftime(strptime(_time, "%H:%M:%S.%3M"), "%H%M%S%3M")
0 Karma

woodcock
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.")
0 Karma

mydog8it
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"
0 Karma

woodcock
Esteemed Legend

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

0 Karma

somesoni2
SplunkTrust
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")
0 Karma

mydog8it
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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...