Help with a query strategy around tracking device online/offline aggregation

[miked187 (Mike D)]

We have a series of devices that we track by device name, storing the message that they emit, and the datetime of the message. The devices are come online as system load increases and go offline as load decreases. A device maybe online for as little as a minute or 2, or as much as 8-10 hours or more.

A device is considered "online" via its first message that we receive from it, and is "offline" if it has not emitted messages for 20 minutes. A device may come online/offline multiple times during the requested time window

I need to query for all devices that were online/emitting messages during a time range, returning only 3 fields the device name, the time the device came online, and the time the device went offline

Eventually I'll need to grab all the messages emitted for each device found emitting within the time window, but baby steps to get started.

I have the data in ES7.6, but I don't have an approach on how to solve this which is the actual question. I've looked through all the aggregation docs but nothing is jumping out at me, so reaching out to see about suggestions on an approach. TIA

[miked187 (Mike D)]

since I received no suggestions on this I went with the brute force method, and since I always like an answer to be posted when someone solves a problem I wanted to document this here.

I'm building an aggregation table in MySQL based on bringing query data out of ES, the MySQL schema is the simple name, start time, stop time.

for a given time frame (1hr windows), aggregate the device names that were in use within that range of time

then for each device grab all the messages in date ascending order from that same time range

for each message check to see if it fits within an existing start/stop time frame for that device

if yes then we've already got the data, move to the next record

if no then see if the timestamp on the message is within 15 minutes of the devices latest start/stop time.
if so then extend the stop time based on the time stamp of the message
if not and the time is > 15 minutes then create a new time window record for this device, setting the start and end times to be the message time

this works, but is really slow to backfill data initially. The process has been grinding through lots of data for about 5 days now and still a few more months of ES data to go through to build these device online windows of start/stop time

Hope it helps someone