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

[jbrenner]

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

[dsha]

@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

[mayurr98]

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!

[jbrenner]

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.

[richgalloway]

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.

[jbrenner]

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

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

[richgalloway]

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

[jbrenner]

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.

[jbrenner]

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