topic Re: trigger alert if host is missing reporting based on lookup tabel for source=WinEventLog:Security in Alerting

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

vikas_gopal — Thu, 21 Jul 2022 19:46:23 GMT

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

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

What I have achieved so far

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

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

PickleRick — Thu, 21 Jul 2022 18:40:07 GMT

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.

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

vikas_gopal — Thu, 21 Jul 2022 19:10:50 GMT

Thank you so much , logic works .