topic Re: How to reconcile a field in two different sourcetypes? in Splunk Search

How to reconcile a field in two different sourcetypes?

leonjxtan — Tue, 23 May 2017 12:59:02 GMT

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.

Re: How to reconcile a field in two different sourcetypes?

dineshraj9 — Tue, 23 May 2017 13:02:59 GMT

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

Re: How to reconcile a field in two different sourcetypes?

leonjxtan — Tue, 23 May 2017 13:22:07 GMT

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

Re: How to reconcile a field in two different sourcetypes?

knielsen — Tue, 23 May 2017 13:42:37 GMT

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

Re: How to reconcile a field in two different sourcetypes?

dineshraj9 — Tue, 23 May 2017 13:49:54 GMT

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

Re: How to reconcile a field in two different sourcetypes?

woodcock — Tue, 23 May 2017 21:51:26 GMT

Like this:

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

Re: How to reconcile a field in two different sourcetypes?

gvnd — Wed, 24 May 2017 09:15:02 GMT

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