How to find unique values between two queries?

[JoshuaJohn]

I know I am for sure over-complicating this. I need to find values that are in field x, that are not in field y.

This is my first query:

index=nitro_prod_loc_server earliest=-4h
| stats values("locId") as All_Locs

This returns all locations, it requires a 4 hour timespan

This is my second query:

index=nitro_prod_loc_server appName="nitroCheck" bdy.addInfo{}.key="Serial Number" 
| stats values("locId") as "Checked_Locs"

This returns a list of locations that have been checked, it needs the time to be set to all time.

I want a list of locations not found in the second query. Any suggestions?

[JoshuaJohn · Accepted solution]

Got it

| multisearch 
 [ search index=-### appName="NotifiCenter" earliest=-4h]
 [ search index=-### appName="NitroCheck" bdy.addInfo{}.key="Serial Number" ]
 | stats values(locId) as location distinct_count(locId) AS c_idx by appName
 | stats count(appName) as c_appName by location
 | where c_appName < 2
 | table location
 | sort location asc

[MuS]

Hi JoshuaJohn,

you should not use join for reasons.

You can use a multireport to do this, and this SPL is un-tested so you might have to modify it to match 😉

| multireport
[  search index=nitro_prod_loc_server earliest=-4h
 | stats values("locId") as All_Locs ]
[ search index=nitro_prod_loc_server appName="nitroCheck" bdy.addInfo{}.key="Serial Number" 
 | stats values("locId") as "Checked_Locs" ]
| streamstats count(index) AS c_idx
| where c_idx < 2 AND isnull(appName)

This would assume you have no appName field returned from the first search.

Hope this helps ...

cheers, MuS

[JoshuaJohn]

Ah I do have an appName returned from the first field, it always returns something (regardless if its set to specifically return)