Example how to query the downtime of a device which has an alert configuration on the state of the machine (state 0\/1):<\/p>\n
Example how to query the downtime of a device which has an alert configuration on the state of the machine (state 0\/1): select element_name, sum(downtime) downtime from ( select e.element_name, h.ts, h.VALUE_NUMBER, h.direction, case when h.DIRECTION = 0 then CAST((h.ts … Continue reading select element_name, sum(downtime) downtime from (
\nselect e.element_name,
\n h.ts,
\n h.VALUE_NUMBER,
\n h.direction,
\n case
\n when h.DIRECTION = 0 then
\n CAST((h.ts + 0) - ((lag(h.ts, 1, h.ts) OVER(ORDER BY ts)) + 0) AS FLOAT)*24*60*60
\n else
\n null
\n end as downtime
\n from elements e
\n join alerthistory h
\n on e.element_id = h.ELEMENT_ID
\n where e.element_name like
\n 'Device_%.Status'
\n and ts between to_date('01.07.2014','DD.MM.YYYY') and to_date('31.07.2014','DD.MM.YYYY')
\n) group by element_name<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"