Need assistance creating an alerting modern dashboard

KMSigma.SWI  Do we have any

SELECT aa.AlertActiveID,
CASE 
  WHEN ao.AlertConfigurations.Severity = 2 then '<b><p style="color:red;font-size:16px">Critical</p></b>'
  WHEN ao.AlertConfigurations.Severity = 3 then '<b><p style="color:orangered;font-size:16px">Serious</p></b>'
  WHEN ao.AlertConfigurations.Severity = 1 then '<b><p style="color:Orange;font-size:16px">Warning</p></b>'
  WHEN ao.AlertConfigurations.Severity = 0 then '<p style="font-size:16px">Informational</p>'
  WHEN ao.AlertConfigurations.Severity = 4  then '<p style="font-size:16px">Notice</p>'
  ELSE concat(ao.AlertConfigurations.Severity, '')
  END AS [Severity]
, CONCAT('<p style="font-size:16px"><a href="/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:', a.AlertObjectID, '">' , ao.AlertConfigurations.DisplayName, '</a></p>') AS Alert 
, a.triggertimestamp
, a.TriggerCount AS [Recent Alert Count]
, CASE
    WHEN Minutediff(a.triggertimestamp,GETUTCDATE()) BETWEEN 0 AND 119 THEN concat('<p style="font-size:16px">',Minutediff(a.triggertimestamp,GETUTCDATE()), ' Minutes Ago</p>') 
    WHEN HOURDIFF(a.TriggerTimeStamp,GETUTCDATE()) BETWEEN 2 AND 48 THEN concat('<p style="font-size:16px">',HOURDIFF(a.triggertimestamp,GETUTCDATE()), ' Hours Ago</p>') 
    ELSE concat('<p style="font-size:16px">',DAYDIFF(a.triggertimestamp,GETUTCDATE()), ' Days Ago</p>') 
--  ELSE concat('<p style="font-size:16px">',a.TriggerTimeStamp,'</p>') 
    END AS [Trigger Time] 
, concat('<p style="font-size:16px">',ao.AlertNote,'</p>') as [Ticket]
, concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedBy,'</p>') as [Acknoledged By]
, concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedDateTime,'</p>') as [Acknoledged Time]
, CASE 
  WHEN ao.RelatedNodeCaption IS NULL then  CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') 
  WHEN ao.EntityCaption = ao.RelatedNodeCaption then  CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') 
  ELSE CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl,  '">',ao.EntityCaption, '</a> on ','<a href="', ao.RelatedNodeDetailsUrl, '">', ao.RelatedNodeCaption,'</a></p>') 
  END AS [On]

FROM Orion.AlertActive aa
LEFT JOIN Orion.AlertStatus a on aa.AlertObjectID = a.AlertObjectID
LEFT JOIN Orion.AlertObjects ao on a.AlertObjectID = ao.AlertObjectID

SELECT aa.AlertActiveID,
CASE 
  WHEN ao.AlertConfigurations.Severity = 2 then '<b><p style="color:red;font-size:16px">Critical</p></b>'
  WHEN ao.AlertConfigurations.Severity = 3 then '<b><p style="color:orangered;font-size:16px">Serious</p></b>'
  WHEN ao.AlertConfigurations.Severity = 1 then '<b><p style="color:Orange;font-size:16px">Warning</p></b>'
  WHEN ao.AlertConfigurations.Severity = 0 then '<p style="font-size:16px">Informational</p>'
  WHEN ao.AlertConfigurations.Severity = 4  then '<p style="font-size:16px">Notice</p>'
  ELSE concat(ao.AlertConfigurations.Severity, '')
  END AS [Severity]
