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.
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) ]
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) ]
Thanks @woodcock. Gives me what I was expecting with a little tweak in the syntax.
@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.
Add this:
| appendpipe [ stats avg(zeroCount) AS zeroCount ]