Splunk Search

How to populate fields from two indexes that share one field

maryamchar
Explorer

Hello,
I asked this question yesterday but didn't get the right solution. I have two indexes with different fields and only share one common field, I want to have a table where it display some fields form both indexes. So far, it displays fields from one index only, not sure what I'm doing wrong. Here is my attempt.

Note: field 2 and field 3 from index=1 , field 4 and field 5 from index=2 , common field is name

index=1 OR index=2
|eval name=coalescce(name1,name2)
|stats values(field2) as fields2 values(field3) as field3 values(field4) as field4 values(field5) as field5 by name

thank you in advance!!!

Tags (1)
0 Karma
1 Solution

PowerPacked
Builder

Hi

You should use join command to correlate data from one index to data in other index.

index=1 | stats c by name1, field2, field3 | rename name1 as name2| join name2 [| search index=2 | stats c by name2, field4, field5]

other example : index=_internal | stats c by host sourcetype | join type=left host [ | search index=_audit | stats c by host source]

Thanks

View solution in original post

PowerPacked
Builder

Hi

You should use join command to correlate data from one index to data in other index.

index=1 | stats c by name1, field2, field3 | rename name1 as name2| join name2 [| search index=2 | stats c by name2, field4, field5]

other example : index=_internal | stats c by host sourcetype | join type=left host [ | search index=_audit | stats c by host source]

Thanks

maryamchar
Explorer

Thank you!!! It worked!!

0 Karma

adonio
Ultra Champion

not sure why or what you need to coalesce if the split by field is the same ...
index = a or index = b name=* |stats values(field2) as fields2 values(field3) as field3 values(field4) as field4 values(field5) as field5 by name

0 Karma

maryamchar
Explorer

I need coalesce because it's the same field but named differently in each index. however, they both have same data. The query you provided didn't work

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...