Merge Data from two indexes without using Join

[katzr]

Hello,

I know there are many answers on this topic, but I can't seem to find any answer that is working for me. I have one index called index=A which has +200,000 events with a unique ID. I have another index=B that has a smaller number of events with the same unique ID but called uniqueID2 let's say. I need help pulling in a few fields from index=A into index=B for the matching uniqueID to uniqueID2. Join isn't working and is too slow. I tried using stats with eval case, but I can't seem to figure it out.

What I want to ultimately do is run searches and aggregate the data by the fields in index=A and the data in index=B. For example index A contains Region and index=B contains the data I want to group by region.

Thank you for the help!

[DalJeanis]

@katzr - if you'd like a more specific answer, then post a breakdown of the fields on each that you want to have and how you want the grouping to work.

[katzr]

Hi @DAIJeanis thank you for the help. I realized that I didn't phrase the question correctly. index=B has that ID but there are multiple records of ID so I essentially need to use a left join on index=A sort of thing. Does that make sense?

Index=A has unique ID with unique records based on that ID
Index=B has that unique ID but there are multiple records per ID

I need the multiple records per ID with fields from index A added to it.

[DalJeanis]

Assuming that each uniqueID/uniqueID2 is actually unique in both files, and all the other field names are unique...

index=A OR index=B
| fields uniqueID uniqueID2 fieldA1 fieldA2 fieldA3 fieldB1 fieldB2 fieldB3
| rename uniqueID2 as uniqueID
| stats values(*) as * by uniqueID

Now you have a single record for each uniqueID, with all the fields from both files on it. If you want to throw away any records that were not matched, then you can add this...

 | where mvcount(index)=2

---

This next strategy is for a slightly more complicated situation, where you have some fields that have the same name, but different data that you need to retain, on the two indexes.

index=A OR index=B
| fields uniqueID uniqueID2 fieldX1 FieldOnBothButOnlyWantA fieldA1 fieldA2 fieldA3 fieldB1 fieldB2 fieldB3
| rename uniqueID2 as uniqueID
| eval fieldX1A=if(index="A",fieldX1,null())
| eval fieldX1B=if(index="B",fieldX1,null())
| eval FieldOnBothButOnlyWantA=if(index="A",FieldOnBothButOnlyWantA ,null())
| stats values(*) as * by uniqueID