Wanted to build a new modern dashboard widget that took the average time to acknowledge an alert and how much time that the alert took to close.
One item to note is that i excluded alerts that were not acknowledged. Knowing that some alerts may appear and clear faster than anyone in the NOC might be able to handle them. This only focuses on alarms that have been acknowledged and subsequently cleared.
After dragging a new widget, i selected 'KPI Widget' from the list.
I added 2 values, one call 'Time to Acknowledge' and the other was 'Time to Close'
The SWQL query for each is the same except commenting out 1 line.
The first value uses this query. The second value comments out the first ROUND line and uncomments the 2nd ROUND Line.
If you want time longer or shorter than a week you can update the DAYDIFF line and change the amount of days to show, currently set at 7.
-- Scripts are not supported under any SolarWinds support program or service. -- Scripts are provided AS IS without warranty of any kind. SolarWinds further -- disclaims all warranties including, without limitation, any implied warranties -- of merchantability or of fitness for a particular purpose. The risk arising -- out of the use or performance of the scripts and documentation stays with you. -- In no event shall SolarWinds or anyone else involved in the creation, -- production, or delivery of the scripts be liable for any damages whatsoever -- (including, without limitation, damages for loss of business profits, business -- interruption, loss of business information, or other pecuniary loss) arising -- out of the use of or inability to use the scripts or documentation. SELECT ROUND( AVG( TOSTRING(MINUTEDIFF(ah.TimeStamp,ack.timestamp))),2) AS [Minutes Until Acknowledged] --ROUND( AVG( TOSTRING(MINUTEDIFF(ah.TimeStamp,reset.timestamp))),2) AS [Minutes Until Reset] FROM Orion.AlertHistory ah LEFT JOIN Orion.AlertObjects ao ON ao.alertobjectid=ah.alertobjectid LEFT JOIN Orion.AlertConfigurations ac ON ac.alertid=ao.alertid LEFT JOIN (SELECT timestamp, AlertActiveID, AlertObjectID,message FROM orion.alerthistory ah WHERE eventtype=2) ack ON ack.alertactiveid=ah.AlertActiveID AND ack.alertobjectid=ah.AlertObjectID LEFT JOIN (SELECT timestamp, AlertActiveID, AlertObjectID FROM orion.alerthistory ah WHERE eventtype=1) reset ON reset.alertactiveid=ah.AlertActiveID AND reset.alertobjectid=ah.AlertObjectID WHERE DAYDIFF(ah.timestamp,GETUTCDATE())<7 AND ah.eventtype=0 AND ac.Severity IN (1,2,3) AND ack.timestamp IS NOT NULL AND reset.timestamp IS NOT NULL
Kudos to mesverrum for the original post here for the SWQL base i used.