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.
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
update ping....
@Mus
How do I use mulitple fields for join. Can you post a link.
can you provide two sample events from each index?
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&"
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
where is the field neat_client_app
coming from?
Its a calculated field
Never mind, I used your provided samples and created a simple search which you can use. It's the one in the answers 😉