corelating two different data sets

[selvam_sekar]

Hi,

I am trying to get the execution count based on the parentIDs over two different data sets. Please could you review and suggest ? 

I would like to see what's execution count  between (sourcetype=cs, sourcetype=ma) , only the field ParentOrderID is common between cs, ma sourcetype.

Note: daily close to ~10Million events are loaded  into splunk and unique execution will be 4Million.Also, sometime the join query is getting auto-canceled.

SPL:

index=india sourcetype=ma NOT (source=*OPT* OR app_instance=MA_DROP_SESSION OR "11555=Y-NOBK" OR fix_applicationInstanceID IN(*OPT*,*GWIM*)) msgType=8 (execType=1 OR execType=2 OR execType=F) stream=Outgoing app_instance=UPSTREAM "clientid=XAC*"
| dedup fix_execID,ParentOrderID
| stats count
| join ParentOrderID
[ search index=india sourcetype=cs NOT (source=*OPT* OR "11555=Y-NOBK" OR applicationInstanceID IN(*OPT*,*GWIM*)) msgType=8 (execType=1 OR execType=2 OR execType=F) app_instance=PUBHUB stream=Outgoing "clientid=XAC" "sourceid=AX_DN_XAC"
| dedup execID,ParentOrderID
| stats count]

Thanks,

Selvam.

[ITWhisperer · Accepted solution]

Sub-searches e.g. those used by join, are limited, so you could try combining the initial search like so

index=india (sourcetype=ma NOT (source=*OPT* OR app_instance=MA_DROP_SESSION OR "11555=Y-NOBK" OR fix_applicationInstanceID IN(*OPT*,*GWIM*)) msgType=8 (execType=1 OR execType=2 OR execType=F) stream=Outgoing app_instance=UPSTREAM "clientid=XAC*") OR (sourcetype=cs NOT (source=*OPT* OR "11555=Y-NOBK" OR applicationInstanceID IN(*OPT*,*GWIM*)) msgType=8 (execType=1 OR execType=2 OR execType=F) app_instance=PUBHUB stream=Outgoing "clientid=XAC" "sourceid=AX_DN_XAC")

Next you have to work out what is meant by your dedup. For example, if you rename fix_execID as execID, you could do your dedup like this

| stats count execID ParentOrderID sourcetype

Next problem is your join (apart from avoiding joins in the first place (with the combined initial search), your two searches do not return ParentOrderID since they both end with stats count, therefore the only field you have to join with is count, and I suspect this is not what you require?

[selvam_sekar]

@ITWhisperer  thank you.

I am trying to get the total execution id count between the different sourcetype, where parent id is equal.  As per the design, sourcetype=ma execution will be higher than sourcetype=cs.

But, i want to get execution count of sourcetype=ma that has sent to sourcetype=cs.

[ITWhisperer]

``` Set a flag based on sourcetype ```
| eval flag=if(sourcetype="ma",1,2)
``` Get single event for each ParentOrderID by sourcetype (dedup) ```
| stats vakues(flag) as flag by ParentOrderID sourcetype
``` Add flags from both sourcetypes ```
| stats sum(flag) as flags by ParentOrderID
``` Count each type of flag ```
| stats count by flags
``` Flags is 1 for ma only, 2 for cs only, 3 for both ma and cs ```