Sub-search not yielding results in SQL

[seemakurthy]

The search below does not yield results like NOT IN SQL. Any suggestion please.

earliest=06/19/2019:23:00:00 latest=06/20/2019:15:00:00 index="cf-pci" cf_app_name="order-event-publisher*" "Posted event message for OrderId*" | rex field=_raw "Posted event message for OrderId (?[A-Z0-9]*), versionId (?[0-9]*)"| eval return =  OrdNo."/".VerId| dedup return| stats count by return | table return 
|search NOT 
[ search earliest=06/19/2019:23:00:00 latest=06/20/2019:16:00:00 index=pt-supply-chain-visibility sourcetype=scv-listener-oms:application:access  "message.data.status"=processed | rename message.data.domainId as  OrdNo message.data.versionId as VerId | eval return =  OrdNo."/".VerId| dedup return| stats count by return| fields return]

[woodcock]

Stop thinking SQL; try this:

(index="cf-pci" cf_app_name="order-event-publisher*" "Posted event message for OrderId*") OR
(index=pt-supply-chain-visibility sourcetype=scv-listener-oms:application:access  "message.data.status"=processed)
| rex "Posted event message for OrderId (?<OrderNo>[A-Z0-9]*), versionId (?<VerId>[0-9]*)"
| eval return = colesce(OrdNo,message.data.domainId) . "/" . colesce(VerId, message.data.versionId)
| stats dc(index) AS index_count values(index) AS index BY return
| where index_count==1

[jnudell_2]

Could you please add more details about what you are trying to accomplish with this search?

[seemakurthy]

I have two containers which are exchanging data, i am reconciling the data exchange between them. The top query gives me sender events, the sub query gives me receiver events. I am checking for events which are sent by producer and not received by the consumer.
Producer:-
earliest=06/19/2019:23:00:00 latest=06/20/2019:15:00:00 index="cf-pci" cf_app_name="order-event-publisher*" "Posted event message for OrderId*" | rex field=_raw "Posted event message for OrderId (?[A-Z0-9]), versionId (?[0-9])"| eval return = OrdNo."/".VerId| dedup return| stats count by return | table return

Consumer:-
search earliest=06/19/2019:23:00:00 latest=06/20/2019:16:00:00 index=pt-supply-chain-visibility sourcetype=scv-listener-oms:application:access "message.data.status"=processed | rename message.data.domainId as OrdNo message.data.versionId as VerId | eval return = OrdNo."/".VerId| dedup return| stats count by return| fields return]