How to get the fields from two searches based on common field in two searches?

[nivets]

 i have two searches in different indexes index itsi_grp* and Index B, and these two searches will have common fieldvalues in hostname and problemdesc fields name index itsi_grp* using these two fields i have to get a "id" field from  index B

For example

Index itsi_grp*

Hostname   Problemdesc   name

AAA                CPU issue          abc

Index B

Host  Problem ID

AAA   CPU          555

My result should like below

Host            problem               ID           incindetnumber     name

AAA              CPU                       555       *******                       abc

Am using the join command to get the results but its not returning any values , even the data is available in both indexes, used below query,

index=itsi_grp* 
|search name="abc"|rename Hostname as Host, Problemdesc as Problem |join Hostname Problem [search index=B sourcetype=abc]
|table Host, Problem, incindetnumber,ID, name

For fetching incindet, i will be using the lookup. The only problem is while using the join command am not getting the results, its returning 0 statistics.  Could you please help me if am using the join command properly

[gcusello]

HI @nivets,

the join command is a very slow command and must be used only when there isn't ani other solution.

so try this different approach to adapt to you use case:

(index=itsi_grp* name="abc") OR (index=B sourcetype=abc)
| eval Hostname=coalesce(Hostname, Host)
| stats 
   values(Problemdesc) AS Problem
   values(incindetnumber) AS incindetnumber
   values(ID) AS ID 
   values(name) AS name
   BY Hostname 

in other words use the common field as grouping key and values for the fields to display.

Then you can use the lookup command to enrich your results with static values from a lookup.

Ciao.

Giuseppe

[ITWhisperer]

You renamed Hostname as Host so you need to use Host in the join not Hostname

index=itsi_grp* 
|search name="abc"|rename Hostname as Host, Problemdesc as Problem |join Host Problem [search index=B sourcetype=abc]
|table Host, Problem, incindetnumber,ID, name

Also, if your example is true to your events, the value in Problem ("CPU") does not match the value in Problemdesc ("CPU issue"), so you won't get a match in this instance.

Additionally, you should try to avoid joins with the join command where possible as it isn't usually very efficient.