How to reconcile a field in two different sourcetypes?

[leonjxtan]

My use case is:
There is sourcetype1, which has tradeID field; also sourcetype2, which also has tradeID field.

I think sourcetype2 should be a subset of sourcetype1, and I want to do reconciliation.
How to write a search so that it returns all tradeID in sourcetyp1, but not in sourcetype2?

Thanks.

[gvnd]

Try this one:

index=* sourcetype=sourcetype1 OR sourcetype=sourcetype2
| stats dc(sourcetype as sourcetypes values(sourcetype) as sourcetype by tradeID
| search sourcetype=sourcetype1 AND sourcetypes= 2

[woodcock · Accepted solution]

Like this:

index=foo sourcetype=sourcetype1 OR sourcetype=sourcetype2
| eval tradeID=if((sourcetype=sourcetype1), tradeID, null())
| Your Other Stuff Here

[knielsen]

Try this:

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | stats count by tradeID,sourcetype | xyseries tradeID sourcetype count | fillnull sourcetype1 sourcetype
 2 | search sourcetype1>0 sourcetype2=0 | fields tradeID

[dineshraj9]

You can use a subsearch to find all tradeID in sourcetype2 and filter them from sourcetype1 -

sourcetype=sourcetype1 NOT [ search sourcetype=sourcetype2 | dedup tradeID | table tradeID ] | dedup tradeID | table tradeID

[leonjxtan]

Thanks.

I tried this search, but strangely 9 seconds are spent on parsing the search. Is it normal for sub-search?

918.18 startup.handoff

[dineshraj9]

This approach should be faster -

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | eval flag=if(sourcetype=sourcetype2,1,0) | stats sum(flag) as flag by traceID | where flag=0