11 Replies Latest reply on Jul 30, 2017 7:30 AM by slomond@gov.nl.ca

    Report to show Active Alerts

    slomond@gov.nl.ca

      I am looking to create a report that shows the active alerts for a responsible team. Basically I am looking to create a report for what is shown under the alerts menu.

       

      The canned report active alerts report is a starting point but it does not show the node the alert is occurring on.

       

      I am looking to have this e-mail to myself each morning.  

        • Re: Report to show Active Alerts
          zackm

          Are you trying to group the results based on the "ResponsibleTeam" custom property that comes builtin with the product?

           

          Try this out:

           

          SELECT
          ISNULL(cp.ResponsibleTeam,'No Team Selected') 'Responsible Team'
          ,ac.Name 'Alert Name'
          ,ao.EntityCaption 'Alert Object'
          ,ac.ObjectType 'Object Type'
          ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'
          FROM AlertConfigurations ac
          FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID
          JOIN AlertObjects ao ON ao.AlertID = ac.AlertID
          JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL
          ORDER BY 'Responsible Team','Alert Name','Alert Trigger Time (Local)'
          

           

           

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services

            • Re: Report to show Active Alerts
              slomond@gov.nl.ca

              Thanks Zackm but I get a error query is not valid. When I try to use this..

               

              I am basically try to get a report that shows what I see in the active alerts for my team. The most important info is the second column that shows the alert and node name.

               

              2017-04-11 12_41_36-All Active Alerts.png

                • Re: Report to show Active Alerts
                  zackm

                  The problem is that you have a differently named custom property...

                   

                  try this, it should limit the results to only your team based on "CP_ResponsibleTeam = OPS-Windows" (line 10)

                   

                   

                  SELECT  
                  ac.Name 'Alert Name'  
                  ,ao.EntityCaption 'Alert Object'  
                  ,ac.ObjectType 'Object Type'  
                  ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'  
                  FROM AlertConfigurations ac  
                  FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID  
                  JOIN AlertObjects ao ON ao.AlertID = ac.AlertID  
                  JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL
                  WHERE cp.CP_ResponsibleTeam = 'OPS-Windows'
                  ORDER BY 'Alert Name','Alert Trigger Time (Local)' 
                  
                    • Re: Report to show Active Alerts
                      slomond@gov.nl.ca

                      Sorry Zackm still getting the error ..  Really appreciate your assistance with this.. 

                       

                      2017-04-11 14_46_44-Edit Report - Microsoft Edge.png

                        • Re: Report to show Active Alerts
                          zackm

                          That's a SQL query, you have the option in that screenshot set to SWQL

                            • Re: Report to show Active Alerts
                              slomond@gov.nl.ca

                              Ok with your assistance I am so close now.. All I need is the Node Name..

                               

                              This is the SQL query I am now using.

                               

                              SELECT

                              ISNULL(cp.ResponsibleTeam,'No Team Selected') 'Responsible Team'

                              ,ac.Name 'Alert Name'

                              ,ao.EntityCaption 'Alert Object'

                              ,ac.ObjectType 'Object Type'

                              ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'

                              FROM AlertConfigurations ac

                              FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID

                              JOIN AlertObjects ao ON ao.AlertID = ac.AlertID

                              JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL

                              WHERE cp.ResponsibleTeam = 'OPS-Windows' 

                              ORDER BY 'Responsible Team','Alert Name','Alert Trigger Time (Local)'

                               

                              This is a sample of the results..

                               

                              Looking to see the node name like in the Alerts

                               

                               

                              Again appreciate all the assistance.

                                • Re: Report to show Active Alerts
                                  pparsaie

                                  Does SWQL Work for you? I think I caught most of what you guys were going for:

                                  SELECT ISNULL(cp.ResponsibleTeam,'No Team Selected') AS [Responsible Team],
                                  o.AlertConfigurations.Name AS [Alert Name],
                                  o.Node.Caption AS [Node Name],
                                  o.EntityCaption AS [Alert Object],
                                  o.AlertConfigurations.ObjectType AS [Object Type],
                                  ToLocal(o.AlertActive.TriggeredDateTime) AS [Alert Trigger Time (Local)]
                                  FROM Orion.AlertObjects o
                                  JOIN Orion.AlertConfigurationsCustomProperties cp ON o.AlertID = cp.AlertID
                                  WHERE cp.ResponsibleTeam = 'OPS-Windows'
                                  ORDER BY 'Responsible Team','Alert Name','Alert Trigger Time (Local)'
                                  

                                   

                                  This would simply add a Node Name column before the Alert Object column. There is much more that can be done I was just curious if this got you to where you wanted to be using SWQL vs SQL.

                                  • Re: Report to show Active Alerts
                                    zackm

                                    try this one out (SQL)

                                     

                                    SELECT    
                                    ac.Name 'Alert Name'    
                                    ,ao.EntityCaption 'Alert Object'    
                                    ,ac.ObjectType 'Object Type'
                                    ,ao.RelatedNodeCaption 'Related Node'    
                                    ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'    
                                    FROM AlertConfigurations ac    
                                    FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID    
                                    JOIN AlertObjects ao ON ao.AlertID = ac.AlertID    
                                    JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL  
                                    WHERE cp.CP_ResponsibleTeam = 'OPS-Windows'  
                                    ORDER BY 'Alert Name','Alert Trigger Time (Local)'
                                    
                                      • Re: Report to show Active Alerts
                                        slomond@gov.nl.ca

                                        Not quite that one gave me errors but you identified what I need to add..

                                         

                                        This is the SQL Query that worked for me.

                                         

                                        SELECT

                                        ISNULL(cp.ResponsibleTeam,'No Team Selected') 'Responsible Team'

                                        ,ac.Name 'Alert Name'

                                        ,ao.EntityCaption 'Alert Object'

                                        ,ac.ObjectType 'Object Type'

                                        ,ao.RelatedNodeCaption 'Related Node'   

                                        ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'

                                        FROM AlertConfigurations ac

                                        FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID

                                        JOIN AlertObjects ao ON ao.AlertID = ac.AlertID

                                        JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL

                                        WHERE cp.ResponsibleTeam = 'OPS-Windows' 

                                        ORDER BY 'Alert Trigger Time (Local)' Desc

                                         

                                         

                                        Thanks everyone for your assistance with this..

                          • Re: Report to show Active Alerts
                            slomond@gov.nl.ca

                            I have been using this report now for a couple of months. It has been working great but now I would like to improve it just a little more. I would like to have the acknowledged alerts filtered out while maintaining all the existing information. Any help would be greatly appreciated.

                             

                            SELECT

                            ISNULL(cp.ResponsibleTeam,'No Team Selected') 'Responsible Team'

                            ,ac.Name 'Alert Name'

                            ,ao.EntityCaption 'Alert Object'

                            ,ac.ObjectType 'Object Type'

                            ,ao.RelatedNodeCaption 'Related Node'   

                            ,DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),aa.TriggeredDateTime) 'Alert Trigger Time (Local)'

                            FROM AlertConfigurations ac

                            FULL OUTER JOIN AlertConfigurationsCustomProperties cp ON ac.AlertID = cp.AlertID

                            JOIN AlertObjects ao ON ao.AlertID = ac.AlertID

                            JOIN AlertActive aa ON aa.AlertObjectID = ao.AlertObjectID AND ao.EntityNetObjectID IS NOT NULL

                            WHERE cp.ResponsibleTeam = 'OPS-Windows' 

                            ORDER BY 'Alert Trigger Time (Local)' Desc