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
Legend

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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...