The SolarWinds Platform

SQL Advanced Find/Replace within email alert actions

sql-advanced-find-and-replace-email-alert-actions.swql

I had the need to update the email action message (subject) of email actions, and also the email address fields in bulk and this is what I used to do that. Note these are both SQL queries - not SWQL

-- Update the Email Message of Actions
-- with the following query

UPDATE dbo.ActionsProperties
SET dbo.ActionsProperties.PropertyValue = replace (dbo.ActionsProperties.PropertyValue, 'oldval', 'newval')
FROM dbo.ActionsProperties
INNER JOIN dbo.Actions
ON dbo.ActionsProperties.ActionID = dbo.Actions.ActionID
WHERE (
	dbo.Actions.ActionTypeID = 'Email' AND
	dbo.ActionsProperties.PropertyName LIKE 'EmailMessage' AND
	dbo.ActionsProperties.PropertyValue LIKE '%SEARCH-FOR-ME%' AND
	(
		dbo.Actions.Title LIKE '%reset action%' OR
		dbo.Actions.Title LIKE '%trigger action%'
	)
)

-- Update the Email Addresses (To, CC, BCC) of Actions
-- with the following query

UPDATE dbo.ActionsProperties
SET dbo.ActionsProperties.PropertyValue = replace (dbo.ActionsProperties.PropertyValue, 'oldval', 'newval')
FROM dbo.ActionsProperties
INNER JOIN dbo.Actions
ON dbo.ActionsProperties.ActionID = dbo.Actions.ActionID
WHERE (
	dbo.Actions.ActionTypeID = 'Email' AND
	dbo.ActionsProperties.PropertyValue LIKE '%SEARCH-FOR-ME%' AND
	(
		dbo.ActionsProperties.PropertyName LIKE 'EmailTo' OR
		dbo.ActionsProperties.PropertyName LIKE 'EmailCC' OR
		dbo.ActionsProperties.PropertyName LIKE 'EmailBCC'
	)
)