Splunk Search

How can I generate a search to find hosts which are missing a certain sourcetype?

systemsatpayzon
Path Finder

I have a sourcetype which is a log created by the AV application on the host. I would like to find hosts which are missing this particular sourcetype (over 4 hours). It seems like a easy search but I cannot figure out how to write it. Theoretically I would like to do a search on all hosts and sourcetypes and then find the hosts which haven't sent any log with this sourcetype. Easy in theory but I cannot figure it out. Please help me. This is how far i have come:
| metasearch sourcetype=* host=* | dedup sourcetype, host | "here I would like a tabular output of hosts missing this sourcetype"

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try something like this. This should give you list of host which have not send any data for sourcetype="PutYourSourceTypeHere". Run the search for time range that you need.

| tstats count WHERE index=* by host sourcetype | eval count=if(sourcetype="PutYourSourceTypeHere",1,0) | stats sum(count) as count by host | where count=0 

View solution in original post

twinspop
Influencer

Assuming you were in a good state at some point in the last 4 hours, using the _internal index as an example:

| tstats latest(_time) as _time where sourcetype=splunkd and index=_internal by host sourcetype | where (now()-_time)>600

This will show hosts and sourcetypes that had sent internal splunkd logs, but have not for 10 minutes or more.

somesoni2
SplunkTrust
SplunkTrust

Try something like this. This should give you list of host which have not send any data for sourcetype="PutYourSourceTypeHere". Run the search for time range that you need.

| tstats count WHERE index=* by host sourcetype | eval count=if(sourcetype="PutYourSourceTypeHere",1,0) | stats sum(count) as count by host | where count=0 

cain1288
Explorer

Thanks much! Going to try and use this to find hosts that are not reporting sysmon data.

| tstats count WHERE index=* by host sourcetype
| eval count=if(sourcetype="WinEventLog:Microsoft-Windows-Sysmon/Operational",1,0)
| stats sum(count) as count by host
#| search NOT (<exclude/filter out systems as needed here>, example: index=syslog)
| where count = 0
| table host
| rename host as "Hosts Not Sending Sysmon"

0 Karma

bwlm
Path Finder

If the query needs to be filtered down to a subset of hosts (rather than ALL hosts that have logged to the Splunk indexer) that can be defined in a lookup table (e.g. MyHosts.csv with hostnames under the "host" column), this search can be run, but will only return results over the search time frame... so the subset of hosts (i.e. Asset list) must be appended to find out "what hosts are missing" via the "foundevents" field.

As also noted, one can also check for a delay of "600" seconds or greater (i.e. >10 minutes) as well... i.e. to find host and sourcetypes "without events" or latest whose latest event is "more than 10 minutes old" (but still within the current search time range).

 

 

| tstats count as eventcount, latest(_time) as lastTime where index=* [| inputlookup MyHosts.csv | stats values(host) as host] by host, sourcetype, source
| append [| inputlookup MyHosts.csv | fields host ]
| dedup host   `comment("Need to dedup by sourcetype possibly as well here")`
| eval foundevents=if(sourcetype="PutYourSourceTypeHere",1,0)
| eval delaySecs=now()-lastTime
| eval delayDur=tostring(delaySecs,"duration")
| where foundevents=0 or eventcount=0 or delaySecs>600

 

 

 

 

 

 

Tags (3)
0 Karma

systemsatpayzon
Path Finder

Awsome, it worked perfectly.. and fast also!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...