In creating about 100 alerts, I found that I had a slight typo in the condition. I did not want to go back through every single alert to change it, so I did what most of us geeks do - write a code.
This is a very specific alert, in such that one alert was created, then the rest of the 99 alerts were a "duplicate and edit", and just one custom property comparison needed to be changed (along with trigger action). The idea is simple enough, look at the all the alerts, and if any string matches ("XXX - YYY_host"), then replace it with "XXX - YYY"). (I have also submitted this as a feature request https://thwack.solarwinds.com/ideas/7979)
The following script looks at the before and after data fields so I have a good idea if it will work or not:
DECLARE @xx varchar(300)
set @xx=';CustomProperties</a:Value></a:Expr><a:Expr><a:Child i:nil="true"/><a:NodeType>Constant</a:NodeType><a:Value>'
SELECT [Name]
,case when right(substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+1,50)))),4) = 'host'
then 'Replaced'
else 'not' end [Replaced or Not]
, substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+1,50)))) [Orig]
, substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+6,50)))) [Fixed]
, [Trigger] [Orig]
, replace ([Trigger]
,substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+1,50))))
, substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+6,50))))
) [Fixed]
FROM [SolarWindsOrion].[dbo].[AlertConfigurations]
where name like 'XXX - %'
Once validated with just node and validated it, with a deep sigh, executed it across all alerts.
DECLARE @xx varchar(300)
set @xx=';CustomProperties</a:Value></a:Expr><a:Expr><a:Child i:nil="true"/><a:NodeType>Constant</a:NodeType><a:Value>'
update AlertConfigurations
set [Trigger] = case when right(substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+1,50)))),4) = 'host'
then replace ([Trigger]
,substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+1,50))))
, substring([Trigger], charindex(@xx,[Trigger])+len(@xx), charindex( '&', (substring([Trigger], charindex(@xx,[Trigger])+len(@xx)+6,50))))
)
else [Trigger] end
where name like 'XXX - %'
And it worked. 30 mins of writing and debugging, saved me at least half a day of tedious work.
Thanks
Amit
