Need assistance creating an alerting modern dashboard

Similar to the below, but for the alerting section, like active alert, which application, server, node triggering more alert like that. 

Do we have any swql query for that?

Parents
  • 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

  • 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   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  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

Reply
  • 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  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

Children
  • This is a thing of beauty.  You should be very proud of yourself   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.    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.