Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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?

Find more posts tagged with

Accepted answers

hollanjs

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+'%'

<span></span>

</code></div><p><code>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:

Check the alert settings via whatever Orion web endpoint you use to manage SolarWinds
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 @SteveK solution is better long term.

All comments

mesverrum

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

SteveK

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.

adam.beedell

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

hollanjs

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+'%'

<span></span>

</code></div><p><code>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:

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

I whole heartedly agree that @SteveK solution is better long term.

KMSigma.SWI

Reminder: Making direct changes via SQL queries is NOT supported. If you want to play in this arena, you can, but have a database backup handy in case things go sideways.

This is why we normally request that people make changes via the API (when possible). Your best bet is to open a support case and they can provide guidance.

KMSigma.SWI

You could do a "find/replace" with the API.

This is one such example:

$SwisConnection = Connect-Swis -hostname 'yourServerName.or.ip' -Hsername 'UserWithAlertAdminRights' -password 'SaidUsersPassword'

$badEmail = 'fred.loucks@yahoo.com'
$goodEmail = 'fred.loucks@gmail.com'

$BadEmailQuery = @"
SELECT TOP Description, Uri
FROM Orion.Actions
WHERE [Description] LIKE '%$( $badEmail )%'


$BadActions = Get-SwisData -SwisConnection $SwisConnection -Query $BadEmailQuery

ForEach ( $BadAction in $BadActions ) {
    $GoodDescription = $BadDescription.Replace($badEmail, $goodEmail)
    Set-SwisObject -SwisConnection $SwisConnection -Uri $BadAction.Uri -Properties @{ Description = $goodDescription }
}

[embed:6b06e1a7-0ec1-4e43-9421-8caaf95469b6:0fd0d523-30ca-46dd-baa4-fbebf73649d1:message=This%20has%20not%20been%20tested%20and%20it%20absolutely%20SHOULD%20be%20tested%20in%20your%20SolarWinds%20Platform%20Lab%2FDev%20Environment.&type=warning]