1 Reply Latest reply on Apr 3, 2014 8:49 AM by LadaVarga

    Active Advanced Alerts Report Filtered on Custom Property

    lweniger

      I had a hard time figuring out how to do this so I thought I would share. I work for a systems infrastructure team and we share a single NPM/SAM platform with our network team. We get quite a few alerts throughout the night and it takes time to go through each one to see if it cleared out or not. I wanted a scheduled morning report that would tell my team which alerts were still active, and it had to exclude any of the network team's alerts. We separate all of our nodes with a Custom Property called MainRole (ex. Server, Switch, Storage, etc..).

       

      It's easy enough to write a SQL query that shows you all of the active alerts from the AlertStatus table, but what if you want to relate that Alert to the originating node, and even more... filter on a Custom property within that node? Unfortunately, the ActiveObject field only equals NodeID if the ObjectType (Alert type) = Node. You have to find the NodeID in the table that matches the ObjectType. For instance... if the ObjectType is Volume, you have to find that Volume object in the Volumes table to get the NodeID.


      I hope this helps anyone looking for something similar... I still haven't figured out what to do if the ObjectType is APM: Component, but I only have one Alert based on an application Component and it's not critical. I also don't have any alerts based on groups, so I don't know what that would look like. It works great for me, but it's probably just a starting point for most

       

       

      SELECT Lkp.Node AS NodeID, 
                      AlertStatus.TriggerTimeStamp AS TriggerTime, 
                      Nodes.Caption AS SystemName, 
                      AlertStatus.ObjectName AS AlertName,
                      AlertStatus.ObjectType AS AlertCategory, 
                      'Click to View Node Details' AS NodeDetails
      FROM     (SELECT CASE
                          WHEN AlertStatus.ObjectType ='Node'
                               THEN AlertStatus.ActiveObject
                          WHEN AlertStatus.ObjectType ='APM: Hardware Sensor'
                               THEN APM_HardwareItem.NodeID
                          WHEN AlertStatus.ObjectType ='Volume'
                               THEN Volumes.NodeID
                          WHEN AlertStatus.ObjectType ='APM: Application'
                               THEN APM_Application.NodeID
                          WHEN AlertStatus.ObjectType ='Interface'
                               THEN Interfaces.NodeID
                          ELSE NULL
                     END as Node,
                     ActiveObject
      
                     FROM AlertStatus
                     LEFT OUTER JOIN
                          APM_HardwareItem ON APM_HardwareItem.ID = AlertStatus.ActiveObject
                     LEFT OUTER JOIN
                          Volumes ON Volumes.VolumeID = AlertStatus.ActiveObject
                     LEFT OUTER JOIN
                          APM_Application ON APM_Application.ID = AlertStatus.ActiveObject
                     LEFT OUTER JOIN
                          Interfaces ON Interfaces.InterfaceID = AlertStatus.ActiveObject
                     ) Lkp
      INNER JOIN AlertStatus on AlertStatus.ActiveObject = Lkp.ActiveObject
      INNER JOIN Nodes on Nodes.NodeID = Lkp.Node
      
      WHERE   Nodes.MainRole = 'Server' OR
                     Nodes.MainRole = 'F5' OR
                     Nodes.MainRole = 'Fabric' OR
                     Nodes.MainRole = 'Storage' OR
                     Nodes.MainRole = 'Tape Library' OR
                     Nodes.MainRole = 'VIP'
      
      ORDER BY TriggerTime DESC
      
      
      
        • Re: Active Advanced Alerts Report Filtered on Custom Property
          LadaVarga
          SELECT Lkp.Node AS NodeID,   
                          AlertStatus.TriggerTimeStamp AS TriggerTime,   
                          Nodes.Caption AS SystemName,   
                          AlertStatus.ObjectName AS AlertName,  
                          AlertStatus.ObjectType AS AlertCategory,   
                          'Click to View Node Details' AS NodeDetails  
          FROM     (SELECT CASE  
                              WHEN AlertStatus.ObjectType ='Node'  
                                   THEN AlertStatus.ActiveObject  
                              WHEN AlertStatus.ObjectType ='APM: Hardware Sensor'  
                                   THEN APM_HardwareItem.NodeID  
                              WHEN AlertStatus.ObjectType ='Volume'  
                                   THEN Volumes.NodeID  
                              WHEN AlertStatus.ObjectType ='APM: Application'  
                                   THEN APM_Application.NodeID  
                              WHEN AlertStatus.ObjectType ='Interface'  
                                   THEN Interfaces.NodeID 
            WHEN AlertStatus.ObjectType ='APM: Component'  
                                   THEN (SELECT n.nodeid from nodes as n 
            JOIN APM_Application as APMPP ON APMPP.nodeid=n.nodeid
            JOIN APM_Component as apmc ON apmc.ApplicationID=APMPP.ID
            where apmc.ID=AlertStatus.ActiveObject
            )  
            ELSE NULL  
                         END as Node,  
                         ActiveObject  
            
                         FROM AlertStatus  
                         LEFT OUTER JOIN  
                              APM_HardwareItem ON APM_HardwareItem.ID = AlertStatus.ActiveObject  
                         LEFT OUTER JOIN  
                              Volumes ON Volumes.VolumeID = AlertStatus.ActiveObject  
                         LEFT OUTER JOIN  
                              APM_Application ON APM_Application.ID = AlertStatus.ActiveObject  
                         LEFT OUTER JOIN  
                              Interfaces ON Interfaces.InterfaceID = AlertStatus.ActiveObject  
                         ) Lkp  
          INNER JOIN AlertStatus on AlertStatus.ActiveObject = Lkp.ActiveObject  
          INNER JOIN Nodes on Nodes.NodeID = Lkp.Node  
          
          WHERE   Nodes.Caption='s'
            
          ORDER BY TriggerTime DESC
          

           

          First thanks for your query and I added component. Enjoy