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

[cmahan]

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.

[sideview · Accepted solution]

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]

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.

[sideview]

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

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

[cmahan]

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

[sideview]

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?

[cmahan]

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

[cmahan]

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

[sideview]

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

[cmahan]

I think this does the trick. Thanks!