topic What's the best way to find out events that have field values that DO NOT match? in Splunk Search

What's the best way to find out events that have field values that DO NOT match?

atebysandwich — Fri, 28 Apr 2023 16:31:36 GMT

I have two lookups: one is the scan results from the current week and the other is historical lookup of scan results from the weeks prior. Each event is the scan results for a host (fields DNS IP). I have a field called called Host_Auth that can have of the following field values:

Windows Successful
Windows Failure
Unix Failed
Unix Successful
Unix Timeout
Windows Not Attempted
Unix Not Attempted
Unknown

I would like to create a search where the first lookup is compered against the second lookup and return events where the Host_Auth field value is is different.

I tried a join type=left Host_Auth but that didn't quite work

Re: What's the best way to find out events that have field values that DO NOT match?

richgalloway — Fri, 28 Apr 2023 17:20:37 GMT

The join command pairs events with the same values of the specified field. It cannot be used to find differences.

To find differences, join events using another field (DNS, perhaps) then filter on the Host_Auth field.

<<search 1>> | rename Host_Auth as Host_Auth_1 | join type=left DNS [ <<search 2>> | rename Host_Auth as Host_Auth_2 ] | where Host_Auth_1 != Host_Auth_2

My standard disclaimer applies: join is inefficient so consider using append or another method of combining results.

Re: What's the best way to find out events that have field values that DO NOT match?

yuanliu — Fri, 28 Apr 2023 23:05:39 GMT

To build on @richgalloway's disclaimer😃, here's a possible alternative

(<<sourcetype=sourcetyp1 search 1>>) OR (<<sourcetype=sourcetype2 search 2>>) ``` replace sourcetype with any field that differentiates search 1 from search 2 ``` | eventstats dc(sourcetype) as sourcetypes by DNS Host_Auth | where sourcetypes == 1

Re: What's the best way to find out events that have field values that DO NOT match?

atebysandwich — Mon, 01 May 2023 13:41:27 GMT

This worked exactly as I was hoping. I changed the join to be looking for IP rather than DNS.

Thank you for the help on this!