Search and Replace Across all Alerts

Version 2

    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

     

    https://thwack.solarwinds.com/ideas/7979