Using IN Operator in Nested Select in NRQL (New Relic)

Question

I have two different custom events.

• prod:order:OrderStatusUpdated
• prod:tenant:StoreStatusChanged

I would like to know the storeNumber that exists in prod:tenant:StoreStatusChanged event, but does not exist in prod:order:OrderStatusUpdated.

The expected result in this example:

• StoreNumber 3

How can I do that?

Please check the printscreens below:

Answer 1

Given NRDB is not a relational DB there's not currently a way to JOIN two events types. Because you're wanting to find the diff between the two counts (i.e., via JOIN), there's not a way to do that at this time.

However, if you were wanting to find the sum of the store numbers between those two event types have the same properties you may be able to do something with NR's nested aggregation which approximates a UNION.

FROM prod:tenant:StoreStatusChanged, prod:order:OrderStatusUpdated
SELECT uniques(storeNumber)
WHERE tenantName = '123' 
SINCE 3 DAYS AGO

You might also checkout this post on NRQL using multiple FROMs. I know this doesn't solve your question, but perhaps it gives you other options if you have the sum. Sorry I couldn't help you more.