topic Combine 2 indexes with join based on result of a substring search in Splunk Search

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

diliptmonson — Fri, 19 Feb 2016 17:29:13 GMT

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

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

somesoni2 — Fri, 19 Feb 2016 17:40:18 GMT

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

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

diliptmonson — Fri, 19 Feb 2016 18:52:52 GMT

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

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

somesoni2 — Fri, 19 Feb 2016 19:17:47 GMT

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