How to join two searches with no common field?

[jwhughes58]

I have two searches

search 1 -> index=myIndex sourcetype=st1 field_1=*
search 2 -> index=myIndex sourcetype=st2

Fields:

search 1 -> externalId
search 2 -> _id

The information in externalId and _id are the same. I've been trying to use that fact to join the results.

I tried both of these

index=myIndex sourcetype=st1 field_1=*
| rename externalId as _id
| join _id [search index=myIndex sourcetype=st2]

index=myIndex sourcetype=st1 field_1=*
| rename externalId as st1_id
| join st1_id [search index=myIndex sourcetype=st2 | rename _id AS st1_id]

They didn't work for me. I'm think the field id is causing me issues since `*` are normally Splunk internal variables. Any thoughts on how I should approach this?

TIA
Joe

[DalJeanis]

Okay, there's lots of ways, but here's what I would do first.

Pick ONE ID that you think is in both searches. Let's say the value is "George". Do this -

First...

 index=myIndex sourcetype=st1 field_1=* externalId="George"

And second...

index=myIndex sourcetype=st2 _id="George"

See if you get at least one result record from each of the above searches.

If so, then continue with this

 index=myIndex sourcetype=st1 field_1=* externalId="George"
| table externalId 
| join type=left externalId  
    [ index=myIndex sourcetype=st2 _id="George" | stats count as reccount by  _id  | rename _id as externalId]
| table externalId reccount

And see what happens.

[adonio]

very good suggestion here to try and start with 1 result!
would upvote more then once. it saves time to solution

[kmccririe_splun · Accepted solution]

I think what you're trying to do can be accomplished with field aliases. https://docs.splunk.com/Documentation/Splunk/6.5.2/Knowledge/Abouttagsandaliases#Field_aliases

Here is how to do it. https://docs.splunk.com/Documentation/Splunk/6.5.2/Knowledge/Addaliasestofields

You would create a field alias so both externald and _id would map to a new field. Then you can use that field for searches.

[jwhughes58]

I would have never thought of that one. Once I put the two field aliases in place

st1 : FIELDALIAS-st1_common_id
st2 : FIELDALIAS-st2_common_id

I was able to run the below

index=myIndex sourcetype=st1 field_1=*
| join common_id [search index=myIndex sourcetype=st2]

and get data. Something I will remember if I run into this type of issue again.

Thanks.