Hello,
I'm trying to get the sum of days where no events occurred by a city name.
I found the following answer (https://answers.splunk.com/answers/29371/find-days-with-no-events.html) that uses timechart to handle days without events:
sourcetype=foo | timechart count span=1d by city
which gives me the following table:
I feel like I'm getting closer to the solution but what i would like is to know how many days don't have events, in our example that would be:
How could I solve this?
Thanks in advance!
Benoit
try putting this at the end of your search:
|foreach * [eval <<FIELD>>_0=if('<<FIELD>>'=0,1,0)|fields - date_0]|appendpipe [|stats sum(*_0) as *|eval date="Days at 0"]|fields - *_0
that'll add a line at the bottom of your table for the sum of all 0 days. or you could leave the appendpipe []
out of it and just use the |foreach * [....]|stats...
to only bring in the Days at 0
try putting this at the end of your search:
|foreach * [eval <<FIELD>>_0=if('<<FIELD>>'=0,1,0)|fields - date_0]|appendpipe [|stats sum(*_0) as *|eval date="Days at 0"]|fields - *_0
that'll add a line at the bottom of your table for the sum of all 0 days. or you could leave the appendpipe []
out of it and just use the |foreach * [....]|stats...
to only bring in the Days at 0
This works great, thank you very much!
|where count=0
Append this to your query and try
Unfortunately this works only when the timechart is not sorted "by city" and returns nothing otherwise.