Splunk Search
Highlighted

Splunk Join query to find common values ?

Communicator

i have two source A and B

Log A:

REQUESTTS="2018-02-16 01:20:05.303" REPLYTS="2018-02-16 01:20:05.53" SENDERID=RT00267C TRANTYPE=270 COMMONAPPSTATUS=Error COMMONAPPMSGID=INPUTERROR:PP3 LEGACYSYSTEMNAME= GUID=4c903502:1619ba64276:-27f2 CLIENTGUID=4c903502:1619ba64276:-27f3 CONTROLNUMBER=000000001

Log B

CSGUID=4c903502:1619ba64276:-27f2 STATUS=Error ERRORCODE=Y42 ERRORMESSAGE= ERRORMESSAGEDETAILS= MESSAGETS="2018-02-16 01:20:05.619"

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

can any one help on this query?

0 Karma
Highlighted

Re: Splunk Join query to find common values ?

Super Champion

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

View solution in original post

Highlighted

Re: Splunk Join query to find common values ?

SplunkTrust
SplunkTrust

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

Highlighted

Re: Splunk Join query to find common values ?

Explorer

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 IDXA has the field event.tssrcip which is wrong and should be the destip. The IDXB has the field destip and has the correct srcip field, which is what I'm trying to get from this join.

0 Karma