Splunk Search

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

kulsplunk
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=idx_1
Output columns: log_id, log_desc, log_date, cust_id, rgn_id

SPL: index= idx_2 sourcetype=RGN_ST source=RGN
Output columns: rgn_id, rgn_name, rgn_type, lookup_name

SPL: index= idx_2 sourcetype=CUST_ST source=CUST
Output columns: cust_id, cust_name, cust_phone, lookup_name

Now, I'm running this query to get all the values. It shows the columns (log_id, log_desc, log_date, cust_id, rgn_id, rgn_name, rgn_type, cust_id, cust_name, cust_phone, lookup_name ) but does not populate the id-values coming from the idx_2 index. Please let me know if I'm doing anything wrong here.

index=idx_1
| dedup _raw
| join cust_id type=left
[ search index=idx_2 sourcetype=RGN_ST source=RGN lookup_name="customer" ]
| join rgn_id type=left
[ search index=idx_2 sourcetype=CUST_ST source=CUST lookup_name="region" ]
| fields - _raw
| fillnull value="-"
| table *

0 Karma

MuS
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

kulsplunk
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 cust_id/rgn_id. 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

kulsplunk
Explorer

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

index=idx_1
| dedup _raw
| join cust_id type=left
[ search index=idx_2 sourcetype=RGN_ST source=RGN lookup_name="region" ]
| join rgn_id type=left
[ search index=idx_2 sourcetype=CUST_ST source=CUST lookup_name="customer" ]
| fields - _raw
| fillnull value="-"
| table *

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!