13 Replies Latest reply on Apr 28, 2016 10:06 AM by ido.networkstuff

    Alert inventory report question

    st.crispan

      I'm trying to sort my alerts by department, then by node, then by alerts associated to the node...and I don't mean the alerts that are currently active, I mean alerts which are enabled per node.  So far I've got an SQL query which works somewhat, but is incomplete.  I can't figure out why some alerts are reported correctly (per node) but others are simply omitted.

      Here is the SQL code I've got so far:

       

      SELECT 
             AlertConfigurations.Name AS 'ALERT' 
             ,AlertObjects.EntityCaption AS 'ALERT OBJECT' 
             ,AlertObjects.RelatedNodeCaption AS 'RELATED NODE' 
             ,AlertConfigurationsCustomProperties.Alert_Type AS 'ALERT TYPE'
             ,NodesCustomProperties.Department AS 'DEPARTMENT'

      FROM AlertObjects  
             JOIN AlertConfigurations ON AlertConfigurations.AlertID = AlertObjects.AlertID 
             JOIN AlertConfigurationsCustomProperties ON AlertConfigurationsCustomProperties.AlertID = AlertObjects.AlertID
             JOIN NodesData ON NodesData.Caption = AlertObjects.RelatedNodeCaption
             JOIN NodesCustomProperties ON NodesCustomProperties.NodeID = NodesData.NodeID

      WHERE AlertConfigurations.Enabled = 1 

      ORDER BY AlertConfigurations.Name, AlertObjects.EntityCaption 

        • Re: Alert inventory report question
          borgan

          Great question. Anyone?

          • Re: Alert inventory report question
            st.crispan

            Thanks for the bump, borgan.

            This is going to be a serious challenge for those who are SQL masters...

            • Re: Alert inventory report question
              mferrucci

              Hey guys, I'm looking for the same thing. I need to collect the trigger actions for each alert (over 300).  It would be useful to have an alert inventory report which displays all details of every alert configured.

                • Re: Alert inventory report question
                  ido.networkstuff

                  Was surprised to find that this info wasn't already a report we could run, unless I missed it

                  Took me a bit as I only know some basics, but this SWQL query got me what I needed. 

                  I only wanted to see enabled alerts and their e-mail trigger actions so change the WHERE clause as needed.

                   

                  SELECT ac.AlertID, ac.Name, ac.Description, ac.Enabled, ac.Severity, ac.CreatedBy, ap.ActionID, ac.AlertMessage, a.Title, a.Description, ap.PropertyName, ap.PropertyValue

                  FROM Orion.ActionsProperties ap

                  LEFT JOIN Orion.ActionsAssignments aa ON ap.ActionID = aa.ActionID

                  LEFT JOIN Orion.Actions a ON a.ActionID = ap.ActionID

                  LEFT JOIN Orion.AlertConfigurations ac ON aa.ParentID = ac.AlertID

                  WHERE ac.Enabled='True' AND a.ActionTypeID='Email'

                    • Re: Alert inventory report question
                      ido.networkstuff

                      quick update to add trigger condition of the alert

                       

                      SELECT ac.AlertID, ac.Name, ac.Description, ac.Enabled, ac.Severity, ac.CreatedBy, ad.TriggerQuery, ap.ActionID, ac.AlertMessage, a.Title, a.Description, ap.PropertyName, ap.PropertyValue

                      FROM Orion.ActionsProperties ap

                      LEFT JOIN Orion.ActionsAssignments aa ON ap.ActionID = aa.ActionID

                      LEFT JOIN Orion.Actions a ON a.ActionID = ap.ActionID

                      LEFT JOIN Orion.AlertConfigurations ac ON aa.ParentID = ac.AlertID

                      LEFT JOIN Orion.AlertDefinitions ad ON ad.AlertDefID = ac.AlertRefID

                      WHERE ac.Enabled='True' AND a.ActionTypeID='Email'

                  • Re: Alert inventory report question
                    ido.networkstuff

                    Yup it's working for me.

                    I think you need an 's' after 'Action', so should be Orion.ActionsProperties

                    • Re: Alert inventory report question
                      st.crispan

                      Not really where I was going...but I'm having trouble describing it.

                       

                      I don't think that the ActionID or AlertID fields correspond.  

                      • Re: Alert inventory report question
                        st.crispan

                        I'd like a report to look like this:

                         

                         

                        Alerts by Department, by Alert Type, by Node, by Action

                         

                        Department - MAINFRAME

                         

                        Alert Type: AVAILABILITY ALERTS  - NODE DOWN

                                        Node name: AIXBOX1                              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX2                              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX3                              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX4                              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG

                         

                         

                        Alert Type: AVAILABILITY ALERTS  - APPLICATION DOWN

                                        Node name: AIXBOX1               Application name: PRINTER QUEUE          

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX2               Application name: PRINTER QUEUE          

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: EMAIL                           To: Helpdesk

                                                                                                        Action: SYSLOG                        

                         

                         

                        Alert Type: PERFORMANCE ALERTS - Disk less than 5% remaining

                                        Node name: AIXBOX1              

                                                        Volume: /                                 Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: Syslog                          

                                                        Volume: /VAR                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: Syslog

                                                        Volume: /OPT                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                        Volume: /APP                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                        Volume: /STG                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                         

                                        Node name: AIXBOX2              

                                                        Volume: /                                 Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: Syslog                          

                                                        Volume: /VAR                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: Syslog

                                                        Volume: /OPT                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                        Volume: /APP                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                        Volume: /STG                           Action: EMAIL                           To: MAINFRAME DEPARTMENT

                         

                        Alert Type: PERFORMANCE ALERTS - High CPU Utilization

                                        Node name: AIXBOX1              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX2              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX3              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: SYSLOG                        

                                        Node name: AIXBOX4              

                                                                                                        Action: EMAIL                           To: MAINFRAME DEPARTMENT

                                                                                                        Action: SYSLOG

                         

                         

                         

                        The fields which contain the sort are in:

                         

                        Department - NodesCustomProperties.Department     ---- This contains the Department "MAINFRAME", also the other departments I want to sort on.

                        Alert type - AlertConfigurationsCustomProperties.Alert_Type   ---- This contains the field which identifies alerts as "Performance" or "Availability" ...I'd like to create one report per Department, and then sort all the alerts that department can get by "Availability" or by "Performance".   The report above is just an example of how I'd like it to look.

                          • Re: Alert inventory report question
                            njoylif

                            I *BELIEVE* alertObjects only gets populated after an instance of an alert has triggered, thus, if a specific alert has yet to trigger for a node, it will not show up based on your query.

                            I also *Think* the new beta 12.0 has a resource that tells you for a given node what alerts it is eligible to trigger...but verify that one.