Splunk Search

Splunk Join query to find common values ?

karthi2809
Contributor

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?

0 Karma
1 Solution

493669
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

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

0 Karma

MuS
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

493669
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...