Splunk Search

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

edwardrose
Contributor

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

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

View solution in original post

Anam
Community Manager
Community Manager

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

0 Karma

tkopchak
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

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

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

cmerriman
Super Champion

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?

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...