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.
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
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
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.
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
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
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.
The way I read your output expectation matches the query I gave in my answer. How is the join failing you?
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.
Join is slow. That is one of its downfalls.