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
I will give this a try. You have been more helpful than solarwinds support on this!!!
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.
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 <> ''