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

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

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...