6 Replies Latest reply on Dec 31, 2017 12:16 AM by stevengj1

    Active Alerts - Creating a duplicated view

    timt

      Hi,

       

      I am in need of creating an exact duplicate "Active Alerts View", but also need to filter the alerts to specific type of alerts.  This is because the View Limitation can not filter by Specific Alerts, and I can't use the Accounts alerts limitation category because everyone logs into the same group.

       

      Short story, I found a SWQL query here for it, but I can't seem to find the "Active Time".. any ideas where this is located, or how I can get Active Time to display as in Active Alerts View?

       

      SELECT 

        o.AlertConfigurations.Name AS [ALERT NAME] 

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

        ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE] 

        ,o.EntityCaption AS [ALERT OBJECT] 

        ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

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

      ,o.RelatedNodeCaption AS [RELATED NODE] 

      ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE] 

      FROM Orion.AlertObjects o 

      WHERE o.AlertActive.TriggeredMessage <> '' and o.AlertConfigurations.Name = 'Critical'

       

      A bonus, anyone knows how to add the icons or color as well?

        • Re: Active Alerts - Creating a duplicated view
          tdanner

          "Active Time" is not an explicit property on the alert - it's just the time that has passed since it was triggered. In the real Active Alerts View, this is computed and formatted in code. Since you are working in the Custom Query Resource, you don't have that option and will have to do the best you can in SWQL. Here's one way to do that:

           

          CASE WHEN o.AlertActive.TriggeredDateTime IS NULL THEN NULL ELSE (

              TOSTRING(FLOOR(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0)) + 'h ' +

              TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm'

          ) END AS [ACTIVE TIME]

           

          This is not as fancy as the real one - it will show "0h 5m" for an alert that triggered 5 minutes ago instead of just "5m" like the real one. Likewise it will show "51h 5m" instead of "2d 3h 5m" for an alert that triggered a little over 2 days ago. But hopefully it is good enough for your purposes.

           

          Custom Query Resource doesn't really give you enough control to do the conditional icons and colors.

            • Re: Active Alerts - Creating a duplicated view
              timt

              Getting an error mismatch input form case...  is this correct?  Thanks in advance!

               

              SELECT 

                o.AlertConfigurations.Name AS [ALERT NAME] 

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

                ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE] 

                ,o.EntityCaption AS [ALERT OBJECT] 

                ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

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

              CASE WHEN o.AlertActive.TriggeredDateTime IS NULL THEN NULL ELSE (

                  TOSTRING(FLOOR(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0)) + 'h ' +

                  TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm'

              ) END AS [ACTIVE TIME]

                  ,o.RelatedNodeCaption AS [RELATED NODE] 

              ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE] 

              FROM Orion.AlertObjects o 

              WHERE o.AlertActive.TriggeredMessage <> '' and o.AlertConfigurations.Name = 'Critical'

                • Re: Active Alerts - Creating a duplicated view
                  timt

                  I got it, thanks!

                   

                  SELECT 

                    o.AlertConfigurations.Name AS [ALERT NAME] 

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

                    ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE] 

                    ,o.EntityCaption AS [ALERT OBJECT] 

                    ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

                   

                  ,CASE WHEN o.AlertActive.TriggeredDateTime IS NULL THEN NULL ELSE (

                      TOSTRING(FLOOR(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0)) + 'h ' +

                      TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm'

                  ) END AS [ACTIVE TIME]

                      ,o.RelatedNodeCaption AS [RELATED NODE] 

                  ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE] 

                  FROM Orion.AlertObjects o 

                  WHERE o.AlertActive.TriggeredMessage <> '' and o.AlertConfigurations.Name = 'Critical'

                    • Re: Active Alerts - Creating a duplicated view
                      stevengj1

                      Hi,  I like this for filtering on alerts.  It worked well for me.  But I want to filter and include some of fields on something else and I am getting errors I never try a SWQL before.  We have our NPM integrated with Service Now and want to include the incident number and assignment group and receiving an this error "There was an error processing the request."   I understand that o. links to the table Orion.AlertObjects that part I am confuse is how to interpret o.AlertConfigurations.Name where Name is not in AlertObjects table but is in  AlertConfiguration table.  So is that the proper format to get fields from another table o.<tablename>.<fieldname>.  What am I missing do I need to add something like this   ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_Incident Number]

                       

                      Thanks

                       

                      SELECT

                        o.AlertConfigurations.Name AS [ALERT NAME]

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

                        ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

                        ,o.EntityCaption AS [ALERT OBJECT]

                        ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

                        ,o.SNI_AlertIncidents.IncidentNumber AS [Incident Number]

                        ,o.SNI_AlertIncidents.AssignmentGroup AS [Assigned To]

                       

                      ,CASE WHEN o.AlertActive.TriggeredDateTime IS NULL THEN NULL ELSE (

                          TOSTRING(FLOOR(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0)) + 'h ' +

                          TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm'

                      ) END AS [ACTIVE TIME]

                          ,o.RelatedNodeCaption AS [RELATED NODE]

                      ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

                      FROM Orion.AlertObjects o

                      Where o.SNI_AlertIncidents.IncidentNumber  <> '' and  o.SNI_AlertIncidents.IncidentNumber ='Network Services'

                        • Re: Active Alerts - Creating a duplicated view
                          stevengj1

                          I was able to figure it out,  this work for me to include the other fields I want for the Service Now incident

                          SELECT

                            o.AlertConfigurations.Name AS [ALERT NAME]

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

                            ,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

                            ,o.EntityCaption AS [ALERT OBJECT]

                            ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

                            ,o.AlertIncident.IncidentNumber as [INCIDENT NUMBER]

                            ,o.AlertObjectID AS [_LinkFor_INCIDENT NUMBER]

                            ,o.AlertIncident.AssignedTo AS [ASSIGNMENT GROUP]

                            ,o.AlertObjectID AS [_LinkFor_ASSIGNMENT GROUP]

                            

                          ,CASE WHEN o.AlertActive.TriggeredDateTime IS NULL THEN NULL ELSE (

                              TOSTRING(FLOOR(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0)) + 'h ' +

                              TOSTRING(MINUTEDIFF(o.AlertActive.TriggeredDateTime,GETUTCDATE())%60) + 'm'

                          ) END AS [ACTIVE TIME]

                              ,o.RelatedNodeCaption AS [RELATED NODE]

                          ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

                          FROM Orion.AlertObjects o

                          WHERE o.AlertIncident.IncidentNumber <> '' AND (o.AlertIncident.AssignedTo = 'Global Network Services'

                          OR o.AlertIncident.AssignedTo = 'Network Services')

                  • Re: Active Alerts - Creating a duplicated view
                    cgregors

                    Check my post on this thread.

                     

                    Filtering out events from "Last XX Events" and "Last XX Audit Events" reports

                     

                    I managed to recreate the Event block including icons and colors.