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

[systemsatpayzon]

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"

[twinspop]

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 · Accepted solution]

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]

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"

[bwlm]

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

[systemsatpayzon]

Awsome, it worked perfectly.. and fast also!