Splunk Search

How to find unique values between two queries?

JoshuaJohn
Contributor

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?

0 Karma
1 Solution

JoshuaJohn
Contributor

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

View solution in original post

0 Karma

JoshuaJohn
Contributor

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

View solution in original post

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

JoshuaJohn
Contributor

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!