Splunk Search

Count days without events

bntdumas
Engager

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:
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:
alt text

How could I solve this?

Thanks in advance!
Benoit

0 Karma
1 Solution

cmerriman
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

View solution in original post

cmerriman
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

bntdumas
Engager

This works great, thank you very much!

0 Karma

splunker12er
Motivator
|where count=0

Append this to your query and try

0 Karma

bntdumas
Engager

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...