Alerting

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

vikas_gopal
Builder

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

Labels (1)
0 Karma
1 Solution

PickleRick
Ultra Champion

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.

View solution in original post

0 Karma

vikas_gopal
Builder

Thank you so much , logic works .

0 Karma

PickleRick
Ultra Champion

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.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...