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
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...