Optimize a search without using join

[jonathan_yan5]

Hello,

Hope you can give an solution to my concern.
There were different sourcetypes under a single index and they have a similar field called BATCH_ID, "Sourcetype A" is coming from a database input (dump) and "Sourcetype B" is from a DB input (tail). is it possible to match UNIQUE values under sourcetype A with sourcetype B and exclude those that were not present in Sourcetype A under a single field without using "join"?

My search below takes time to load results on the browser:
index=AAA sourcetype="star_transaction_logs" BATCH_ID=* AGENCY_CODE=* EMPLOYEE_NO=* SERVICE_CODE=WHTLST SE_RESPCODE=0000 | join BATCH_ID AGENCY_CODE EMPLOYEE_NO [search index=AAA sourcetype=star_employees_history ACTION_TYPE=A BATCH_ID=* AGENCY_CODE=* EMPLOYEE_NO=* | join BRANCH_CODE [search index=mls_index sourcetype="star_branches_sourcetype" BRANCH_CODE=*] ] |dedup BATCH_ID | stats count(BATCH_ID) as COUNT by BRANCH_CODE BRANCH_NAME| addcoltotals label=Total labelfield=category COUNT | fields BRANCH_CODE BRANCH_NAME category COUNT | sort BRANCH_NAME

[MuS · Accepted solution]

Hi jonathan_yan5,

Sure it is possible, take a closer look at this answer http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... to learn more about it.

cheers, MuS

[jonathan_yan5]

Thanks MuS!.. it successfully matched a specific field with values on two different sourcetypes. Can you also give the search wherein i could match values on 3 different fields existing on two different sourcetypes under a single query? Basically i should be able to match BATCH_ID, AGENCY_CODE and EMPLOYEE_NO on my report

Sourcetype A
Field BATCH_ID = ABC
Field AGENCY_CODE = XYZ
Field EMPLOYEE_NO = 123

should match:

Sourcetype B
Field BATCH_ID = ABC
Field AGENCY_CODE = XYZ
Field EMPLOYEE_NO = 123