How do I filter my time chart results to only display devices that have a count of zero for any week within a certain time range?

[g038123]

I'm fairly new to Splunk and have a search that basically returns a count of the number of times a device logs in to our system and uploads data each week. The time chart looks similar to this.

_time     Device A  Device B  Device C   Device D
2015-10-04  1        1      1         0
2015-10-11  1        1      1         0
2015-10-18  1        1      1         2
2015-10-25  1        0      1       1
2015-11-01  1        0      2         1
2015-11-08  1        1      1         1
2015-11-15  1        1      3         1

The only devices I'm concerned about are those that have zero connections at some point: Devices B and D. How would I filter those that are working as intended, Devices A and C, from my results?
The total device list can be in the thousands depending on the geography I search in. I'm only interested in the ones that appear to be having issues, those with a zero count for a week or more, so I can focus on that population.

Thank you in advance for any help.

[martin_mueller · Accepted solution]

This should do:

  base search | timechart count by device | untable _time device count
| eventstats min(count) as min by device | where min=0
| xyseries _time device count

First, make your initial timechart and flip that into a stats-like table, then filter by "device has a value that's zero", then flip back into timechart-like table.

[g038123]

That worked perfectly, thank you for the step by step explanation, it was very helpful.