Joining and grouping indexes

[kp_pl]

below is my scenario described by Oracle DBA 😉

I have two indexes

INDEXA
fieldA
fieldB
fieldC

INDEXB
fieldA
fieldX
fieldY
fieldZ

First I need to join them both, it will be kind of LEFT JOIN as you porbably noticed by fieldA. Then group it by filedA+FieldZ and count each group.

In DBA language something like :
select a.fieldA, b.filedZ, count(*)
from indexA A left join indexB B on a.fieldA=b.fieldA
group by a.fieldA, b.filedZ

any hints ?

K.

[glc_slash_it]

Usually, instead of using join, you can replace it by stats and will be a lot better in performance.

Try to do something like this and adjust it to your needs:

index=INDEXA OR index=INDEXA
| stats values(fieldB) AS fieldB values(fieldC) AS fieldC  values(fieldX) AS fieldX values(fieldY) AS fieldY values(fieldZ) AS fieldZ by fieldA
| fillnull value=unknown fieldZ 
| stats count(fieldB) AS fieldB count(fieldC) AS fieldC  count(fieldX) AS fieldX count(fieldY) AS fieldY by fieldA, fieldZ 

First use OR to merge the info from both indexes and use stats to group the other fields by fieldA.

Then, assuming there will be gaps of information in some fiels, usa can use fillnull to fill those gaps.

Then, count all fields by fieldA and fieldZ.

Also check this post:

https://community.splunk.com/t5/Splunk-Search/Replace-join-with-stats-to-merge-events-based-on-commo...

[kp_pl]

😞completely different logic than in relational databases. It takes me a time to switch for this "new" one .

Ok, one more condition I noticed.
Two indexes are linked by field FieldA. The point is that FieldA in IndexB needs to be converted to :

| eval ModA = mvindex(split(FieldA, ","), 0)

So the relation one_to_many is IndexA.FieldA = IndexB.ModA


is this clear what I am writing about ... 🤔

[kp_pl]

Ok, to simplify it as easy as possible :

There are two indexes:
INDEXA
FieldA
FieldB

INDEXB
FieldA
FieldC

to create a relation between indexes I need to modify INDEXB.FieldA

eval FieldA1 = mvindex(split(FieldA, ","), 0)

and now want to group by FieldA/FieldA1and FieldB and count FieldC