Find missing ids from two searches using stats not set

[bowesmana]

I have an index with two 'transaction types'. Create and Offer. For each create, I get an ID and I want to find out all created Ids that do not get an offer. I am making multiple 'Offer' requests, so there can be more than one Offer row for each i_bid. I can do it with set diff using

| set diff 
[ search index=web_load sourcetype=instrument i_tx=Create | stats count by i_bid | table i_bid ]
[ search index=web_load sourcetype=instrument i_tx=Offer i_status=0 | stats count by i_bid | table i_bid ]
| table i_bid

and I get a table with the created ids from search (Create) that do not have an offer in search 2.

However, I'd like to get this using stats if possible for efficiency as there will be hundreds of thousands of Create operations and set subsearches are limited to the default 10,000.

I can create tables of i_bid and i_tx using

index=web_load sourcetype=instrument i_status=0 i_tx=Create OR i_tx=Offer
| stats count by i_tx, i_bid

so, feel it ought to be possible to remove common i_bid

[sundareshr · Accepted solution]

Try this

.... | chart count over i_bid by i_tx | where Offer=0

[bowesmana]

I got to this

| stats count by i_bid | where count=1

which worked, but yours does to, thanks a lot.