How long Alert was Active

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