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

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

jbrenner — Thu, 22 Mar 2018 19:08:21 GMT

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

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

richgalloway — Thu, 22 Mar 2018 19:24:14 GMT

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.

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

jbrenner — Thu, 22 Mar 2018 20:12:28 GMT

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

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

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

jbrenner — Tue, 29 Sep 2020 18:39:25 GMT

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.

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

mayurr98 — Fri, 23 Mar 2018 03:20:10 GMT

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!

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

richgalloway — Fri, 23 Mar 2018 03:58:11 GMT

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

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

jbrenner — Tue, 29 Sep 2020 18:38:43 GMT

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

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

jbrenner — Fri, 23 Mar 2018 17:20:27 GMT

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.

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

dsha — Tue, 29 Sep 2020 21:58:53 GMT

@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