How do I use a comparison search to find all devices not reporting to Splunk?

[edwardrose]

I am trying to find all devices not reporting into splunk via a qualys scan of our DMZ and searching against all indexes. I want to have only the hosts that are not reporting in to show in the results. Here is my search

index=* 
    [ inputlookup dmzhosts.csv 
    | table IP 
    | rename IP AS host 
    | format] OR 
    [ inputlookup dmzhosts.csv 
    | table hostname 
    | rename hostname AS host 
    | format]
| eval host=upper(host)
| stats count by host
| append [inputlookup dmzhosts.csv | eval count=0, hostname=upper(hostname)|rename hostname as host | fields host, count]
| stats sum(count) AS Total by host
| where Total=0

There are only 160ish hosts being reported in the qualys search that generates the dmzhost.csv outputs file. I created a search to look for IP or hostname and assign values to the total number of events to each host and compare it back with the original output file where all hosts were assigned a 0 value. Then only report hosts that have a stats count of 0. Does my search look ok?

Thanks
Ed

[Anam]

Hi @edwardrose

I am the Community Content Specialist for Splunk Answers. Did any of the answers work for you? If they did please go ahead and accept the appropriate one and if not let the community know if you need more clarification/help with your problem.

Thanks

[tkopchak]

If you are looking to locate systems that have stopped sending data to Splunk, you might want to look at the Broken Hosts app, https://splunkbase.splunk.com/app/3247/. Having a lookup table containing host information already puts you in a good position to configure this app, since it uses a similar concept for tracking hosts and how frequently they should be sending data.

[woodcock · Accepted solution]

This is the Sentinel Search problem discussed (with solution) here:

https://conf.splunk.com/session/2015/conf2015-LookupTalk.pdf

[cmerriman]

I'm actually doing something very similar with wanting to find missing data. so i am also doing an append and assigning a 0 value. the only difference is i do a max instead of sum in my last stats and i use search instead of where, but really, the logic makes sense. is the search working as you'd expect? if you grab one of the hosts that has a 0 count after running and plug it into index=* host=host_you_identified, is there data? i don't generally condone index=*, however - what time frame is this search running?