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

[atebysandwich]

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 

[richgalloway · Accepted solution]

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.

[atebysandwich]

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!

[yuanliu]

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