Splunk Enterprise

corelating two different data sets

selvam_sekar
Path Finder

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.

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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?

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

selvam_sekar
Path Finder

@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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
``` 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 ```
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...