Remove orphaned SCM database objects after uninstalling the evaluation version

6/4/2019 4:19 PM

6/4/2019 4:19 PM

This article provides an explanation of how to clean the Server Configuration Monitor (SCM) database to remove orphaned SCM database objects after uninstalling the evaluation version. Leaving orphaned database objects may result in a compatibility error when upgrading other SolarWinds applications.

Orion Platform

The steps below provide guidance for removing all SCM structures and data from the database. This includes removing tables, views, procedures, and data. However, following these steps does not remove alerts, which must be removed manually. To convert out-of-the-box alerts to user-defined alerts so that you can remove them from the Alert Management page, see Delete Out-of-the-Box Alerts.  

Before making any manual changes to the database, make sure to back up the database.

1. Log into the database server.
2. Open SQL management studio.
3. Select the SolarWinds database.
4. Click New query.
5. Paste the query below into the Query window.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

/* Warning: This script removes ALL SCM structures and data from database !!! */

/* Tables, views, procedures etc. */
DECLARE @table_object_id int
DECLARE @table_name varchar(max)
DECLARE @table_type varchar(max)
DECLARE @stmt nvarchar(max)

DECLARE table_cursor CURSOR FAST_FORWARD FOR
SELECT o.object_id, o.name, o.[type]
FROM sys.objects o
WHERE o.[type] IN ('FN','TF','V','P','U') AND (o.name LIKE 'SCM_%' OR o.name LIKE 'dbm_SCM_%')
ORDER BY CASE o.[type] WHEN 'U' THEN 'z' ELSE o.[type] END

OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
WHILE @@FETCH_STATUS=0
BEGIN

SET @stmt = CASE @table_type
    WHEN 'V' THEN 'VIEW'
    WHEN 'P' THEN 'PROCEDURE'
    WHEN 'FN' THEN 'FUNCTION'
    WHEN 'TF' THEN 'FUNCTION'
    ELSE 'TABLE'
    END

    SET @stmt = 'DROP '+ @stmt +' ['+ @table_name +']'
    PRINT @stmt
    EXEC [dbo].sp_executesql @statement = @stmt

    FETCH NEXT FROM table_cursor INTO @table_object_id, @table_name, @table_type
END

CLOSE table_cursor
DEALLOCATE table_cursor

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[GetScmDbVersion]') AND xtype in (N'FN', N'IF', N'TF'))
BEGIN
    DROP FUNCTION GetScmDbVersion
END


/* Files and filegroup */
DECLARE @filegroupName nvarchar(128) = 'SCM_CONTENTS'
DECLARE @dbName nvarchar(128) = DB_NAME()
DECLARE @fileName nvarchar(512)
DECLARE @sql nvarchar(1000)

SELECT TOP 1 @fileName = df.name FROM sys.filegroups fg
LEFT JOIN sys.database_files df ON fg.data_space_id = df.data_space_id
WHERE fg.name = @filegroupName

WHILE (@fileName IS NOT NULL)
BEGIN
    SET @sql = 'ALTER DATABASE ' + @dbName + ' REMOVE FILE ' + @fileName
    EXEC(@sql)

    SELECT TOP 1 @fileName = df.name FROM sys.filegroups fg
    LEFT JOIN sys.database_files df ON fg.data_space_id = df.data_space_id
    WHERE fg.name = @filegroupName
END

IF EXISTS (SELECT * FROM sys.filegroups fg WHERE fg.name = @filegroupName)
BEGIN
    SET @sql = 'ALTER DATABASE ' + @dbName + ' REMOVE FILEGROUP ' + @filegroupName
    EXEC(@sql)
END

/* Limitations */
DELETE FROM [dbo].[LimitationTableRelation] WHERE Module = 'SCM'

/* Menu */
DECLARE @MenuItemsTable TABLE
(
    MenuItemID int
)

INSERT INTO @MenuItemsTable
SELECT MenuItemID 
FROM [dbo].[MenuItems] WHERE Link IN ('/Orion/SCM/Summary.aspx')

DELETE FROM [dbo].[MenuBars] WHERE MenuItemID IN (SELECT MenuItemID FROM @MenuItemsTable)
DELETE FROM [dbo].[MenuItems] WHERE MenuItemID IN (SELECT MenuItemID FROM @MenuItemsTable)

DELETE FROM [dbo].[UserTabs] WHERE MenuBarID = 'SCM_TabMenu'

/* Modules */
DELETE [dbo].[Modules] WHERE Name = 'SCM'

