help me with search query for my use case

[sravankaripe]

index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=out | where TRANSACTION_ID=[search index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=in | dedup TRANSACTION_ID| table TRANSACTION_ID]

i have some ids in EVENT_DIRECTION=in
i need to retrieve corresponging events from EVENT_DIRECTION=out

[woodcock]

This is exactly what you asked (ditch the where😞

index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=out [search index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=in | dedup TRANSACTION_ID| table TRANSACTION_ID]

But why not one of these instead:

index="ABC" sourcetype="XYZ" ENV=production someservice stats list(_raw) BY TRANSACTION_ID
index="ABC" sourcetype="XYZ" ENV=production someservice stats valuse(*) AS * BY TRANSACTION_ID

In any case, definitely do NOT use transaction for this use case.

[briancronrath · Accepted solution]

I'd recommend using transaction:

index="ABC" sourcetype="XYZ" ENV=production someservice (EVENT_DIRECTION=out OR EVENT_DIRECTION=in) | transaction TRANSACTION_ID maxspan=100s

However if you want to stick to a subsearch I'd just join the data in:

index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=out | join TRANSACTION_ID [search index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=in | dedup TRANSACTION_ID| table TRANSACTION_ID]

Just remember that with a subsearch your results can only go up to what your configured max subsearch results value is.

[sravankaripe]

this one is working for me Thanks

index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=out | join TRANSACTION_ID [search index="ABC" sourcetype="XYZ" ENV=production someservice EVENT_DIRECTION=in | dedup TRANSACTION_ID| table TRANSACTION_ID]