Hi,
You might want to try the following SQL script to clean up a host:
--------------------
DECLARE @source NVARCHAR(500);
-- Set the hostname you which to delete
SET @source = '<hostname for deletion>';
DECLARE @table_id VARCHAR(256);
DECLARE @r_table VARCHAR(256);
DECLARE @h_table VARCHAR(256);
DECLARE @v_table VARCHAR(256);
DECLARE @message varchar(80);
DECLARE @RSQLQuery AS NVARCHAR(500);
DECLARE @HSQLQuery AS NVARCHAR(500);
DECLARE @VSQLQuery AS NVARCHAR(500);
DECLARE @ParameterDefinition AS NVARCHAR(100);
DECLARE @cs_id AS NVARCHAR(500);
DECLARE @dev_id AS NVARCHAR(500);
-- selecting the cs_id from the COMPUTER_SYSTEM table
SELECT @cs_id = cs_id from NimsoftSLM.dbo.CM_COMPUTER_SYSTEM where name = @source;
-- Creating a cursor and fill it with the data from CM_DEVICE table
DECLARE device_cursor CURSOR FOR
SELECT dev_id from NimsoftSLM.dbo.CM_DEVICE where dev_name = @source;
-- Creating a cursor and fill it with the data from the S_QOS_DATA table
DECLARE TABLE_CURSOR CURSOR FOR
SELECT table_id, r_table, h_table, v_table FROM NimsoftSLM.dbo.S_QOS_DATA where source = @source;
-- Open the cursor
-- Loop through the data from the S_QOS_TABLE
-- delete all data that was collected for the system
OPEN TABLE_CURSOR
FETCH NEXT FROM TABLE_CURSOR
INTO @table_id, @r_table, @h_table, @v_table
WHILE @@FETCH_STATUS = 0
BEGIN
-- Building the actual deletion queries
SET @RSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' + @r_table + ' where table_id = ' + @table_id;
SET @HSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' + @h_table + ' where table_id = ' + @table_id;
SET @VSQLQuery = 'DELETE FROM NimsoftSLM.dbo.' + @v_table + ' where table_id = ' + @table_id;
SET @ParameterDefinition = '@table_id VARCHAR(256)'
-- Executing the queries
-- Printing a statement of what we are running
PRINT ' '
SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @r_table
PRINT @message
EXECUTE sp_executesql @RSQLQuery, @ParameterDefinition, @table_id
PRINT ' '
SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @h_table
PRINT @message
EXECUTE sp_executesql @HSQLQuery, @ParameterDefinition, @table_id
PRINT ' '
SELECT @message = '--- Deleting collected data for: ' + @table_id + ' from: ' + @v_table
PRINT @message
EXECUTE sp_executesql @VSQLQuery, @ParameterDefinition, @table_id
-- Get the next set of data from the cursor
FETCH NEXT FROM TABLE_CURSOR
INTO @table_id, @r_table, @h_table, @v_table
END
-- Close and remove the cursor for the S_QOS_TABLE data
CLOSE TABLE_CURSOR;
DEALLOCATE TABLE_CURSOR;
-- Delete data from the tables that we do not need anymore
DELETE FROM NimsoftSLM.dbo.S_QOS_DATA where source = @source;
DELETE FROM NimsoftSLM.dbo.CM_COMPUTER_SYSTEM where name = @source;
-- As thers is also remaining data in the CM_DEVICE table clean that using the device_cursor
OPEN device_cursor
FETCH NEXT FROM device_cursor
INTO @dev_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- Printing a status message
PRINT ' '
SELECT @message = '--- Running cleanup for device id: ' + @dev_id
print @message
-- Deleting data from the DEVICE tables
DELETE FROM NimsoftSLM.dbo.CM_DEVICE_ATTRIBUTE where dev_id = @dev_id;
DELETE FROM NimsoftSLM.dbo.CM_DEVICE_RELATIONSHIP where parent_dev_id = @dev_id;
DELETE FROM NimsoftSLM.dbo.CM_DEVICE_RELATIONSHIP where child_dev_id = @dev_id;
DECLARE @ci_id AS NVARCHAR(500);
-- We need an inner cursor to clean up the CONFIGURATION ITEMS (CI)
DECLARE CI_cursor cursor FOR
SELECT ci_id from NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where dev_id = @dev_id
OPEN CI_cursor
FETCH NEXT FROM CI_cursor
INTO @ci_id
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM_METRIC where ci_id = @ci_id;
DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where ci_id = @ci_id
FETCH NEXT FROM CI_cursor
INTO @ci_id
END
-- Close and cleanup the inner cursor so it can be reused in the next outer cursor loop
CLOSE CI_cursor;
DEALLOCATE CI_cursor;
DELETE FROM NimsoftSLM.dbo.CM_CONFIGURATION_ITEM where dev_id = @dev_id;
FETCH NEXT FROM device_cursor
INTO @dev_id
END
-- Close and clean the outer cursor
CLOSE device_cursor;
DEALLOCATE device_cursor;