dcsimg

How do I remove extra host IDs from a MySQL database?

Products

Webtrends Analytics 8.1
Webtrends Analytics 8.0

Cause

Extra HostIDs exist in the Webtrends system database for hosts no longer in use or no longer exist.

Resolution

Remove references to the old hosts by running queries against the database to delete rows that pertain to the offending HostIDs.

1. Stop the Webtrends services. For more information, refer to KB 113946 – Order to stop and start the Webtrends services by version.

2. After confirming the “Webtrends – MySQL” service has stopped. Back up the installation using the first two steps detailed in KB 062367 – How to manually back up and restore versions of Webtrends Analytics which use MySQL for the system database
Note: The \storage folder may be omitted to save time, but it is recommended all three folders are backed up at the same time to ensure data is synced between the system and configuration information.

3. Start the “Webtrends – MySQL” service.

4. Execute the queries in step 9 using a MySQL editor. To execute them using the MySQL command shell, proceed to step 5.

5. Open a command line window on the Webtrends server and navigate to the following folder:

\Webtrends\common\database\mysql\bin\

6. Type the following command to log into the MySQL database, replacing with the name of the user account used by Webtrends to access the database. Provide the password when prompted:

mysql -u ?p

7. Type the following and hit Enter:

use wt_sched

8. To determine the proper HostID entry to be removed, type the following and hit Enter:

SELECT * FROM wt_host;

The wt_host table information displays. The HostID column is in the first position and past and current HostID values display below the column header.

9. Execute the following queries, replacing in each line with the value for the host to be removed:
DELETE FROM wt_capability WHERE HostID = ”;
DELETE FROM wt_pollrate where ComponentInstallationID in (select ComponentInstallationID from wt_componentinstallation WHERE HostID = ”);
DELETE FROM wt_componentinstallation WHERE HostID = ”;
DELETE FROM wt_hostmetricconfiguration WHERE HostID = ”;
DELETE FROM wt_hostgroupmember WHERE HostID = ”;
DELETE FROM wt_hostmetrichistory WHERE HostID = ”;
DELETE FROM wt_hostservicerole WHERE HostID = ”;
DELETE FROM wt_lock WHERE HostID = ”;
DELETE FROM wt_TaskStatus;
DELETE FROM wt_TaskChronicle;
DELETE FROM wt_HostStatus;
DELETE FROM wt_Monitoralertresolvehistory;
DELETE FROM wt_MonitorResultAction;
DELETE FROM wt_MonitorResult;
DELETE FROM wt_ServiceStatus;
DELETE FROM wt_ComponentStatus WHERE HostID = ”;
DELETE FROM wt_eventstatus;
DELETE FROM wt_processrecord WHERE hostid = ”;
DELETE FROM wt_host WHERE hostid = ”;

10. Exit the MySQL command shell and start the Webtrends services.

More Information

For installations which use MS SQL for the system database, refer to KB 114014 – How do I remove extra host IDs from an MS SQL database?