Search the Community
Showing results for tags 'client mainteance'.
Found 1 result
jbrantley posted a topic in Cool ToolsHey fellow geeks! Like many of you, I used the check boxes to Hide from All Clients and Disable this Client when offboarding and removing old clients from the navigation tree. Since moving to Automate 12, this functionality no longer works. Having several former clients cluttering up the tree in the CC, I started the process of manually deleting clients. This process was slow and frustrating. To combat this, I created a new stored procedure that can be run from script or via SQLyog to simplify this process. The procedure performs the following steps: Checks to ensure no computers remain in the computers table Identifies all locations assigned to the client Deletes network devices, retired assets, location stats, and passwords assigned at the location level then deletes the location. Scans all tables for columns named clientid then deletes any reference to the clientid in these tables. This includes cleaning up references in plugins that the standard delete command misses. This procedure can be run from script using the SQL Execute function with the below SQL statement Substitute the target clientid for X. The same statement can be executed directly from SQLyog, or your favorite MySQL management tool. I've attached a SQL dump that can be executed to add the procedure to your database. Let me know how this works for you! Thanks, Jared Brantley firstname.lastname@example.org Here's the code: DELIMITER $$ USE labtech$$ DROP PROCEDURE IF EXISTS sp_Custom_DeleteClient$$ # Procedure is launched by running CALL sp_Custom_DeleteClient(%clientid%); CREATE PROCEDURE sp_Custom_DeleteClient(IN CLID INT) # Start of procedure. sp_Custom_DeleteClient: BEGIN # NOTE: If this procedure is performed on versions of MySQL earlier than 5.6.3, you may # encounter a 1329 warning for No data - zero rows fetched, selected, or processed. This # is a bug that is resolved in MySQL 5.6.3. The procedure still completes successfully. # Subroutine to verify no agents exist on computers table. SafetyCheck_SUB: BEGIN # Check to ensure that no agents exist in the computers table for ClientID. IF (SELECT COUNT(computerID) FROM computers WHERE clientid = CLID) > 0 THEN # If agents exist, end the procedure now. LEAVE sp_Custom_DeleteClient; # Carry on if no agents exist in the computers table. END IF; # End SafetyCheck subroutine. END SafetyCheck_SUB; /*==============================================================*/ # Subroutine to delete locations assigned to ClientID. DeleteLoc_SUB: BEGIN # Variable representing LocationID. DECLARE LOCID INT(10); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to grab all LocationsIDs for the ClientID. DECLARE LOCID_CUR CURSOR FOR SELECT locationid FROM locations WHERE clientid = CLID; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH=1; # Here we open the cursor to begin looping through the results. OPEN LOCID_CUR; # Begin looping through results. Del_Location: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH LOCID_CUR INTO LOCID; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Location; END IF; # Delete network devices assigned to LocationID. DELETE FROM networkdevices WHERE locationid = LOCID; # Delete retired assets assigned to LocationID. DELETE FROM retiredassets WHERE locationid = LOCID; # Delete location stats assigned to LocationID. DELETE FROM h_locationstats WHERE locationid = LOCID; # Delete passwords assigned at location level. DELETE FROM passwords WHERE locationid = LOCID; # Delete the location. DELETE FROM locations WHERE locationid = LOCID; # End of Del_Location loop. END LOOP; # Close the open CURSOR. CLOSE LOCID_CUR; # End of location deletion subroutine. END DeleteLoc_SUB; /*==============================================================*/ # Subroutine to delete the client. DeleteClient_SUB: BEGIN # Variable to store table names containing a clientid column. DECLARE TBLNAME VARCHAR (50); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to loop through tables containing a clientid column. # This excludes views and the plugin_webroot_3api table uses the clientid # column for an entirely different purpose. DECLARE TBLNAME_CUR CURSOR FOR SELECT table_name FROM information_schema.columns WHERE column_name='clientid' AND table_name NOT LIKE 'v_%' AND table_name != 'plugin_webroot3_api'; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH = 1; # Here we open the cursor to begin looping through the results. OPEN TBLNAME_CUR; # Loop creation to delete client records. Del_Client: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH TBLNAME_CUR INTO TBLNAME; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Client; END IF; # Create a new variable with the populated TBLNAME and CLID variables. SET @s = (SELECT CONCAT('DELETE FROM ',TBLNAME,' WHERE clientid IN (',CLID,');')); # Preapare a delete statement from the above variable. PREPARE stmt FROM @s; # Excute the delete statement EXECUTE stmt; # Dump the statement from memory DEALLOCATE PREPARE stmt; # End of Del_Client loop. END LOOP; # Close the open CURSOR. CLOSE TBLNAME_CUR; # End client deletion subroutine. END DeleteClient_SUB; /*==============================================================*/ # End of procedure. END sp_Custom_DeleteClient $$ DELIMITER; sp_Custom_DeleteClient.sql