5 Replies Latest reply on Dec 19, 2017 12:55 PM by alphabits

    Help Creating Custom Report

    keenb

      I'm fairly new to SQL and SWQL.

       

      I'm trying to build a custom report that run daily and capture all ACTIVE CRITICAL alerts for our production systems while ignoring certain nodes that contain certain custom properties.

       

      We have what we label "Alert_Groups" in custom properties and for this particular report, I'm trying to ignore a certain alert group.

       

      I found a SWQL query on here that gives me all the the critical alerts which is great but everything I'm trying is failing to ignore those nodes that fall within that certain "Alert_Group".

       

      The SWQUL query I'm using is this:

      SELECT 

        o.AlertConfigurations.Name AS [ALERT NAME] 

        ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME] 

        ,o.EntityCaption AS [ALERT OBJECT] 

        ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT] 

        ,o.RelatedNodeCaption AS [RELATED NODE]

        ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE] 

        ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME] 

        ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE] 

       

        ,CASE 

        WHEN o.AlertConfigurations.Severity = 1 then 'Critical' 

        WHEN o.AlertConfigurations.Severity = 2 then 'Serious' 

        WHEN o.AlertConfigurations.Severity = 3 then 'Warning' 

        WHEN o.AlertConfigurations.Severity = 4 then 'Informational' 

        WHEN o.AlertConfigurations.Severity = 5 then 'Notice' 

        END AS [Severity] 

      FROM Orion.AlertObjects o 

      WHERE o.AlertActive.TriggeredMessage <> '' 

      AND o.AlertConfigurations.Severity = 1 ORDER by o.AlertActive.TriggeredDateTime DESC

       

      I've tried adding "AND" statements under the case but either the results come back blank or I get an error on the query.

       

      Can someone please help me out?

        • Re: Help Creating Custom Report
          alphabits

          Are you familiar with joins? I didn't see the NodesCustomProperties table joined in your statement above.

          Let me know if you're not and I can mock something up using a modified version of your above query.

          • Re: Help Creating Custom Report
            alphabits

            Here is the modified version of your query with the join added, and a delimiter for a node custom property.

            Be sure to put your AND statements above your ORDER BY.

            Hopefully this works out for you.

             

             

            SELECT

             

              o.AlertConfigurations.Name AS [ALERT NAME]

             

              ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]

             

              ,o.EntityCaption AS [ALERT OBJECT]

             

              ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

             

              ,o.RelatedNodeCaption AS [RELATED NODE]

             

              ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

             

              ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]

             

              ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

             

              ,CASE

             

              WHEN o.AlertConfigurations.Severity = 1 then 'Critical'

             

              WHEN o.AlertConfigurations.Severity = 2 then 'Serious'

             

              WHEN o.AlertConfigurations.Severity = 3 then 'Warning'

             

              WHEN o.AlertConfigurations.Severity = 4 then 'Informational'

             

              WHEN o.AlertConfigurations.Severity = 5 then 'Notice'

             

              END AS [Severity]

             

             

            FROM Orion.AlertObjects o

             

            inner join Orion.NodesCustomProperties NCP

            on NCP.NodeID=o.RelatedNodeID

             

            WHERE o.AlertActive.TriggeredMessage <> ''

             

            AND o.AlertConfigurations.Severity = 1

             

            --the below is an example of me limiting the results using my CUSTOMER custom property.

            AND NCP.Customer like 'HAL%'

             

            ORDER by o.AlertActive.TriggeredDateTime DESC

            1 of 1 people found this helpful