Splunk Search

Combine 2 indexes with join based on result of a substring search

diliptmonson
Explorer

I have 2 indexes:
First index:
index= abc with field1 having values like "\A,\B,\C" and "\A,\D" and so on
Second index:
index=def with field 2 having values like A, F and so on.

Now I have to join the 2 indexes using field1 and field2 such that
1.Search field1 of index abc for the value of field2 in index def (For instance a match happens when A in field2 is same as \A in field1)
2. When a match happens i need to combine these 2 rows using a join

Can anyone please provide me guidance on how to approach this

0 Karma

somesoni2
Revered Legend

Not sure how efficient it'll be, but try something like this (this is assuming you've field4 single valued field (just one value) and field1 multivalued field OR contains multiple values.

index=abc OR index=def   | eval commonfield=coalesce(field1,field4) | makemv commonfield delim="," | mvexpand commonfield | stats list(*) as * by commonfield | where isnotnull(field4) | eval temp=mvzip(field4,field5,"#") | mvexpand temp | rex field=temp "(?<field4>.*)#(?<field5>.*)"  | fields - temp commonfield

See this run anywhere sample with your example data.(first 3 rows are just to generate data)

| gentimes start=-1 | eval temp="A,B,C Def Dpx;D,E Ghi Abc;F Ghi " | table temp| makemv temp delim=";" | mvexpand temp | rex field=temp "(?<field1>.*)\s(?<field2>.*)\s(?<field3>.*)" | append [| gentimes start=-1 | eval temp="A 123;D 245;A 343" | table temp| makemv temp delim=";" | mvexpand temp | rex field=temp "(?<field4>.*)\s(?<field5>.*)" ] | fields - temp 
| eval commonfield=coalesce(field1,field4) | makemv commonfield delim="," | mvexpand commonfield | stats list(*) as * by commonfield | where isnotnull(field4) | eval temp=mvzip(field4,field5,"#") | mvexpand temp | rex field=temp "(?<field4>.*)#(?<field5>.*)"  | fields - temp commonfield
0 Karma

somesoni2
Revered Legend

What all fields you want to join? Could you provide the fields from both indexes that you need in final output?

0 Karma

diliptmonson
Explorer

Hi Somesoni2,
These are the 2 indexes I need to combine:

index= abc;
field1 - "\A,\B,\C";"\D,\E";"\F"
field2 - Def, Ghj, Klm
field3- Dpx,Abc,

index=def;
field4= "A","D","A"
field5=123,245, 571

Now I need to combine both these indexes together such that the result obtained is
Row 1: "\A,\B,\C",Def,Dpx,"A",123
Row2: "\D,\E";"\F",Ghj,Abc,
Row3:"\A,\B,\C",Def,Dpx,"A",571

Thanks a lot for your help... 😉

0 Karma
Get Updates on the Splunk Community!

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...