Splunk Search

Count of zero and non zero values in a table?

mihikaraj
New Member

I have a search which generates a table as below. The column value is epoch time.

IP 1542682800 1542684600 1542686400 1542688200 1542690000 1542691800 1542693600
10.7.13.1 0 0 0 59 84 51 0
10.7.13.2 0 61 140 103 136 102 0
10.7.14.3 0 0 0 0 0 0 0
10.7.15.4 0 0 22 6 3 0 0
10.7.15.5 60 12 138 84 15 0 0
10.7.34.6 0 0 0 0 0 0 0
10.7.34.7 0 0 0 0 0 0 0

Search is like this :
base search |
| bucket span=30m _time
| chart count(people) by IP _time limit=500 | sort _time

I am trying to add two columns which would have the count of zero and non-zero values for a particular IP. Any help with this is appreciated.

So for the 1st row above will have zero count 4 and non zero count 3 and so on for each row.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

base search
| bucket span=30m _time 
| chart count(people) by IP _time limit=500
| sort _time
| eval zeroCount=0. count=0
| foreach 15* [ eval count = count + 1, zeroCount = zeroCount + if(($<<FIELD>>$ == 0, 1, 0) ]

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

base search
| bucket span=30m _time 
| chart count(people) by IP _time limit=500
| sort _time
| eval zeroCount=0. count=0
| foreach 15* [ eval count = count + 1, zeroCount = zeroCount + if(($<<FIELD>>$ == 0, 1, 0) ]
0 Karma

mihikaraj
New Member

Thanks @woodcock. Gives me what I was expecting with a little tweak in the syntax.

0 Karma

mihikaraj
New Member

@woodcock, Is there a way to have a new row at the bottom which is average of that column values? I tried using foreach but not able to.

0 Karma

woodcock
Esteemed Legend

Add this:

| appendpipe [ stats avg(zeroCount) AS zeroCount ]
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 ...