Splunk Search

## Count days without events

Engager

Hello,

I'm trying to get the sum of days where no events occurred by a city name.

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

Benoit

Tags (5)
1 Solution
Super Champion

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

Super Champion

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

Engager

This works great, thank you very much!

Motivator
``````|where count=0
``````

Append this to your query and try

Engager

Unfortunately this works only when the timechart is not sorted "by city" and returns nothing otherwise.

Get Updates on the Splunk Community!

#### Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

#### Observability Newsletter Highlights | March 2023

March 2023 | Check out the latest and greatestSplunk APM's New Tag Filter ExperienceSplunk APM has updated ...