, CONCAT('<p style="font-size:16px"><a href="/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:', a.AlertObjectID, '">' , ao.AlertConfigurations.DisplayName, '</a></p>') AS Alert 
, a.triggertimestamp
, a.TriggerCount AS [Recent Alert Count]
, CASE
    WHEN Minutediff(a.triggertimestamp,GETUTCDATE()) BETWEEN 0 AND 119 THEN concat('<p style="font-size:16px">',Minutediff(a.triggertimestamp,GETUTCDATE()), ' Minutes Ago</p>') 
    WHEN HOURDIFF(a.TriggerTimeStamp,GETUTCDATE()) BETWEEN 2 AND 48 THEN concat('<p style="font-size:16px">',HOURDIFF(a.triggertimestamp,GETUTCDATE()), ' Hours Ago</p>') 
    ELSE concat('<p style="font-size:16px">',DAYDIFF(a.triggertimestamp,GETUTCDATE()), ' Days Ago</p>') 
--  ELSE concat('<p style="font-size:16px">',a.TriggerTimeStamp,'</p>') 
    END AS [Trigger Time] 
, concat('<p style="font-size:16px">',ao.AlertNote,'</p>') as [Ticket]
, concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedBy,'</p>') as [Acknoledged By]
, concat('<p style="font-size:16px">',ao.AlertActive.AcknowledgedDateTime,'</p>') as [Acknoledged Time]
, CASE 
  WHEN ao.RelatedNodeCaption IS NULL then  CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') 
  WHEN ao.EntityCaption = ao.RelatedNodeCaption then  CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl, '">',ao.EntityCaption, '</a></p>') 
  ELSE CONCAT('<p style="font-size:16px"><a href="',ao.EntityDetailsUrl,  '">',ao.EntityCaption, '</a> on ','<a href="', ao.RelatedNodeDetailsUrl, '">', ao.RelatedNodeCaption,'</a></p>') 
  END AS [On]

FROM Orion.AlertActive aa
LEFT JOIN Orion.AlertStatus a on aa.AlertObjectID = a.AlertObjectID
LEFT JOIN Orion.AlertObjects ao on a.AlertObjectID = ao.AlertObjectID

Is this tweak on the SolarWinds Active Alert Dashboard , adam.beedell ?

Probably, cant remember where I got it from and revised several bits. There's a couple columns there no-one needs but hey I had it to hand

Everyone else, if 2 people recommend a page, and one of them is KMSigma.SWI  use his one first

I wanted to share what I have on my alerting table.  So one of the issues I ran into, is we use custom properties pretty heavily for our alerts.  Basically all of our emails are tied to a Email custom property for the object type.  When trying to create a dashboard for the teams, this became a bit of a struggle since I didn't really have a good way to filter.  I forget what was original code and what was lifted or modified from other queries and dashboards.  I believe the active time came from one dashboard, and I want to say I used the link KMSigma.SWI referenced for the base of the table layout.

To make something usable for our end, I ended up doing a subquery with various Cases to populate back the custom properties.  This then allowed me to easily filter in my where clause for the teams, and provide a clean-ish select statement at the top.  Anything in the [CP] table is the subquery which gets nasty.  The subquery would need to be edited to line up with your environment.  The main things I pulled over for ours is a Responsible Team property and an Email address field.  Each object (Node, Volume, Datastore, Application (and component), Transaction (and step)) has a corresponding custom property for this.

When I build out the modern dashboard widget, I have:
An Alert name which hyperlinks to the alert
Severity
Object (Node, Volume, Virtual Machine, etc....)
Triggering Entity (Name, Status, Hyperlink)
Related Node (Name, Status, Hyperlink)
Email
Responsible Team
Trigger Time
Time Active
Acknowledge by

