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

just wondering if anyone knows if this works for the hosted connectwise automate platform?

Share this post


Link to post
Share on other sites

It does, you'll first need to write a SQL Execute script function to add the stored procedure to the database. Other than that, you should be good.

Share this post


Link to post
Share on other sites

Can't wait to implement this, as I've been manually going thru the same process. However, I noticed a discrepancy in the .SQL file I downloaded at lines 46-47.

The comment references SQLSTATE 020000, but in the DECLARE statement, it is FOR SQLSTATE '02000'. I just wanted to confirm that three trailing zeros as written in the actual statement is correct before running it for the first time.

Thanks!

2020-05-20_10-59-20.png

Share this post


Link to post
Share on other sites
32 minutes ago, JosefNT said:

Can't wait to implement this, as I've been manually going thru the same process. However, I noticed a discrepancy in the .SQL file I downloaded at lines 46-47.

The comment references SQLSTATE 020000, but in the DECLARE statement, it is FOR SQLSTATE '02000'. I just wanted to confirm that three trailing zeros as written in the actual statement is correct before running it for the first time.

Thanks!

2020-05-20_10-59-20.png

Kudos to you sir, you found a typo in my notes. The DECLARE statement is correct with the SQLSTATE 02000. I accidentally added an extra zero in the comment above the statement explaining the purpose of the DECLARE.

Share this post


Link to post
Share on other sites

Hello all.  I am having trouble trying to get this to run on the HostedRMM version of Automate.  I tried the SQL Execute script but it is not working.  I keep working on it with support and trying to get it figured out but with this being a custom script their support is limited. Any point into the right direction would be greatly appreciated.

 

First attempt:

1
The Script(5817) failed in the Then section at step 3. The reason: Label not found: CALL sp_Custom_DeleteClient(192);

Script:    NOC\Tools\Location Removal- Starting at Server Time: Wednesday, July 22, 2020 9:09:08 AM
IF    True         Time Taken:  0.0468825
L3    Script Call    Jump (P1): CALL sp_Custom_DeleteClient(192);     Time Taken:  0.0468825

7/22/2020 9:09:09 AM
ComputerID
Hidden_ComputerID

 

Second Attempt:

1
The Script(5817) failed in the Then section at step 2. The reason: SQL Execute failed processing query

Script:    NOC\Tools\Location Removal- Starting at Server Time: Wednesday, July 22, 2020 9:11:09 AM
IF    True         Time Taken:  1.5091626
L2    SQL Execute    Sql (P1): 'DELIMITER $$

USE labtech$$

DROP PROCEDURE IF EXISTS sp_Custom_DeleteClient$$

# Pr...'     Time Taken:  1.5091626

7/22/2020 9:11:11 AM
ComputerID
Hidden_ComputerID
 

 

Share this post


Link to post
Share on other sites
1 hour ago, AAMITS said:

Hello all.  I am having trouble trying to get this to run on the HostedRMM version of Automate.  I tried the SQL Execute script but it is not working.  I keep working on it with support and trying to get it figured out but with this being a custom script their support is limited. Any point into the right direction would be greatly appreciated.

 

Hey, save the query as a text file with a .SQL extension. From there, you can open the Control Center client, then go to System -> General -> Import -> SQL file.

Import the SQL file you created and that should add the procedure to your hosted database. From there you can call the procedure in a script as outlined above via SQL EXECUTE script function with CALL sp_Custom_DeleteClient(X);

If you still have issues, reply here or message me on the MSPGeek slack channel and I'll help you out.

Share this post


Link to post
Share on other sites

Thank you for the quick reply.  I have imported the file as an .SQL extension and when I try to execute the script function I get the error that it was not found....well now I feel dumb.  I was staring right at the answer that you pointed me to as well but was doing it wrong.  I changed the command to SQL Execute and it worked!  Now I will work on customizing it so I can call the location ID from the script and run it on the locations version having to change the ID each time before running it.

Share this post


Link to post
Share on other sites
1 hour ago, AAMITS said:

Thank you for the quick reply.  I have imported the file as an .SQL extension and when I try to execute the script function I get the error that it was not found....well now I feel dumb.  I was staring right at the answer that you pointed me to as well but was doing it wrong.  I changed the command to SQL Execute and it worked!  Now I will work on customizing it so I can call the location ID from the script and run it on the locations version having to change the ID each time before running it.

Hey all you need to do is a single command in the script. SQL EXECUTE is the function then use CALL sp_Custom_DeleteClient(%clientid%); Make this a client script and then run it against the client you wish to remove. As long are there are no computers (all are retired), the scrip should remove everything associated with the client you run the script against. The %clientid% variable is passed to the script when you run it against the client.

Share this post


Link to post
Share on other sites

Greetings,

I've imported the SQL file to my hosted Automate server and created a script that runs an SQL Execute with "CALL sp_Custom_DeleteClient(%clientid%);" however when I run the script against a client the script fails with the following:


1
The Script(390) failed in the Then section at step 1. The reason: SQL Execute failed processing query

Script:    CUSTOM\***GC - Delete client- Starting at Server Time: Tuesday, September 15, 2020 11:11:00 AM
IF    True         Time Taken:  0.570417
L1    SQL Execute    Sql (P1): CALL sp_Custom_DeleteClient(16);     Time Taken:  0.570417

9/15/2020 11:11:01 AM
ComputerID
Hidden_ComputerID
 

This script was ran against a client that has no computers. Please advise.

Regards,

Pat

Share this post


Link to post
Share on other sites
9 hours ago, Pat B said:

Greetings,

I've imported the SQL file to my hosted Automate server and created a script that runs an SQL Execute with "CALL sp_Custom_DeleteClient(%clientid%);" however when I run the script against a client the script fails with the following:


1
The Script(390) failed in the Then section at step 1. The reason: SQL Execute failed processing query

Script:    CUSTOM\***GC - Delete client- Starting at Server Time: Tuesday, September 15, 2020 11:11:00 AM
IF    True         Time Taken:  0.570417
L1    SQL Execute    Sql (P1): CALL sp_Custom_DeleteClient(16);     Time Taken:  0.570417

9/15/2020 11:11:01 AM
ComputerID
Hidden_ComputerID
 

This script was ran against a client that has no computers. Please advise.

Regards,

Pat

Hey Pat, sorry for the delayed reply. I'll build a script for you tomorrow morning to ensure the stored procedure is properly added to your environment. I'll send it via direct message. If you'd like, I'll be happy to have a remote support session with you as well. Let me know your thoughts! I'd love to get this working for you.

Thanks,

Jared

MSP Automated, LLC

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