M100779: Remove unwanted computers and network devices from ManageSoft

It is common for a number of unwanted or old computers and network devices to appear in ManageSoft databases over time. In order to ensure accuracy in numbers shown by reports, it is good practice to implement strategies to clean up these records. This article describes a number of approaches which can be followed to remove such records from the database. The attached zip file includes the text of the article and several helpful installables and procedures.

Introduction

You may want to delete information about a computer or network device for a number of reasons. Some examples include:

The computer has been made obsolete or removed from the environment.
Inventory (or other) information has not been received from the computer for a long period of time.
The device was discovered on the network at some point in the past but has not been seen for a long time.

Background

In order to understand how to clean up data about computers and network devices, it is important to understand how ManageSoft stores this information. There are two key types of entity that ManageSoft keeps track of:

A “computer” (recorded in the Computer table in the database) is a piece of hardware such as a desktop, laptop or server. ManageSoft can potentially collect inventory, install packages, collect application usage information and perform other similar operations on computers. Computers are added to the database by a range of operations, including importing an inventory, installation log or other information from a managed device agent, the server side policy merge operation, and when information from a network discovery is imported.
A “network device” (recorded in the NetworkDevice table) is a device that has been found within an environment by ManageSoft discovery processes. Typical discovery processes performed by ManageSoft include IP range scans, enumeration of devices in a WINS browser list (NetBIOS discovery), or discovery based on inventory information that has been received from devices.

A computer may be linked to one or more network device records in the database, and a network device may be linked to a computer. This information is stored in the ComputerID field of the NetworkDevice table.

Data about computers and network devices stored in the database can be cleaned up independently of each other.

Procedure

The following sections describe various ways to remove computer and network device data from the ManageSoft database.

After deleting computer or network device records, be aware of the processes which can cause these records to be recreated in the database. When those processes happen, the records will be created as if the computer had never been known about previously—any previously recorded data for the records will no longer exist.

Your ManageSoft database should already be configured to be backed up regularly with appropriate recovery procedures in place. Before deleting any significant amounts of data, it is wise to ensure that a recent backup has occurred successfully in case there is any need to recover lost data.

Computer deletion from Active Directory

The typical mechanism by which information about a computer is deleted from ManageSoft is through Active Directory: when a computer account is deleted from (or disabled in) Active Directory, the next server side policy merge process performed by ManageSoft will delete all information about that computer from the ManageSoft database.

This is the method of choice by which most cleanup of computer records should be achieved. However it may not be appropriate in all situations. For example:

Not all computers that ManageSoft keeps track of and knows about are associated with computer accounts in an Active Directory domain.
Not all organizations have a tight obsolescence process which ensures computer accounts in Active Directory are deleted or disabled in a timely manner.

In situations such as these, some of the strategies discussed further below can be followed.

Operations Portal module for computer deletion

The attached DeletePortal.zip file contains a module for the ManageSoft Operations Portal which can be used to delete computer records based on name or date inventory information was last received.

The following steps can be used to install and access the module:

Uncompress the contents of DeletePortal.zip in to [INSTALLDIR]\Portal\Web\Modules\ ([INSTALLDIR] is the installation directory of ManageSoft, typically c:\Program Files\ManageSoft) on the reporting server.
Navigate to http: //reporting-server/ManageSoftOP with Internet Explorer.
Select the Data Management > Delete Computers menu option.
Follow the guidance and interface presented to list and delete appropriate computers by name or inventory age.

Computer deletion using direct SQL statements

Information about computers can be deleted directly from the database using appropriate SQL statements. These statements can be constructed to select and delete computers according to logic or heuristics you choose. See the ManageSoft System Reference documentation (Ref-System_Reference.pdf) for further information about the ManageSoft database schema.

The ManageSoft database contains a stored procedure named DeleteComputer which can be used to delete an individual computer record based on its ComputerID. This procedure can be used as follows:

DECLARE @ComputerID INT
SET @ComputerID = ID of the computer to be deleted
EXEC DeleteComputer @ComputerID

ManageSoft 7.8.1 or later databases contain an additional DeleteComputers stored procedure which can be used to delete a set of computers. It is more efficient to delete a set of computers using DeleteComputers than it is to delete them one at a time using DeleteComputer. The DeleteComputers procedure can be used as follows:

-- Create a temporary #Computer table containing the
-- IDs of the computers to be deleted
SELECT ComputerID
INTO #Computer
FROM Computer
WHERE whatever condition you need for deleting computers

-- Delete the computers listed in #Computers
EXEC DeleteComputers

-- Drop the temporary table as we don’t need it any longer
DROP TABLE #Computer

The attached SQL scripts contain a number of examples using these stored procedures to delete computers based on different heuristics:

DeleteComputerByInventoryDate.sql: Deletes computers based on the last date on which hardware inventory was received. Computers whose most recent hardware inventory was received more than 90 days ago are deleted.
DeleteComputerByDomain.sql: Deletes computers recorded in a specified domain.

A couple of approaches to consider when determining how and when to run a SQL script to perform any cleanup are to:

Execute the script manually using an interface such as SQL Query Analyzer.
Execute the script automatically on a scheduled basis using a SQL Server Agent job.

If you are not familiar with how to do these things, contact your database administrator or see SQL Server Books Online for more information.

Network device deletion using direct SQL statements

As with computers, network device records can be deleted directly from the ManageSoft database using appropriate SQL statements. No stored procedures are provided to do this; records should be deleted directly from the NetworkDevice table.

Be aware of the following constraints when deleting records from the NetworkDevice table:

Any records in the ActionApplies table with a DeviceID field value that refers to a record in the NetworkDevice table should be deleted before deleting the NetworkDevice record.
Any records in the NetworkDevice table with a DuplicateID field value that refers to another record in the NetworkDevice table should be deleted before deleting the later NetworkDevice record.

A typical script to clean up records in the NetworkDevice table looks like the following:

USE ManageSoft

-- Select network devices to be deleted
SELECT DeviceID
INTO #NetworkDevice
FROM NetworkDevice
WHERE whatever condition you need for deleting network devices

-- Delete all references and records for the selected devices
DELETE FROM ActionApplies
WHERE DeviceID IN (SELECT DeviceID FROM #NetworkDevice)

DELETE FROM NetworkDevice
WHERE DuplicateID IN (SELECT DeviceID FROM #NetworkDevice)

DELETE FROM NetworkDevice
WHERE DeviceID IN (SELECT DeviceID FROM #NetworkDevice)

-- Drop the temporary table as we don’t need it any longer
DROP TABLE #NetworkDevice

The attached SQL scripts contain a number of examples of scripts to delete network devices based on different heuristics:

DeleteNetworkDeviceByLastUpdate.sql: Deletes network device records based on their LastUpdate field; devices that have not been updated for more than 90 days are deleted. The LastUpdate field records the date and time at which a device was last discovered or had any inventory details relevant to discovery change.
DeleteNetworkDeviceByDomain.sql: Deletes network device records by the NT/NetBIOS domain they are in.

Introduction

Background

Procedure

Related Document

Comments