SELECT [AA].AlertObjects.AlertConfigurations.Name 
    , '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString([AA].AlertObjectID) As AlertDetailURL --Build Out URL/URI
    , [AA].AlertObjects.AlertConfigurations.Severity
    , [AA].AlertObjects.EntityCaption
    , [AA].AlertObjects.EntityDetailsUrl
    , [CP].EntityStatus
    , Case [AA].AlertObjects.EntityType
        When 'Orion.Nodes' then '[Self]'
        Else [AA].AlertObjects.RelatedNodeCaption
        End  As RelatedNodeCaption
    , [AA].AlertObjects.RelatedNodeDetailsUrl
    , [AA].AlertObjects.Node.Status as RelatedNodeStatus
    , ToLocal([AA].TriggeredDateTime) As TriggerTime
    , CASE
        WHEN MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE()) > 1440
                THEN (TOSTRING(ROUND(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
        WHEN MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE()) > 60
                THEN (TOSTRING(ROUND(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
        ELSE (TOSTRING(MINUTEDIFF([AA].TriggeredDateTime,GETUTCDATE())) + ' Minutes')
        END AS TimeActive
    , [AA].Acknowledged
    , [AA].AcknowledgedBy
    , [CP].EntityObj
    , [CP].Email
    , [CP].ResponsibleTeam
From Orion.AlertActive [AA]
    Left Outer Join (
        SELECT [AO].AlertObjectID
            , Case [AO].EntityType  --Sets a clean value for alert object type
                        When 'Orion.Nodes' then 'Node'
                        When 'Orion.Volumes' then 'Volume'
                        When 'Orion.APM.Component' Then 'Component'
                        When 'Orion.APM.Application' Then 'Application'
                        When 'Orion.VIM.Datastores' then 'Datastore'
                        When 'Orion.VIM.VirtualMachines' then 'Virtual Machine'
                        When 'Orion.SEUM.TransactionSteps' then 'WPM - Step'
                        When 'Orion.SEUM.Transaction' Then 'WPM - Transaction'
                        When 'Orion.Engines' Then 'Orion Server'
                        Else [AO].EntityType --Entity type not accounted for
                    End As EntityObj
            , Case [AO].EntityType --Retrieves status of Object
                        When 'Orion.Nodes' then [AO].Node.Status
                        When 'Orion.Volumes' then [V].Status
                        When 'Orion.APM.Component' Then [C].Status
                        When 'Orion.APM.Application' Then [App].Status
                        When 'Orion.VIM.Datastores' then [DS].Status
                        When 'Orion.VIM.VirtualMachines' then [VM].Status
                        When 'Orion.SEUM.TransactionSteps' then [TS].Status
                        When 'Orion.SEUM.Transaction' Then [T].Status
                        When 'Orion.Engines' Then [SW].Status
                        Else 0  --Entity type not accounted for
                    End As EntityStatus
             , Case [AO].EntityType
                    When 'Orion.Nodes' Then [AO].Node.CustomProperties.Email
                    When 'Orion.Volumes' then [V].CustomProperties.VEmail
                    When 'Orion.APM.Application' then [App].CustomProperties.AEmail
		    When 'Orion.APM.APplication' then [C].Application.CustomProperties.AEmail
                    When 'Orion.VIM.VirtualMachines' then [AO].Node.CustomProperties.Email
                    When 'Orion.VIM.Datastores' then [DS].CustomProperties.DSEmail
                    When 'Orion.SEUM.Transactions' then [T].CustomProperties.TEmail
                    When 'Orion.SEUM.TransactionSteps' then  [TS].Transaction.CustomProperties.TEmail
                    When 'Orion.Engines' then [SW].CustomProperties.Email
                    Else Concat('N/A - ',[AO].EntityType)
                End As Email
            , Case [AO].EntityType
                    When 'Orion.Nodes' Then [AO].Node.CustomProperties.ResponsibleTeam
                    When 'Orion.Volumes' then [V].CustomProperties.VResponsibleTeam
                    When 'Orion.APM.Application' then [App].CustomProperties.AResponsibleTeam
                    When 'Orion.APM.Component' then [C].Application.CustomProperties.AResponsibleTeam
                    When 'Orion.VIM.VirtualMachines' then [AO].Node.CustomProperties.ResponsibleTeam
                    When 'Orion.VIM.Datastores' then [DS].CustomProperties.DSResponsibleTeam
                    When 'Orion.SEUM.Transactions' then [T].CustomProperties.TResponsibleTeam
                    When 'Orion.SEUM.TransactionSteps' then  [TS].Transaction.CustomProperties.TResponsibleTeam
                    When 'Orion.Engines' then [SW].CustomProperties.ResponsibleTeam
                    Else Concat('N/A - ',[AO].EntityType)
                End As ResponsibleTeam
        From Orion.AlertObjects [AO]
        Left Outer Join Orion.Volumes [V] on [AO].EntityDetailsURL = [V].DetailsURL
        Left Outer Join Orion.APM.Application [App] on [AO].EntityDetailsUrl = [App].DetailsUrl
        Left Outer Join Orion.APM.Component [C] on [AO].EntityDetailsUrl = [C].DetailsUrl
        Left Outer Join Orion.VIM.VirtualMachines [VM] on [AO].EntityDetailsUrl = [VM].DetailsUrl
        Left Outer Join Orion.VIM.DataStores [DS] on [AO].EntityDetailsUrl = [DS].DetailsUrl
        Left Outer Join Orion.SEUM.Transactions [T] on [AO].EntityDetailsUrl = [T].DetailsUrl
        Left Outer Join Orion.SEUM.TransactionSteps [TS] on [AO].EntityDetailsUrl = [TS].DetailsUrl
        Left OUter Join Orion.Nodes [SW] on [AO].EntityCaption = [SW].Caption
    ) as [CP] on [AA].AlertObjectID = [CP].AlertObjectID
Where 1=1
--    And [AA].Acknowledged is NULL
--    And [AA].Acknowledged = TRUE
--     And [CP].Email like '%EMAILHERE%'
     And 2=2

This is a thing of beauty.  You should be very proud of yourself SteveK  because I am duly impressed.

Curious about the reason for the WHERE 1=1 and WHERE 2=2 near the end, but otherwise...

Thank you.   KMSigma.SWI feel free to use any of it.  It took a bit to get the tables we used linked up, and I think I drove one of our DBAs a bit crazy trying to get the subquery sorted out.  When I start doing some crazy queries like that, I like to get them involved to make sure I don't cause any issues on the backend they manage.

So on the 1=1, was a trick a previous DBA told me to use.  Essentially as we played around with various where statements, it allowed me to quickly comment out the various conditions without having to move stuff around.  It always seemed like i was commenting out the first thing behind where, so I was cutting/pasting, re-adding "Where" and just got tired of it.  Plus it doesn't really add any time when the query runs.

The 2=2 was due to a bug I stumbled in with the modern dashboard widgets.  I don't know if its still an issue, but the widgtets didn't like the last line being a comment.  So this allowed me to get around that as I would comment out the where pieces for my "template" dashboards.

Yeah - multiple things in the platform don't like comments as the last line.  If I remember right, it's something about preventing possible SQL injections - which I understand, but it seems to be fresh and new every time I encounter it and I'm like...

Glad its just not me on that one.  So now the 2=2 has just become a standard in every SWQL query I write.  I never know where the query may end up (Widget, one off run, report, etc...)

And I would have gone weird with it and used:

WHERE ( 3.14 > 0 ) AND ( 42 > 3.14 )

Because I'm very odd.

For some reason the following did not work:

Where 42 = 'Life, The Universe, and Everything'

I may have to report a bug on that one.  I also tried 'How many roads must a man walk down' but that still came back as wrong.

Fwiw, when you see a WHERE clause containing an evaluation like "1=1" or "2=2", that clause is invisible to the query optimizer because 1=1 or 2=2 always evaluates to TRUE. So, as you say, it's basically a nifty trick to get the query optimizer to say "Move along folks. Nothing to see here. Move along." It also doesn't add any compute time to the SQL, as you've noticed.