Dashboards & Visualizations

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

nivets
Engager

 

 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

Labels (1)
Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...