MSP Automated, LLC

Cleaning up Offboarded Clients

Do you have former clients cluttering up your Automate client list? Does part of your client offboarding process require you to move all former contacts, passwords, etc to a "Disabled Clients" or similarly named client in Automate?


We have a solution for you! With a simple MySQL stored procedure, you can remove all network devices, passwords, retired assets, and locations.


The code below will check the client for the existence of any computers still associated with it. If computers exist, the stored procedure will exit without deleting anything. Once it completes this safety check, the stored procedure grabs all locations associated with the client and deletes the network devices, retired assets, passwords, and location stats before deleting the location itself. Lastly, once all locations are removed, the procedure locates all references to the clientid across the entire database and deletes the associated client reference completely removing the former client from Automate!

DELIMITER $$


USE labtech$$


DROP PROCEDURE IF EXISTS sp_MSPA_DeleteClient$$


# Procedure is launched by running CALL sp_Custom_DeleteClient(%clientid%);
CREATE PROCEDURE sp_MSPA_DeleteClient(IN CLID INT)   
    
 # Start of procedure.
 sp_MSPA_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_MSPA_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.
		DECLARE TBLNAME_CUR CURSOR FOR SELECT table_name 
						FROM information_schema.columns 
						WHERE column_name='clientid' 
						AND table_name NOT LIKE 'v_%';
		
		# 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_MSPA_DeleteClient $$


DELIMITER ;


Once this procedure is added to your Automate database, it becomes as simple as running a script against the target client. The script function needed is SQL Execute and the SQL query needed is below:

CALL sp_MSPA_DeleteClient(%clientid%);


Just like that, you'll have a nice and clean client list!

Leave a Response