Splunk Search

Searching multiple indexes to get the ID-Value and other associated columns

Explorer

Hi there,

I'm trying to join two indexes to get the id-value and ingest the data into main index. Here is my scenario:

SPL: index=idx1
Output columns: log
id, logdesc, logdate, custid, rgnid

SPL: index= idx2 sourcetype=RGNST source=RGN
Output columns: rgnid, rgnname, rgntype, lookupname

SPL: index= idx2 sourcetype=CUSTST source=CUST
Output columns: custid, custname, custphone, lookupname

Now, I'm running this query to get all the values. It shows the columns (logid, logdesc, logdate, custid, rgnid, rgnname, rgntype, custid, custname, custphone, lookupname ) but does not populate the id-values coming from the idx2 index. Please let me know if I'm doing anything wrong here.

index=idx1
| dedup _raw
| join cust
id type=left
[ search index=idx2 sourcetype=RGNST source=RGN lookupname="customer" ]
| join rgn
id type=left
[ search index=idx2 sourcetype=CUSTST source=CUST lookup_name="region" ]
| fields - _raw
| fillnull value="-"
| table *

0 Karma

SplunkTrust
SplunkTrust

Hi kulsplunk,

have a look here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... , where it is explained how it can be done. Basically you can start with this base search:

 index=idx_1 OR ( index=idx_2 sourcetype=RGN_ST source=RGN lookup_name="region" ) OR ( index=idx_2 sourcetype=CUST_ST source=CUST lookup_name="customer")

next is using eval to normalise the fields if needed, otherwise just use a stats to get the values by cust_id, rgn_id

 | stats values(*) AS * by cust_id, rgn_id

Hope this helps to get you started ...

cheers, MuS

Explorer

Thanks MuS for your response! I'm not trying to join the indexes to get the UNION of two sets, but I wanted to do the left join to get the specific details of given custid/rgnid. The problem I'm facing is with bigger set of data. If the data-set is smaller then I get the desired results but if the data is in few millions, then the sub-search does not work.

0 Karma

Explorer

Sorry! here is updated query that I wanted to post.

index=idx1
| dedup _raw
| join cust
id type=left
[ search index=idx2 sourcetype=RGNST source=RGN lookupname="region" ]
| join rgn
id type=left
[ search index=idx2 sourcetype=CUSTST source=CUST lookup_name="customer" ]
| fields - _raw
| fillnull value="-"
| table *

0 Karma