Running nested queries in druid

I have this use case. I want to find the number of devices which have become faulty for sure in the last x time. This device could be anything with IOT capability. We need to first filter the number of devices in the last x time that have a parameter value “STATUS”: “faulty”. Next, to verify that the devices filtered in previous step have actually gone bad, we need to check for the last y time for the events corresponding to the device list continuously remain in the same state.
This verification step handles those cases where suppose device sensor sends “STATUS”: “faulty” when there is electricity outage and by checking for (y=3 days) we could ensure that if the sensor data is continuously “faulty”, the device has actually gone bad.

How do we go about this query.

How do we write the nested queries.

Thanks

Hi Sanjay,

I don’t have a query off the top of my head,

But the scenario you’re describing sounds similar to funnel analysis (aka path analysis), as you’re looking for status changes per device in a particular order.

I suggest a bit more search for this topic (“funnel analysis” “druid”).

Let us know if you found what you were looking for!

Eyal.