topic Re: Splunk Join query to find common values ? in Splunk Search

Splunk Join query to find common values ?

karthi2809 — Tue, 29 Sep 2020 18:04:33 GMT

i have two source A and B

Log A:

REQUEST_TS="2018-02-16 01:20:05.303" REPLY_TS="2018-02-16 01:20:05.53" SENDER_ID=RT00267C TRAN_TYPE=270 COMMONAPP_STATUS=Error COMMONAPP_MSGID=INPUTERROR:PP3 LEGACY_SYSTEM_NAME= GUID=4c903502:1619ba64276:-27f2 CLIENT_GUID=4c903502:1619ba64276:-27f3 CONTROL_NUMBER=000000001

Log B

CS_GUID=4c903502:1619ba64276:-27f2 STATUS=Error ERROR_CODE=Y42 ERROR_MESSAGE= ERROR_MESSAGE_DETAILS= MESSAGE_TS="2018-02-16 01:20:05.619"

IN this GUID and CS_GUID is common values .compare the two source and get the ERROR_CODE=Y42.And to get stats count by LEGACY_SYSTEM_NAME ERROR_CODE

can any one help on this query?

Re: Splunk Join query to find common values ?

493669 — Fri, 16 Feb 2018 06:37:25 GMT

Try like this:

source="A"|rename GUID as CS_GUID| join CS_GUID[search source="B"|search ERROR_CODE="Y42"]|stats count by LEGACY_SYSTEM_NAME ERROR_CODE

Re: Splunk Join query to find common values ?

MuS — Sun, 18 Feb 2018 19:42:31 GMT

Hi karthi2809,

Sorry, to jump on this already answered question, but you should not use join for multiple reasons like subsearch limits and timeouts you can hit without even noticing.

Your example can be solved like this:

( source="A" LEGACY_SYSTEM_NAME=* GUID=* ) OR ( source="B" ERROR_CODE="Y42" CS_GUID ) 
| eval UID=case(isnotnull(GUID), GUID, isnotnull(CS_GUID), CS_GUID, 1=1, "unknown")
| stats values(*) AS * by UID
| stats count by LEGACY_SYSTEM_NAME ERROR_CODE

Take a look at this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html to learn more or read the March 2016 virtual .conf session http://wiki.splunk.com/Virtual_.conf from Nick Mealy about Best practices around grouping and aggregating data from different search results

Hope this helps ...

cheers, MuS

Re: Splunk Join query to find common values ?

seomisp — Tue, 29 Sep 2020 21:22:22 GMT

I'm having the same problem. Mine doesn't work with the inner or left join, although I can see the event from the left join, but without the fields from the other source.

index=IDX_A event.url = "http://some.url"
| rename event.ts_srcip as dest_ip
| join dest_ip [search index=IDX_B]

I can confirm the main query and the subsearch, return results when executed separately. The IDX_A has the field event.ts_srcip which is wrong and should be the dest_ip. The IDX_B has the field dest_ip and has the correct src_ip field, which is what I'm trying to get from this join.