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
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
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.