What would be the best approach for building a query that for a given time-range gives for each device the amount of time in each state?
I have a system that tosses in logs with "Timestamp, DeviceName, NewState" upon each state change.
For example, from 3/18/2012 12:00:00 - 3/18/2012 15:00:00 could you give me a query that would yield:
ABC;UP; 3 minutes = 00:03:00
ABC;Down;2min (from outage in middle) + 02:16:23 (from 12:00:00 til it went up at 14:16:23) = 02:18:23
ABC;On-Backup;00:38:37 (from 14:21:23 - 15:00:00)