topic Re: Joining and grouping indexes in Splunk Search

Joining and grouping indexes

kp_pl — Tue, 18 Jun 2024 06:51:50 GMT

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 ?

Re: Joining and grouping indexes

glc_slash_it — Tue, 18 Jun 2024 07:21:04 GMT

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-common-field/m-p/321060

Re: Joining and grouping indexes

kp_pl — Fri, 21 Jun 2024 11:17:43 GMT

😞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 ... 🤔

Re: Joining and grouping indexes

kp_pl — Fri, 21 Jun 2024 11:44:40 GMT

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