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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...