Sometimes I want to run reports calculating things about timebuckets that have no data in them.
the timechart command is awesome because it knows that even if no data occurred in a given timebucket, it goes ahead and creates a row with that _time value, and with all 0 counts and null statistics as necessary.
in other words
| bin _time span="1h" | stats count by _time clientip is in many ways similar to
timechart span="1h" count by clientip, except that former wont have any buckets to represent times when no data was found.
However sometimes you need to do further calculations on the rows and you need the flexibility of the stats output format, with the "fill in my blank buckets" behavior of timechart.
here's what I have to do, and I don't like it very much.
As an example, here's a report that I can run over 7 days and it'll give me hosts that had 24 consecutive hours where no data was reported. To get this done I have to pile all 400 hosts into the "split-by" part of timechart command, and then I have to use the
untable command to unpack them all.
To give an example, say I have 400 hosts, and I want a search that runs over 7 days and returns the subset of hosts for which any consecutive 24 hour period had zero data in it.
Here's the only solution I've found and I'm not fond of it. Definitely still looking for better answers:
sourcetype=X | timechart span=1h count by host limit=500 | untable _time host count | eval isBlank=if(count>0,0,1) | sort 0 + _time | streamstats sum(isBlank) as hoursBlankInLast24 window=24 by host | search hoursBlankInLast24=24 | stats count by host
I basically pile all 400 hosts into the timechart command so the timechart has a _time column and then 400 other columns for my hosts. =/
Then I use the
untable command to unpack all 400 hosts out so I can get back to the cleaner stats-style output format. BUT, along the way I got the neat "create my blank timebucket rows" behavior of timechart.
fillnull will fill blank field values in existing rows, but I'm talking about the behavior in timechart where it creates a row for each time bucket regardless of whether there is any data there.