How to trigger alert if host is missing reporting based on lookup tabel for source=WinEventLog:Security?

[vikas_gopal]

Hi Experts,

I want to trigger an alert when a particular host for source=WinEventLog:Security is not reporting to splunk from last 1 hour. I have a list of 30 critical hosts and for those I have created a csv lookup as shown below

DC_Machines.csv

Spoiler

host               source
abc              WinEventLog:Security
bcd              WinEventLog:Security
xyz              WinEventLog:Security

What I have achieved so far

Spoiler

| inputlookup DC_Machines.csv | join type=left host [metadata type=hosts index=os_windows index=os_windows_dc ]
| fillnull recentTime
| where recentTime < relative_time(now(), "-1h")
| fields host,recentTime,source

above gave me a host from lookup table which is not reporting at all(fine) but how about those hosts which are reporting except source=WinEventLog:Security

What I want
above query should only return those host which is missing only one source=WinEventLog:Security

My approach might be completely wrong or may be I am missing on something .I tried to add filter on source which is not working in above logic.

Any suggestions please .

Thank you in advance

[vikas_gopal]

Thank you so much , logic works .

[PickleRick · Accepted solution]

You overcomplicate it 😉

Just do a

| tstats count where sourcetype=WinEventLog:Security AND (index=os_windows OR index=os_windows_dc) AND earliest=-1h by host

This will give you list of hosts with corresponding events count

Now you can either append your lookup if you have just a single field named host

| inputlookup append=true DC_Machines.csv

Or do an append if your lookup has more fields

| append [
  | inputlookup DC_Machines.cs
  | table host 

And now you have to do the magic trick 😉

| fillnull count

To get initial zeros for your hosts from lookup.

And finally

| stats sum(count) by host

Et voila. You can filter the results any way you want - get only zeros, or non-zeros. It's up to you.