NetXMS Support Forum

Hi

I am trying to write SQL that will collect how much time each alarm (that got triggered) took to get resolved ( time difference between time of when it got triggered and time of when it got resolved)

Basically I am trying to get table like this

Alarm_id |   Alarm_message | Time it got Triggered |Time it got Resolved   Age ( difference between two times) |
   
Can you please help me with SQL ?

I did spent time looking at Alarm and Alarm_events tables, but I could not frame correct query

I think this is what you are looking for. If not, it should get you started. I did not find a " time resolved" field, only the last time the alarm was changed. Which I am assuming, if it was resolved or terminated, that would be the last time it was changed. I added in the source object name, as I thought that would be useful.

select object_properties.name as 'Node Name', alarms.alarm_id as 'Alarm ID', alarms.message as 'Message', DATEADD(S,alarms.creation_time, '1970-01-01') as 'Date Created',DATEADD(S,alarms.last_change_time, '1970-01-01') as 'Date Last Changed',
CONVERT(varchar, (alarms.last_change_time-alarms.creation_time) / 86400) + ':' + CONVERT(varchar, DATEADD(s, (alarms.last_change_time-alarms.creation_time), 0), 108) as 'Age DD:HH:MM:SS'
from alarms
inner join object_properties on object_properties.object_id=alarms.source_object_id
where alarms.alarm_state > 1
order by alarms.last_change_time desc