This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Alert inventory report question

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 

  • Thanks for the bump, borgan.

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

  • 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.

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

    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'

  • 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'

  • Orion.ActionProperties doesn't seem to exist is this running for you successfully, I attempted and it errors out on that table name. Looking with SWQL tool I do not see that table listed.

  • Yup it's working for me.

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

    pastedImage_0.png

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

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

  • 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.

  • 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.