topic Re: search two indexes specifying two matching columns in Splunk Search

search two indexes specifying two matching columns

the_dude — Tue, 14 Nov 2023 18:57:23 GMT

I am trying to generate three reports with stats. The first is where jedi and sith have matching columns. The third is where jedi and sith do not match. Example:

index=jedi
| table saber_color, Jname, strengths

index-=sith
| table saber_color, Sname, strengths

I need to list where Jname=Sname

The third one is

where the Jname!=Sname

The caveat is I cannot use the join for this query.

Any good ideas?

Re: search two indexes specifying two matching columns

bowesmana — Wed, 15 Nov 2023 01:46:59 GMT

Do you mean where Jname and Sname are the same AND the saber_colour + strengths are the same or something else?

This will find you all the cases where the same name has the same combination of saber_color and strengths

index=jedi OR index=sith | eval name=coalesce(Jname, Sname) | stats values(name) as names by saber_color strengths | where mvcount(names)=1

and to find where Jname!=SName, change the mvcount to equal 2.

Good caveat not using join - you should always avoid join and it's almost never the right solution!

Re: search two indexes specifying two matching columns

the_dude — Wed, 15 Nov 2023 03:45:23 GMT

Just the Jname and Sname need to match and all the other columns will coalesce.
Because of the sheer size of the Sname (I set it up as our windows log index)index it worries me. However if I can find a way to drill down and expedite.

index IN (jedi AND sith)
| table saber_color, Jname, Sname, strengths, saber_color
| where Jname=Sname

That is what I am trying. I'll give it a shot with your method. @bowesmana

Re: search two indexes specifying two matching columns

bowesmana — Wed, 15 Nov 2023 03:49:15 GMT

Your example will not work as I understand Jname comes from index=jedi and Sname comes from index=sith, so unless you aggregate the events together, Jname and Sname will never exist in the same event, so try my example.

Re: search two indexes specifying two matching columns

the_dude — Wed, 15 Nov 2023 14:06:12 GMT

@bowesmanawill give it a try. Thank you.

Re: search two indexes specifying two matching columns

the_dude — Sat, 18 Nov 2023 15:45:04 GMT

@bowesmanait worked now I have to figure out how to utilize my drill down to isolate the columns and index heading with my query. Either way thank you.