Splunk Search

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

Explorer

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

Engager

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

[ search index=myindex "def"
| rex field=
raw "(?pattern)"
| stats count as "Successful" by SESSIONID
**| eval SESSION
ID1=SESSION_ID**
| eval status2 = "Active" ]

with | eval SESSIONID1!=SESSIONID

0 Karma

SplunkTrust
SplunkTrust

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

Explorer

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

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, an upvote would be appreciated.
0 Karma

Explorer

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

SplunkTrust
SplunkTrust

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

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

Explorer

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

0 Karma

Explorer

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

Thanks,
Jonathan

0 Karma