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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...