Okay, so you have 600+ alerts defined over the 10 years you have been using Solarwinds Orion NPM. And, 1500+ alert actions defined. Some of these were alerts you began on a whim, and then a squirrel ran up a tree drawing away your attention and the alert got forgotten about before fully being refined. And, some of your co-workers have moved on to better and brighter occupational horizons. So, you are stuck with hundreds of alert actions that may or may not even be sending alert e-mails to people who even work there.
What do you do? You could browse through 75 pages of alert actions, read each one. Or, you could run this SQL query as a report, export to Excel and then search for the e-mail of you long lost co-worker, identifying the alert and alert action by name so you can go clean up after their mess (or yours). You can also include the WHERE clause below to search for a specific string in the actions.
SELECT "Action ID" = [a].[ActionID] , "Alert Name" = [altc].[Name] , Enabled = CASE [altc].[Enabled] WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END , "Action Type" = [a].[ActionTypeID] -- search for 'Email' in Where clause, optional , "Action Title" = [a].[Title] , "Action Description" = [a].[Description] -- includes To: field -- search for addresses in this fieldFROM Actions [a] JOIN ActionsAssignments [acta] ON [a].[ActionID] = [acta].[ActionID] JOIN AlertConfigurations [altc] ON [acta].[ParentID] = [altc].[AlertID]-- WHERE [a].[Description] LIKE '%5554441212@att.com%' -- Delete to dashes above to search for an e-mail addressGROUP BY [a].[ActionTypeID], [a].[Description], [a].[Title], [a].[ActionID], [altc].[Name], [altc].[Enabled]ORDER BY [a].[ActionTypeID], [a].[Description]
I hope this is helpful to someone. Happy Trails!
