Mike Rockwell (Progress Software) asked a question.

December 15, 2021 at 4:21 PM
Devices that have had ping monitor down for >4 hours

Originally posted in community.whatsupgold.com by @Jason Alberino (Progress Software Company)​ 

Note: this is for 4 hours in the current 'down' device state. So for example:

Ping is Down 20 minutes --- if it is in this state for 4 hours, it would have actually been doing 4 hours 20 minutes. You can adjust this according to your unique device states. i.e. if you have a device state for 'down 3 hours', adjust the 4 to a 1, etc.

 

  1. select distinct(D.nDeviceID) from Device D
  3.  
  4. join PivotActiveMonitorTypeToDevice PAMTTD on PAMTTD.nDeviceID = D.nDeviceID
  6.  
  7. join ActiveMonitorStateChangeLog AMSCL on AMSCL.nPivotActiveMonitorTypeToDeviceID = PAMTTD.nPivotActiveMonitorTypeToDeviceID
  9.  
  10. where (D.bRemoved = 0 and nActiveMonitorStateChangeLogID in (select distinct(max(nActiveMonitorStateChangeLogID)) as nActiveMonitorStateChangeLogID from Device D
  12.  
  13. join PivotActiveMonitorTypeToDevice PAMTTD on PAMTTD.nDeviceID = D.nDeviceID
  15.  
  16. join ActiveMonitorType AMT on AMT.nActiveMonitorTypeID = PAMTTD.nActiveMonitorTypeID
  18.  
  19. join MonitorState MS on MS.nMonitorStateID = PAMTTD.nMonitorStateID
  21.  
  22. join ActiveMonitorStateChangeLog AMSCL on AMSCL.nPivotActiveMonitorTypeToDeviceID = PAMTTD.nPivotActiveMonitorTypeToDeviceID
  24.  
  25. where (DATEDIFF(hour, AMSCL.dStartTime, getdate()) > 4 and AMT.sMonitorTypeName = 'Ping' and MS.nInternalMonitorState IN (1) and dEndTime IS NULL) group by PAMTTD.nDeviceID))

 

  • 221 views