Splunk Search

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

arungeorge09
Path Finder

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.

Tags (2)
0 Karma

MuS
SplunkTrust
SplunkTrust

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

MuS
SplunkTrust
SplunkTrust

update ping....

0 Karma

arungeorge09
Path Finder

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

0 Karma

MuS
SplunkTrust
SplunkTrust

can you provide two sample events from each index?

0 Karma

arungeorge09
Path Finder

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&"
0 Karma

arungeorge09
Path Finder

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
0 Karma

MuS
SplunkTrust
SplunkTrust

where is the field neat_client_app coming from?

0 Karma

arungeorge09
Path Finder

Its a calculated field

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...