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
SplunkTrust
SplunkTrust

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!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...