Alert Filters (Dashboard or Reports)

SteveK

So in my environment, I use custom properties to tag the ownership group of an object and to send emails in the alerts.  This way I can have a standardized set of alerts and not have to create a duplicate alert changing the distribution lists or flag a specific team.

This worked great until I started to try and filter down alert widgets or reports for alerts to a specific team.  I figured others have probably ran into this before, so I wanted to see what others use to help limit those reports.  I've included something I use within my From clause that allows me to then populate columns within my report/dashboard that I can then filter off of.

I'm more so curious to see what others use or if there are any thoughts on improving what I have.

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  --Used as an Example
            When 'Orion.Nodes' then [AO].Node.Caption --Node, Custom Prop:  [AO].Node.CustomProperties.PROPERTY
            When 'Orion.Volumes' then [V].Caption  --Volume, Custom Prop: [V].CustomProperties.PROPERTY or [VM].Node.CustomProperties.PROPERTY
            When 'Orion.APM.Application' then [App].Name  --Application, Custom Prop: [App].CustomProperties.PROPERTY
            When 'Orion.APM.Component' then [C].Name  --Component, Custom Prop:  [C].Application.CustomProperties.PROPERTY
            When 'Orion.VIM.VirtualMachines' then [VM].Name --Virtual Machine, Custom Prop: [VM].CustomProperties.PROPERTY or [VM].Node.CustomProperties.PROPERTY
            When 'Orion.VIM.Datastores' then [DS].Name --Datastores, Custom Prop:  [DS].CustomProperties.PROPERTY
            When 'Orion.SEUM.Transactions' then [T].Name  --Transactions, Custom Prop:  [T].CustomProperties.PROPERTY
            When 'Orion.SEUM.TransactionSteps' then [TS].DisplayName --Transaction Step, Custom Prop:  [TS].Transaction.CustomProperties.PROPERTY
            When 'Orion.Engines' then [SW].Caption --SW Engine, Custom Prop:  [SW].CustomProperties.PROPERTY
            When 'Orion.Groups' then [G].DisplayName --SW Groups, Custom Prop:  [G].CustomProperties.PROPERTY
            Else Concat('N/A - ',[AO].EntityType)  --Entity type not covered
        End as Name
--    , Case [AO].EntityType
--            When 'Orion.Nodes' Then 
--            When 'Orion.Volumes' then 
--            When 'Orion.APM.Application' then 
--            When 'Orion.APM.Component' then 
--            When 'Orion.VIM.VirtualMachines' then 
--            When 'Orion.VIM.Datastores' then 
--            When 'Orion.SEUM.Transactions' then 
--            When 'Orion.SEUM.TransactionSteps' then  
--            When 'Orion.Engines' then
--            When 'Orion.Groups' then
--            Else Concat('N/A - ',[AO].EntityType)
--        End As
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