Splunk Search

How to Search for hosts that have not had an event from a certain SourceName?

cmahan
Path Finder

I want to be able to list all computers that have not received an event of the type below in a given time frame. I tried an example written for sourcetype and substituted SourceName, but it did not seem to like it. Any tips on how to accomplish this?

03/30/2015 04:45:17 PM
LogName=Application
SourceName=EBS Check
EventCode=327
EventType=4
Type=Information
ComputerName=FOO-SLX
TaskCategory=%1
OpCode=Info
RecordNumber=158760
Keywords=Classic
Message=Snapshot snap-4c54c8c8 found, within the time of 120 minutes.

Tags (3)
1 Solution

sideview
SplunkTrust
SplunkTrust

Assuming the host value is just present in the "host" field, you can then use the metadata command, which allows this nice and quick search as a solution.

SourceName="EBS Check" | eval hasEBSCheck=1 | append [| metadata type="hosts" | eval hasEBSCheck=0] | stats max(hasEBSCheck) as hasEBSCheck by host | search hasEBSCheck=0

We basically get the EBS Check events, paint a little field on them hasEBSCheck=1, then we glue onto the set some more rows that come from | metadata type="hosts", onto which rows we have painted hasEBSCheck="0", then stats command does the work of figuring which hosts have the event and which don't.

Note: if your events are in some other index, you'll have to put the right index=foo expression into the metadata command as well as the initial search.

Another Note: append is commonly overused. Usually you can do things like this by pouring all the relevant events in with a simple disjunction and grouping with stats. Here though it's far more efficient to just pull the list of all hosts with the metadata command and thus we need append.

View solution in original post

sideview
SplunkTrust
SplunkTrust

Assuming the host value is just present in the "host" field, you can then use the metadata command, which allows this nice and quick search as a solution.

SourceName="EBS Check" | eval hasEBSCheck=1 | append [| metadata type="hosts" | eval hasEBSCheck=0] | stats max(hasEBSCheck) as hasEBSCheck by host | search hasEBSCheck=0

We basically get the EBS Check events, paint a little field on them hasEBSCheck=1, then we glue onto the set some more rows that come from | metadata type="hosts", onto which rows we have painted hasEBSCheck="0", then stats command does the work of figuring which hosts have the event and which don't.

Note: if your events are in some other index, you'll have to put the right index=foo expression into the metadata command as well as the initial search.

Another Note: append is commonly overused. Usually you can do things like this by pouring all the relevant events in with a simple disjunction and grouping with stats. Here though it's far more efficient to just pull the list of all hosts with the metadata command and thus we need append.

cmahan
Path Finder

How can I refine this further to a specific ComputerName format or similar?

For example, I just want ComputerName="-SLX", or NOT ComputerName="-Web"

Adding the terms to the beginning of the search does not work as expected.

0 Karma

sideview
SplunkTrust
SplunkTrust

I think you're looking for "*" as a wildcard.

ComputerName="*-SLX" or NOT ComputerName="*-Web"

0 Karma

cmahan
Path Finder

Yes, but that cannot be combined with the search terms in the given answer.... at least I am not sure how to format it. Adding as you wrote to the front of the search does not have the desired effect...

0 Karma

sideview
SplunkTrust
SplunkTrust

I assumed you wanted those terms to be in the initial search, ie

SourceName="EBS Check" ComputerName="*-SLX" or NOT ComputerName="*-Web" | eval hasEBSCheck=1 | append [| metadata type="hosts" | eval hasEBSCheck=0] | stats max(hasEBSCheck) as hasEBSCheck by host | search hasEBSCheck=0

But now maybe I'm thinking you needed those terms on the other side. ie you want the events that have had zero EBSCheck events, and that also do NOT (?) have SLX or Web prefixes? Can you confirm that?

0 Karma

cmahan
Path Finder

Yes, I would like to be able to narrow it either by including *-SLX AND *-APPDB or by using a negative like NOT *-WEB. We have several server types and i want to exclude those from the search that should not have the EBScheck to eliminate false positives for missing the event. When i try as you show above with adding terms to the left, it does not filter as expected. I still see *-KSYNC, *-SSAA, and others I would expect to be excluded, even when I try like this:

SourceName="EBS Check" ComputerName="-SLX" OR ComputerName="-APPDB" | eval hasEBSCheck=1 | append [| metadata type="hosts" | eval hasEBSCheck=0] | stats max(hasEBSCheck) as hasEBSCheck by host | search hasEBSCheck=0

0 Karma

cmahan
Path Finder

there are asterisks in the search i posted before the -SLX and -APPDB, i guess that character requires a certain format in these notes to show up... its there though

0 Karma

sideview
SplunkTrust
SplunkTrust

Gotcha. Well this changes things a bit. Specifically once you need more than just simple "host" to specify the base server list, you can't use the metadata command anymore. You'll need this instead.

SourceName="EBS Check" | eval hasEBSCheck=1 | append [search index=* ComputerName="*-SLX"  or  NOT ComputerName="*-Web" | stats count by host | eval hasEBSCheck=0] | stats max(hasEBSCheck) as hasEBSCheck by host | search hasEBSCheck=0
0 Karma

cmahan
Path Finder

I think this does the trick. Thanks!

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...