i have two source A and B
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
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?
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
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 ...
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.