Splunk Search

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?

Explorer

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

SplunkTrust
SplunkTrust

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.

View solution in original post

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!