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!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...