Splunk Join not working

[coolkris]

I am trying to create a result set out of 2 search queries with a common field.I have tried multiple solutions provided but nothing seems to work. The queries have separate indexes and separate source types and the 1st search query searches for a particular text.

index = i1 sourcetype=s1 "Text to search" | join type ="left" field1 [ search index = i2 sourcetype = s2 app_message = "Completed" ] table field1 field2 field3

In the above query field1 is the common field and field2 and field3 are coming from the 2nd search query. But somehow this query produces values for field1 and no values for field2 and field3.

[MuS]

Hi coolkris,

try something like this:

( index = i1 sourcetype=s1 "Text to search" ) OR ( index = i2 sourcetype = s2 app_message = "Completed" )
| stats values(field2) AS field2 values(field3) AS field3 by field1

this will search both indexes and sourcetypes and lists values of field2 and field3 grouped by field1.

Hope this helps ...

cheers, MuS

[coolkris]

Thanks for the answer. But even after executing your query, the values for field2 and field3 are displaying null. But the 2nd query as separate are showing values for field1. When I include in join, its showing null.
In the join query, when I see all fields available in the events tab, i see only fields from 1st query.

[MuS]

Can you please post samples for both results, make sure they are masked before posting and please explain what in the events represents which field if they are not key value pairs.

cheers, MuS