4 Replies Latest reply on Aug 24, 2017 2:47 PM by dan jagnow

    Solarwinds Database Manager - Trying to find email addresses alerts are sent to

    martian monster

      We are moving our Solarwinds Orion and SQL servers to more robust servers to handle them and need to figure out who is receiving email alerts out of Solarwinds.  If I run the query for Alert Configurations it will list all of the alerts enabled or not - is there someway to only have this report the enabled alerts so I can let end users know that Solarwinds will be down?  Thanks!

        • Re: Solarwinds Database Manager - Trying to find email addresses alerts are sent to
          dan jagnow

          You can add a WHERE clause to your SWQL statement that filters the alert configurations to show only those that are enabled.  An example follows:

           

          SELECT
             AlertID,
             AlertMessage,
             AlertRefID,
             Name,
             Description,
             ObjectType,
             Enabled,
             Frequency,
             Trigger,
             Reset,
             Severity,        
             NotifyEnabled,
             NotificationSettings,
             LastEdit,
             CreatedBy,
             Category,
             Canned
          FROM Orion.AlertConfigurations
          WHERE Enabled = TRUE
            • Re: Solarwinds Database Manager - Trying to find email addresses alerts are sent to
              martian monster

              I will give this a try.  You have been more helpful than solarwinds support on this!!!

              • Re: Solarwinds Database Manager - Trying to find email addresses alerts are sent to
                martian monster

                To build upon this - I am running the query ActionDefinitions to try to find out what email address each alert goes to.  We are going to be moving our current SolarWinds install so I need to let users that would receive alerts when the system is down for the move. I ran that query and it listed most of the configured alerts and associated smtp server but it is missing a few things.

                 

                1. I recently created some alerts and configured additional emails addresses to go to and these do not show up here.

                2. We had 6 different SMTP servers listed under SMTP servers and consolidated it down to one.  I think everything has been changed over but when I run the above query via database manager nothing changes at all.

                 

                Not sure if this query can do what I would like above or not,  or if there is a different query or sets of queries I ran run to get this information. Ideally if I can copy it all to an excel spread sheet I can manipulate it so I can see the data I want to see.

                 

                Thanks!

                  • Re: Solarwinds Database Manager - Trying to find email addresses alerts are sent to
                    dan jagnow

                    To get some details from the SolarWinds Database Manager on the enabled alerts with associated actions that send emails, including what SMTP server they are configured to use, you could do something like this:

                     

                    SELECT
                         ac.AlertID,
                         ac.AlertMessage,
                         ac.Name,
                         ac.Description,
                         ac.Enabled,
                         ac.ObjectType,
                         ac.NotifyEnabled,
                         a.ActionID,
                         a.ActionTypeID,
                         a.Title,
                         ap.PropertyName,
                         ap.PropertyValue,
                         s.Address AS SMTPAddress
                    FROM [dbo].[AlertConfigurations] ac
                    INNER JOIN [dbo].[ActionsAssignments] aa ON aa.ParentID = ac.AlertID
                    INNER JOIN [dbo].[Actions] a ON a.ActionID = aa.ActionID
                    INNER JOIN [dbo].[ActionsProperties] ap ON ap.ActionID = a.ActionID
                    LEFT JOIN [dbo].[SMTPServers] s ON ap.PropertyName = 'SMTPServerID' AND CAST(s.SMTPServerID AS varchar(5)) = ap.PropertyValue
                    WHERE ac.Enabled = 1
                         AND ac.NotifyEnabled = 1
                         AND a.ActionTypeID = 'Email'
                         AND a.Enabled = 1
                         AND (ap.PropertyName IN ('EmailTo', 'EmailCC', 'EmailBCC') OR ap.PropertyName = 'SMTPServerID')

                     

                    If you are only interested in the email addresses, you could simplify it to something like this:

                     

                    SELECT DISTINCT (ap.PropertyValue) AS Email
                    FROM [dbo].[AlertConfigurations] ac
                    INNER JOIN [dbo].[ActionsAssignments] aa ON aa.ParentID = ac.AlertID
                    INNER JOIN [dbo].[Actions] a ON a.ActionID = aa.ActionID
                    INNER JOIN [dbo].[ActionsProperties] ap ON ap.ActionID = a.ActionID
                    WHERE ac.Enabled = 1
                         AND ac.NotifyEnabled = 1
                         AND a.ActionTypeID = 'Email'
                         AND a.Enabled = 1
                         AND ap.PropertyName IN ('EmailTo', 'EmailCC', 'EmailBCC')
                         AND ap.PropertyValue <> ''