How to compare 2 table with a same field value?

[Jayee3010]

Hi,
Please help me construct this query.
I have 2 search tables
Table1 from Sourcetype=A
FieldA1 FieldB1

Table2 from Sourcetype=B
FieldA2 FieldB2

Output Table should be:

FieldA1 FieldB1 FieldA2 [where value(FieldB1)=value(FieldB2)]

Thank you.

[dwaddle]

Something that should work without a join ...

sourcetype=A OR sourcetype=B 
| eval common=coalesce(FieldB1,FieldB2) 
| stats first(FieldA1) as FieldA1, first(FieldB1) as FieldB2, first(FieldA2) as FieldA2 by common

[Jayee3010]

Thanks. coalesce() will combine both fields. So the query is giving many false positives. However, I edited the query a little, and getting the targeted output. And this is faster. Thanks so much for the help.

[sundareshr]

Try this

index=myindex sourcetype=A OR sourcetype=B | eval f1=coalesce(FieldB2, FieldB1) | stats values(FieldA1) as FieldA1 values(FieldA2) as FieldA2 by f1 | rename f1 AS FieldB1

[richgalloway · Accepted solution]

Here's one way, although perhaps not the most efficient.

sourcetype=A | join FieldB1 [search sourcetype=B | rename FieldB2 as FieldB1] | table FieldA1 FieldB1 FieldA2

[Jayee3010]

Hi,

Thanks for the query. I tried join command but, it didn't work. My output expectation is below:

sourcetype=A ..... | fields FieldA1, FieldB1 -> search value(FieldB1) in sourcetype=B | fields FieldA2,FieldB2 where FieldB1=FieldB2 | table FieldA1,FieldB1,FieldA2

Does it make sense?

Thank you.

[richgalloway]

The way I read your output expectation matches the query I gave in my answer. How is the join failing you?

[Jayee3010]

Hi,

Sorry for the late response. Yes, its worked finally. I had to alter my logic a bit. Swapped the query and received the output. But, Join command is very slow and taking long time to produce the result.

Thanks so much.

[dwaddle]

Join is slow. That is one of its downfalls.