Splunk Search

How can I obtain a list of values returned by one query, but not another?

jbrenner
Path Finder

I have one query that returns SESSION_IDs of attempted orders:

index=my_index "abc" | rex field=_raw "(?<SESSION_ID>pattern)" 

And I have a second query that returns SESSION_IDs of successful orders:

index=my_index "def" | rex field=_raw "(?<SESSION_ID>pattern)" 

How do return the list of SESSION_IDs returned by the first query that are not returned by the second query? I assume this involves using a subsearch with the NOT keyword, but I can't figure out how to do it.

Thanks!
Jonathan

0 Karma

dsha
Engager

@jbrenner what mayur said is correct in you case you need to replace the = with !=

[ search index=my_index "def"
| rex field=_raw "(?pattern)"
| stats count as "Successful" by SESSION_ID
| eval SESSION_ID1=SESSION_ID
| eval status2 = "Active" ]

with | eval SESSION_ID1!=SESSION_ID

0 Karma

mayurr98
Super Champion

Why do not you try
I assume both the keywords are in same index

 index=my_index "abc" NOT "def" | rex field=_raw "(?<SESSION_ID>pattern)" 

Also try this

index=my_index "abc" 
| rex field=_raw "(?<SESSION_ID>pattern)" 
| stats count as "Attempted" by SESSION_ID 
| eval SESSION_ID1=SESSION_ID 
| eval status1="Active" 
| join SESSION_ID1 type=outer 
    [ search index=my_index "def" 
    | rex field=_raw "(?<SESSION_ID>pattern)" 
    | stats count as "Successful" by SESSION_ID 
    | eval SESSION_ID1=SESSION_ID 
    | eval status2 = "Active" ] 
| eval Status = if(match(status1,status2), "Active", "Not_in_list") 
| table SESSION_ID1 Status 
| where Status="Not_in_list"

let me know if this helps!

0 Karma

jbrenner
Path Finder

Hi Mayur. Thanks for responding. I don't see how the first example would work, because the attempts and successes are in separate log statements.
I was able to get your second example to run, but it's returning the SESSION_IDs of all attempted orders instead of just the failed ones.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try something like this.

index=my_index "abc" NOT [search index=my_index "def" | rex "(?<SESSION_ID>pattern)" | fields SESSION_ID | format] | rex "(?<SESSION_ID>pattern)"

Depending on what you want to do with the events you could add | stats count by SESSION_ID or something similar.

---
If this reply helps you, Karma would be appreciated.

jbrenner
Path Finder

Hi Rich,
Thanks for the answer. I tried your suggestion, but I'm getting the following error:

Error in 'rex' command: Invalid argument: 'NOT'

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Thanks for pointing out my error. I fixed my answer.

---
If this reply helps you, Karma would be appreciated.

jbrenner
Path Finder

I assume I also need at least one "stats count by SESSION_ID" somewhere in the query to get a unique list of SESSION_IDs, but I'm not sure where it needs to go.

0 Karma

jbrenner
Path Finder

Hi Rich, I tried your latest query and added "| stats count by SESSION_ID" to the end, and it runs now, but it's returning the SESSION_IDs of all attempted orders, so the NOT doesn't seem to be working correctly.

Thanks,
Jonathan

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...