topic Re: How to join 2 indexes with a field searched from the first index? in Splunk Search

How to join 2 indexes with a field searched from the first index?

arungeorge09 — Mon, 28 Sep 2020 18:07:54 GMT

I have 2 indexes and would like to join them with a common field and the names are not same . I tried all posts with join but was unable to do it.

Here is the query I tried without any luck/

Index1 has field name as batch and index2 has field name as batch_id and named differently in both indexes(batch & batch_id). The batch field is got through a rex query but evaluated to add to the field list. Please help.

earliest="@w0" index=slingneat event="pushApns" 25597 
| rex field=_raw "\\\\\"time\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"batch\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"tms_id\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"src\\\\\":\\\\\"(?.*?)\\\\\"" 
| eval batch=batch
| eval tms_id=tms_id 
| eval time=time 
| eval src=src 
| eval batch_id=batch 
| join batch_id [ search index= analyticslogs table_name="neat"  ] 
| table batch_id, client_version

[Edited]
I am able to get the query running by making the fields common.Thanks for the answer. I cant search on both indexes because only batch_id is common between the indexes. So I have to find first batch_id for condition in index1 and then search in other index.

Re: How to join 2 indexes with a field searched from the first index?

MuS — Fri, 14 Nov 2014 08:08:55 GMT

Hi arungeorge09,

looks a bit over-complicated what you're doing here.

Using your provide event examples, I created two files and indexed them. Using those indexed events I was able to get your result by using a very simple single search like this:

earliest="@w0" ( index=slingneat event="push*" ) OR ( index= analyticslogs table_name="neat" )
| rex field=_raw "time.+?\"(?P<time>\d+).+?batch.+?\"(?P<batch_id>\d+)" 
| eval alert_text=urldecode(alert_text) | eval formatted_time=strftime(time/1000,"%F %T") 
| eval neat_client_app=if(neat_client_app!="", neat_client_app, "N/A")
| streamstats last(alert_text) AS alert_text last(os_ver) AS os_ver 
| stats count(evant) AS noofalerts by neat_client_app, os_ver, batch_id, formatted_time, alert_text

You're using eval to create fields which are not used afterwards, so I skipped them. Also, my eval for neat_client_app is just there because I have no clue where you got this field from.

Try it and you will see that it will run much faster.

hope this helps ...

cheers, MuS

Re: How to join 2 indexes with a field searched from the first index?

arungeorge09 — Fri, 14 Nov 2014 09:51:35 GMT

@Mus
How do I use mulitple fields for join. Can you post a link.

Re: How to join 2 indexes with a field searched from the first index?

MuS — Fri, 14 Nov 2014 10:14:33 GMT

can you provide two sample events from each index?

Re: How to join 2 indexes with a field searched from the first index?

arungeorge09 — Mon, 17 Nov 2014 06:55:40 GMT

First Index

<167>1 2014-11-15T16:45:44.542-07:00 cpneat05.sling.com neat 11151 gcm [meta@28281 sequenceId="43096" sysUpTime="858744854"][analytics@28281 event="pushGcm" platform="GCM" outcome="0" errorCode="0" errorDesc="Push to apns success" errorContext="TCP-SSL" operation="PUSH_GCM" opTime="46" startTime="1416095144542" appId="appId" deviceToken="token" args="{\"time\":\"1416095144194\",\"batch\":\"26966\",\"tms_id\":\"tmsId\",\"src\":\"src\"}" txId="907472412"]

Second Index data

68.230.42.81 - - [15/Nov/2014:15:45:46 -0800] "POST /da/vm HTTP/1.1" 200 30 "-" "da-android-tablet/4.1.22" "-" "unique_user=3cdee44a7b555a8486f3680f33d46e336c82a38b935ecf652357c47b1a02aef0&client_id=DRA_Android_Tablet&client_version=4.1.22&player_instance_id=99000445086028&os_name=Android&os_ver=4.4.2&partner=dish&app_id=appId&HHID=A253E6EB4D041C43E044001A4B0AA2BC&bundle_id=bundleId&lang=en&friendly_name=SM-T217S&table_name=neat&pns_registration_token=deviceToken&pns_platform=GCM&source_system=src&neat_alert_id=118124209&alert_metadata=%7b%22time%22:%221416095144194%22%2c%22batch%22:%2226966%22%2c%22tms_id%22:%22tmsId%22%2c%22src%22:%22src%22%7d&arrive_timestamp=1416095145&action=Media%20Card&pns_id=467842546567&alert_text=Exciting%20Game:%20Ducks%20at%20Kings%0a2-2%2c%20End%20of%203rd%20pd%0aKopitar%20power%20play%20goal%20ties%20it%20for%20Kings%2c%20forcing%20OT.&batch_id=26966&device_id=24d1d65a1b00f472&submit_timestamp=1416095144194&"

Re: How to join 2 indexes with a field searched from the first index?

arungeorge09 — Mon, 17 Nov 2014 06:56:11 GMT

I have my working query now which is pretty complex but does the job 😉

earliest="@w0" index=slingneat event="push*" | rex field=_raw "\\\\\"time\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"batch\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"tms_id\\\\\":\\\\\"(?.*?)\\\\\",\\\\\"src\\\\\":\\\\\"(?.*?)\\\\\"" | eval batch=batch|eval tms_id=tms_id | eval time=time | eval src=src | eval batch_id=batch | eval pns_platform=platform|eval batchPlatform=batch."#".platform| join pns_platform [search index= analyticslogs table_name="neat" ]|eval alert_text=urldecode(alert_text)|eval formatted_time=strftime(time/1000,"%F %T") |stats count(event) as noofalerts by neat_client_app,os_ver,batch, formatted_time,alert_text| table neat_client_app ,os_ver, formatted_time,alert_text,batch, noofalerts

Re: How to join 2 indexes with a field searched from the first index?

MuS — Mon, 17 Nov 2014 09:18:14 GMT

where is the field neat_client_app coming from?

Re: How to join 2 indexes with a field searched from the first index?

MuS — Wed, 19 Nov 2014 08:23:00 GMT

update ping....

Re: How to join 2 indexes with a field searched from the first index?

arungeorge09 — Mon, 15 Dec 2014 09:45:57 GMT

Its a calculated field

Re: How to join 2 indexes with a field searched from the first index?

MuS — Mon, 15 Dec 2014 09:49:14 GMT

Never mind, I used your provided samples and created a simple search which you can use. It's the one in the answers 😉