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?

g038123
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

martin_mueller
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

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

g038123
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...