Jump to content
jbrantley

MySQL Stored Procedure to delete/cleanup former clients from Automate

Recommended Posts

Hey 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.

image.png.cb21042f9832653b51f4404882db9745.png

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

Quote

CALL sp_Custom_DeleteClient(X);

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

jared@mspautomated.com

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

Edited by jbrantley
typo in "CALL sp_Custom_DeleteClient(X);" The closed parenthesis was a 0.
  • Thanks 1

Share this post


Link to post
Share on other sites

i am getting Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Why would that happen 

 

 

Share this post


Link to post
Share on other sites

What version of MySQL are you using?

If you’re copy/pasting then make sure you paste as plain text.

You should also be able to download the SQL file and import it via the control center.

if all else fails, let me know and I’ll be happy to setup a support session and check it out with you.

-Jared 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...