search two indexes specifying two matching columns

[the_dude]

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?

[bowesmana · Accepted solution]

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! 

[the_dude]

@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.

[the_dude]

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 

[bowesmana]

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.

[the_dude]

@bowesmanawill give it a try. Thank you.