Trying to Query the SolarWinds DB for keyword(s) used in an email action in an alert

ZombieEtiquette

I have many alerts that reference knowledge base articles. My organization is moving to a new ticketing system and the KB #s referenced in some of these alerts will no longer be relevant. In the past I have used the SolarWinds DB Manager to find things such as contacts who are no longer needed.

SELECT TOP 1000 * FROM [dbo].[ActionsProperties]
Where PropertyName = 'EmailTo' and PropertyValue LIKE '%email@email.com'

I would use a query such as the one above to get a list of alerts that get sent to a specific email. I'm trying to see what kind of query I could run to find alerts that have an email action which a message body that contains "KB" so I know what alerts I should update. 


I found a table called [dbo].[Alerts], which has a column called TriggerMessage Template, however this table only shows 9 (what look like out-of-the-box-alerts)

Any ideas on what I might be able to try would be much appreciated. 

Thanks!

tony.johnson

Perhaps not exactly what you are looking for but this is a query in my toolbox to search all tables in a given DB for a value and will return the table name and column. I have found it tremendously useful over the years. Use with caution however as it is not cheap to run. 

Declare @SearchStr nvarchar(100)
SET  @SearchStr='YOUR_SEARCH_STRING' BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128),
@SearchStr2 nvarchar(110)  SET  @TableName = ''    SET @SearchStr2 =
 QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL    
BEGIN       
  SET @ColumnName = ''      
  SET @TableName =  (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' +
    QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES 
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
        'IsMSShipped') = 0)

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)      
  BEGIN
    SET @ColumnName = (
      SELECT MIN(QUOTENAME(COLUMN_NAME))
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
        AND TABLE_NAME = PARSENAME(@TableName, 1)
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
      AND QUOTENAME(COLUMN_NAME) > @ColumnName)
      IF @ColumnName IS NOT NULL            
      BEGIN
      INSERT INTO #Results
      EXEC
      (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + 
          ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
      )             
      END       
    END     
  END

  SELECT ColumnName, ColumnValue FROM #Results END