I have a process that experiences about 8640 events per day, or what I would expect to be an average of 0.1 events per second. Multiple events could occur in the same second, but overall, there are about 8500ish events per day.
search status=200 earliest=7/1/2017:00:00:00 searchtimespandays=1 | eval _time=floor(_time) | stats count by _time | | timechart span=1d count mean(count) min(count)
The "eval time=floor(time)" serves to round downward each event's time to the nearest second.
The "stats count by _time" then computes, for each second, how many events occurred within that second.
What I have seen online suggests that a timechart command would allow computation of max, mean, etc. across the entire timespan (1d).
What I would expect here is something like the following:
_time count mean(count) 2017-07-01 8141 0.094224
But what I am getting is:
_time count mean(count) 2017-07-01 8141 1.091021
So it appears that I am getting averages that are only including the seconds for which there is at least one event. In my case, I need to also include the zero-event seconds, which this doesn't appear to be doing.
I tried table
| makecontinuous span=1s, but that complained that too many rows (> 50000) were being generated.
Is there a way to calculate mean/max/min, etc while including the 'zero-event' seconds?
You are running into a problem because you did not rename
count in the
stats command. Change to...
| stats count as mycount by _time | timechart span=1d count mean(mycount) min(mycount)
The renaming doesn't seem to change anything for my queries. With or without renaming, the mean(count) or mean(mycount) comes out greater than 1.
Is there something else I'm missing?
Okay, try this
| stats count as mycount by _time | appendpipe [|stats min(_time) as mintime, max(_time) as maxtime | eval mytime=mvrange(mintime,maxtime+1) | table mytime | eval mycount=0 | mvexpand mytime | rename mytime as _time ] | stats sum(mycount) as mycount by _time | eval day=strftime( _time,"%Y-%m-%d") | stats count mean(mycount) as avgcount by day
And if the above results look right, then replace the last two lines with ...
| timechart span=1d count mean(mycount) as avgcount
This appears to work for the first 2 hours or so of the timeframe. I think appendpipe is limited to ~10K returns, so it's not computing accurately past the first 2.7 hours or so. 10000/3600 = 2.7 ?