I wanted to provide over a query I recently started to use to determine how long an alert has been active. This was built to be used as a widget for the modern dashboard, but could easily be modified to work as a custom query on the classic dashboard or a report. If anyone sees anything off on the logic please let me know.
I forgot who published the original query, but I ended up taking their logic for the time conversions for Minutes/Days/etc... I also saw some queries where RelatedNodeCaption was '[Self]' if it was a node object, which I liked so I included that as well. Once again, I forget who the original author of that one was.
The query pulls up the list of Reset action events from Alert History, then based on a match of the AlertActiveID, then looks up the time for the Trigger event.
I also included a variant that can be included to add custom properties of the object type. You will just need to update the Values in the subquery.
If anyone sees any holes in my logic, or has thoughts on improvement, please let me know.
Select [AH].AlertObjects.AlertConfigurations.Name
, '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString([AH].AlertObjectID) As AlertDetailURL
, [AH].AlertObjects.EntityCaption
, [AH].AlertObjects.EntityDetailsUrl
, Case [AH].AlertObjects.EntityType
When 'Orion.Nodes' then '[Self]'
Else [AH].AlertObjects.RelatedNodeCaption
End As RelatedNodeCaption
, [AH].AlertObjects.RelatedNodeDetailsUrl
-- , [CP].CUSTOMPROPERTYHERE
, ToLocal([TH].TimeStamp) as TriggerTime
, ToLocal([AH].TimeStamp) as ResetTime
, Case
When ([TH].TimeStamp is Null) then 'N/A'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/10080.0,1)) + ' Weeks')
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/1440.0,1)) + ' Days')
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/60.0,1)) + ' Hours')
Else (ToString(MinuteDiff([TH].TimeStamp,[AH].TimeStamp)) + ' Minutes')
End as TimeActive
, Case
When ([TH].TimeStamp is Null) then '99999999'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/10080.0,1)
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/1440.0,1)
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/60.0,1)
Else MinuteDiff([TH].TimeStamp,[AH].TimeStamp)
End as TimeActiveValue
, Case
When ([TH].TimeStamp is Null) then 'N/A'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then 'Weeks'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then 'Days'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then 'Hours'
Else 'Minutes'
End as TimeActiveUnit
From Orion.AlertHistory [AH]
Left Outer Join (
SELECT [H].AlertHistoryID
, [H].AlertActiveID
, [H].AlertObjectID
, [H].TimeStamp
From Orion.AlertHistory [H]
Where [H].EventType = 0
) as [TH] on [AH].AlertActiveID = [TH].AlertActiveID
Left Outer Join ( --Update results based on custom properties and value
Select [AO].AlertObjectID
, [AO].EntityCaption
, Case [AO].EntityType
When 'Orion.Nodes' Then 'Node'
When 'Orion.Volumes' then 'Volumes'
When 'Orion.APM.Application' then 'SAM - Application'
When 'Orion.APM.Component' then 'SAM - Component'
When 'Orion.Vim.VirtualMachines' then 'Virtualization - Virtual Machine'
When 'Orion.VIM.Datastores' then 'Virtualization - Datastores'
When 'Orion.SEUM.Transactions' then 'WPM - Transaction'
When 'Orion.SEUM.TransactionSteps' then 'WPM - Transaction Step'
When 'Orion.Engines' then 'SolarWinds Polling Engine'
When 'Orion.Groups' then 'SolarWinds Group'
Else Concat('N/A - ',[AO].EntityType)
End As AlertObjectType
-- , Case [AO].EntityType
-- When 'Orion.Nodes' Then [AO].Node.CustomProperties.VALUEHERE
-- When 'Orion.Volumes' then [V].CustomProperties.VALUEHERE
-- When 'Orion.APM.Application' then [App].CustomProperties.VALUEHERE
-- When 'Orion.APM.Component' then [C].Application.CustomProperties.VALUEHERE
-- When 'Orion.Vim.VirtualMachines' then [VM].Node.CustomProperties.VALUEHERE
-- When 'Orion.VIM.Datastores' then [DS].CustomProperties.VALUEHERE
-- When 'Orion.SEUM.Transactions' then [T].CustomProperties.VALUEHERE
-- When 'Orion.SEUM.TransactionSteps' then [TS].Transaction.CustomProperties.ValueHere
-- When 'Orion.Engines' then [SW].CustomProperties.VALUEHERE
-- When 'Orion.Groups' then [G].CustomProperties.VALUEHERE
-- Else Concat('N/A - ',[AO].EntityType)
-- End As VALUENAMEHERE
From Orion.AlertObjects [AO]
--Node can be linked from [AO].Node
Left Outer Join Orion.Volumes [V] on [AO].EntityDetailsUrl = [V].DetailsUrl --Volume
Left Outer Join Orion.APM.Application [App] on [AO].EntityDetailsUrl = [App].DetailsUrl --Application
Left Outer Join Orion.APM.Component [C] on [AO].EntityDetailsUrl = [C].DetailsUrl --Component
Left Outer Join Orion.VIM.VirtualMachines [VM] on [AO].EntityDetailsUrl = [VM].DetailsUrl --Virtual Machines
Left Outer Join Orion.VIM.DataStores [DS] on [AO].EntityDetailsURL = [DS].DetailsUrl --DataStores
Left Outer Join Orion.SEUM.Transactions [T] on [AO].EntityDetailsUrl = [T].DetailsUrl --WPM/Transaction
Left Outer Join Orion.SEUM.TransactionSteps [TS] on [AO].EntityDetailsUrl = [TS].DetailsUrl --WPM Step/Transaction Step
Left Outer Join Orion.SEUM.Agents [Agent] on [AO].EntityDetailsUrl = [Agent].DetailsUrl --WPM Player Servers/Agents
Left Outer Join Orion.Nodes [SW] on [AO].EntityCaption = [SW].Caption --SolarWinds/Orion Engine
Left Outer Join Orion.Groups [G] on [AO].EntityDetailsUrl = [G].DetailsUrl --Orion Groups
) as [CP] on [AH].AlertObjectID = [CP].AlertObjectID
Where [AH].EventType = 1
Select [AH].AlertObjects.AlertConfigurations.Name
, '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString([AH].AlertObjectID) As AlertDetailURL
, [AH].AlertObjects.EntityCaption
, [AH].AlertObjects.EntityDetailsUrl
, Case [AH].AlertObjects.EntityType
When 'Orion.Nodes' then '[Self]'
Else [AH].AlertObjects.RelatedNodeCaption
End As RelatedNodeCaption
, [AH].AlertObjects.RelatedNodeDetailsUrl
, ToLocal([TH].TimeStamp) as TriggerTime
, ToLocal([AH].TimeStamp) as ResetTime
, Case
When ([TH].TimeStamp is Null) then 'N/A'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/10080.0,1)) + ' Weeks')
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/1440.0,1)) + ' Days')
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then (ToSTring(Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/60.0,1)) + ' Hours')
Else (ToString(MinuteDiff([TH].TimeStamp,[AH].TimeStamp)) + ' Minutes')
End as TimeActive
, Case
When ([TH].TimeStamp is Null) then '99999999'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/10080.0,1)
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/1440.0,1)
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then Round(MINUTEDIFF([TH].TimeStamp,[AH].TimeStamp)/60.0,1)
Else MinuteDiff([TH].TimeStamp,[AH].TimeStamp)
End as TimeActiveValue
, Case
When ([TH].TimeStamp is Null) then 'N/A'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 10080
Then 'Weeks'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 1440
Then 'Days'
When MinuteDiff([TH].TimeStamp,[AH].TimeStamp) > 60
Then 'Hours'
Else 'Minutes'
End as TimeActiveUnit
From Orion.AlertHistory [AH]
Left Outer Join (
SELECT [H].AlertHistoryID
, [H].AlertActiveID
, [H].AlertObjectID
, [H].TimeStamp
From Orion.AlertHistory [H]
Where [H].EventType = 0
) as [TH] on [AH].AlertActiveID = [TH].AlertActiveID
Where [AH].EventType = 1
