Change Alert Email Addresses through SQL

Some of my customers are changing their email addresses. I have over 1000 alerts which need their email addresses changed. Not all alerts list customer's name in the header so I'm not 100% sure who receives each email unless I open a specific alert and look at email addresses.  

Is there a way to change the email addresses for the alerts through the database? Specifically, can I do a job or something like a "Find fred.loucks@yahoo.com and Replace With fred.loucks@gmail.com." I would like to do a bulk change/bulk changes for each entity that is changing their email address. Is that possible? If so, what commands and process would I use to make that happen?

  • Yes, although if you don't know how to do a sql replace yourself I'd be extremely nervous about trying to explain it to you via a forum.   There are lots of tiny syntax mistakes that are silly easy to make when doing a table update. 

    If there is a DBA at your company that can back the db up with you and run through ithe change with you then it's really going to do a lot to keep you from accidentally messing up all of your alert configurations and having to do a panic db restore

  • I'm not aware of a way to do that.  As Mesverrum mentioned, a DBA should be able to do some kind of find/replace but I'm not sure of the specific table that houses the alert actions.

    One recommendation I have is to eventually look at replacing how you do your email alerts.  We ended up setting a custom property on our Nodes, Applications, etc... (basically any object type we send emails on) for email address.  Then in the email alert action you can specify in your To box 

    ${N=SwisEntity;M=CustomProperties.Email}

    Note, you can replace Email with whatever you want to call the property.  This allows our teams to configure email alerts when they setup the object without having to create new or modifying alerts.  It also saves time whenever a team goes through a rebranding and needs the distribution list changed.  We also have a policy in place that we do not add individual emails to the field (except for initial tests) on distribution lists.

    We were able to template out alot of our alerts utilizing this approach and was able to reduce the amount of alerts we have in the environment.

  • You can do it, but there's some gotchas. I dont have a script handy. There's a table that seems to function as a easier-to-display version that really looks like the thing you should update, the thing to actually update is the uglier, harder to find one.

    Otherwise Steve's method is great, and incombination with ${defaultemailto} and friends is a good way to go for most scenarios

  • The following sql query would get you a list of alerts that send emails to emails that match the keyword in the filter at the top of the script:

    DECLARE @emailFilter VARCHAR(255) = 'contoso'
    
    SELECT
         act.Title AS [ActionName]
        ,act.ActionTypeID AS [ActionType]
        ,ap.PropertyName
        ,ap.PropertyValue
    FROM SolarWindsOrion.dbo.ActionProperties ap
    INNER JOIN SolarWindsOrion.dbo.Actions act
        ON act.ActionId = ap.ActionID
    WHERE ap.PropertyName = 'EmailTo'
        AND ap.PropertyValue LIKE '%'+@emailFilter+'%'

    The `ActionProperties` is what looks like it needs to be altered. You could manually go into that table and hand update a value to see if that:

    1. Check the alert settings via whatever Orion web endpoint you use to manage SolarWinds
    2. Run/trigger an alert and see if said email fires off succesfully and is received by recipients (use yourself for testing)

    But remember that this is all on you to validate the scripts, emails, etc and ensure that your SolarWindsOrion databases aren't altered enough to be outside of support from whatever enterprise support contracts you have with SolarWinds. It might be worth testing, validating, proposing to SolarWinds accout/techincal managers for confirmation that scripts will be supported, then passing on to your DBA to fully update the enterprise according to whatever new email mappings your org is moving to.

    I whole heartedly agree that  solution is better long term.