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!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...