/* Reports */
DECLARE @ReportsTable TABLE
(
    ReportID int
)

INSERT INTO @ReportsTable
SELECT ReportID FROM [dbo].[ReportDefinitions] WHERE Category = 'Server Configuration'

DELETE FROM [dbo].[ReportDefinitions] WHERE ReportID IN (SELECT ReportID FROM @ReportsTable)
DELETE FROM [dbo].[ReportFavorites] WHERE ReportID IN (SELECT ReportID FROM @ReportsTable)

/* Views and resources */
DECLARE @ViewsTable TABLE
(
    ViewID int
)

DECLARE @ResourceTable TABLE
(
    ResourceID int
)

INSERT INTO @ViewsTable
SELECT ViewID FROM [dbo].[Views] WHERE ViewKey IN
(
    'Server Configuration SubView',
    'SCM Summary'
)

INSERT INTO @ResourceTable
SELECT ResourceID FROM [dbo].[Resources] WHERE ViewID IN (SELECT ViewID FROM @ViewsTable)

DELETE FROM [dbo].[Resources] WHERE ResourceID IN (SELECT ResourceID FROM @ResourceTable)
DELETE FROM [dbo].[ResourceProperties] WHERE ResourceID IN (SELECT ResourceID FROM @ResourceTable)
DELETE FROM [dbo].[Views] WHERE ViewID IN (SELECT ViewID FROM @ViewsTable)
DELETE FROM [dbo].[ViewConditions] WHERE ViewID IN (SELECT ViewID FROM @ViewsTable)

/* Service Directory */
DELETE FROM [dbo].[ServiceDirectoryEntries] WHERE ServiceId LIKE 'SCM.%'

/* Settings */
DELETE FROM [dbo].[Settings] WHERE SettingID IN
(
    'SCM-Retain Detail',
    'SCM-Retain Hourly',
    'SCM-Retain Daily'
)

/* Subscriptions */
DECLARE @SubscriptionsTable TABLE
(
    SubscriptionId UNIQUEIDENTIFIER
)

INSERT INTO @SubscriptionsTable
SELECT Id FROM [dbo].[Subscriptions] WHERE EndpointAddress LIKE '%/scm/%'

DELETE FROM [dbo].[SubscriptionTags] WHERE Subscription_Id IN (SELECT SubscriptionId FROM @SubscriptionsTable)
DELETE FROM [dbo].[PendingNotifications] WHERE Subscription_Id IN (SELECT SubscriptionId FROM @SubscriptionsTable)
DELETE FROM [dbo].[Subscriptions] WHERE Id IN (SELECT SubscriptionId FROM @SubscriptionsTable)

/* Web Settings */
DELETE FROM [dbo].[WebSettings] WHERE SettingName = 'SCM.Website.Version'
DELETE FROM [dbo].[WebUserSettings] WHERE SettingName IN ('SCM.SCMUserRole', 'ScmWizardUserSettings', 'scmNodeListWidgetSettings')

/* Auditing */
DECLARE @ActionTypePattern nvarchar(100) = 'Orion.SCM.%'
DECLARE @AuditingTable TABLE
(
    AuditEventID int
)

INSERT INTO @AuditingTable
SELECT AuditEventID FROM [dbo].[AuditingEvents] WHERE ActionTypeID IN
(
    SELECT ActionTypeID
    FROM [dbo].[AuditingActionTypes] aat
    WHERE aat.ActionType LIKE @ActionTypePattern
)

DELETE FROM [dbo].[AuditingArguments] WHERE AuditEventID IN (SELECT AuditEventID FROM @AuditingTable)
DELETE FROM [dbo].[AuditingEvents] WHERE AuditEventID IN (SELECT AuditEventID FROM @AuditingTable)
DELETE FROM [dbo].[AuditingActionTypes] WHERE [ActionType] LIKE @ActionTypePattern

/* Events */
DELETE FROM [dbo].[Events] WHERE EventType >= 7500 AND EventType <= 7599
DELETE FROM [dbo].[EventTypes] WHERE EventType >= 7500 AND EventType <= 7599

/* Alerts */
/*
    Removing alerts is too complicated using SQL script, remove 'Server Configuration' alerts manually.
    Use following article to convert out-of-the-box alerts to user-defined to be able to remove them from Alert Management page:
    https://support.solarwinds.com/SuccessCenter/s/article/Delete-Out-of-the-Box-Alerts
*/

 

6. Click Execute Query.
7. Close Database Manager.