topic Re: Count of zero and non zero values in a table? in Splunk Search

Count of zero and non zero values in a table?

mihikaraj — Wed, 21 Nov 2018 03:36:18 GMT

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.

Re: Count of zero and non zero values in a table?

woodcock — Wed, 21 Nov 2018 16:22:16 GMT

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) ]

Re: Count of zero and non zero values in a table?

mihikaraj — Wed, 21 Nov 2018 22:30:33 GMT

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

Re: Count of zero and non zero values in a table?

mihikaraj — Fri, 23 Nov 2018 00:58:21 GMT

@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.

Re: Count of zero and non zero values in a table?

woodcock — Fri, 23 Nov 2018 04:31:45 GMT

Add this:

| appendpipe [ stats avg(zeroCount) AS zeroCount ]