Comparing version number for multiple hosts in index

[pl2345]

I have an index that ingests scan files and assigns a sourcetype based on the folder location. There are several scans for each host, which caused some issues when I tried to join or append.

I'm trying to build  a search that takes the host, scan, and version in one folder and compares it against the host, scan, and version in the other and create a table with the host, evaluate scan and version, and authoritative scan and version and tell me if we have the most up to date scans in the authoritative folder. What's the best method to create this search?

Example data:

HOST_FQDN: Host1

sourcetype: Evaluate

SCAN: IE11

Version: 2

HOST_FQDN: Host1

sourcetype: Authoritative

SCAN: IE11

Version: 3

[richgalloway]

To find instances where the Evaluate version differs from the Authoritative version, try this query.

index=foo HOST_FQDN=* (sourcetype=Evaluate OR sourcetype=Authoritative) SCAN=* Version=*
| stats values(HOST_FQDN) as HOST_FQDN, list(sourcetype) as sourcetype, list(Version) as Version by SCAN

[pl2345 · Accepted solution]

That search worked, but I was looking more for a line by line comparison. After taking a step back yesterday I came up with the following that gave me the results I was looking for.

---

index=foo sourcetype=Evaluate HOST_FQDN=* 
| join type=left HOST_FQDN SCAN 
   [search index=foo sourcetype=Authoritative HOST_FQDN=* 
   | rename sourcetype as "Authoritative Source File", Version as "Authoritative Version"] 
| rename source as "Evaluate Source File", Version as "Evaluate Version"
| table _time, HOST_FQDN, SCAN, "Evaluate Source File", "Evaluate Version", "Authoritative Source File", "Authoritative Version" 
| dedup HOST_FQDN STIG​

---