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