How to filter xyseries table results to only show rows where there is a value of 0 in any column?

[bogleric]

I am generating an XYseries resulting in a list of items vertically and a column for every day of the month.

Example:

Item    Day 1    Day 2    Day 3    Day 4
Item1   98       8998     0        87
Item2   900      23       234      34
Item3   1        1        1        1
Item4   542      0        87       3

I would like to filter the table to only show rows where there is a 0 for the item in any column. In this example, only rows for Item1 and Item4 would show. Keep in mind the days are for the entire month; therefore usually 30 or 31 day columns.

This table is built with:

index=index1 earliest=-1mon@mon latest=@mon | convert timeformate="%m-%d-%Y" ctime(_time) as MDY | bin _time span=1d | eventstats sum(count) as cnt by itemname, MDY | xyseries itemname MDY cnt | fillnull

[woodcock]

Add this to the end of your search:

... | eval ZeroCount=0 | foreach Day* [eval ZeroCount=ZeroCount+if((<<FIELD>>==0), 1, 0)] | where ZeroCount>0

[bogleric]

This is returning zero results.

I set the <> to cnt which contains the count for the day for that item. In the foreach statement I set that to 07* and also tried the field name.

The Day1, Day2, Day3, is actually a date value. EX: 07-01-2015, 07-02-2015, ....., 07-30-2015, 07-31-2015

There are a total of 300+ rows returned prior to adding the addition to the search and several have 0 counts. Some have a 0 count for every day (column).

[woodcock]

OK, then try this:

 ... | eval ZeroCount=0 | foreach * [eval ZeroCount=ZeroCount+if((<<FIELD>>==0), 1, 0)] | where ZeroCount>0

[bogleric]

I should have mentioned I also tried the 'foreach *'. I just tried again to validate my initial results. Still results in "No results found".

In testing the search is valid however the final where is not providing a match. This was validated with
...|where ZeroCount=0

This displayed all results. In reviewing the value of the ZeroCount column... all rows show a zero count of '0' in the ZeroCount column. This validates the ZeroCount=0 test.

I am guessing that this point that perhaps the value in the column represented by cnt may be a string rather than a number.

[woodcock]

That should not be the problem: sum creates numbers and xyseries should not change the type of the fields. There really isn't much more to say if this doesn't work (note value=0😞

index=index1 earliest=-1mon@mon latest=@mon | convert timeformate="%m-%d-%Y" ctime(_time) as MDY | bin _time span=1d | eventstats sum(count) as cnt by itemname, MDY | xyseries itemname MDY cnt | fillnull value=0 | eval ZeroCount=0 | foreach * [eval ZeroCount=ZeroCount+if((<<FIELD>>==0), 1, 0)] | where ZeroCount>0