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!

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