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

[drmorgan78]

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

[to4kawa]

| 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.

[woodcock]

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.")

[mydog8it]

@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")

[woodcock]

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.")

[mydog8it]

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

[woodcock]

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

[somesoni2]

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

[mydog8it · Accepted solution]

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"