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!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...