Jump to content


  • Content Count

  • Joined

  • Last visited

  • Days Won


jbrantley last won the day on December 23 2018

jbrantley had the most liked content!

Community Reputation

1 Neutral

My Information

  • Location
    Alpharetta, GA
  • Agent Count
    4000 - 6000 Agents


    Scripting, MySQL, PowerShell, basic coding
    Providing consulting services for ConnectWise products as well as technical manager for a MSP in the Atlanta, GA area.

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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
  6. 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. 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 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
  7. Hey, finally home! I don't have immediate access to a version 12 environment but this should more or less allow you to add a single PC to a contact. 1. From the Control Center, select Browse > Clients tab then either double-click on the desired client or right-click on the desired client and select Open. The Client window is displayed. 2. Click on the Contacts tab. 3. Select/highlight the contact in question. 4. On the right side, there should be a box for Associated Computers. Click the drop down arrow and find the computer in the list. This will assign the computer to the contact record. The backend SQL command that is issued is INSERT INTO ContactComputers (ComputerId, ContactId) VALUES (@ComputerId@,@ContactID@); Note: This is assuming that you have configured the contact record to have web access to your Web Control Center/Client Portal. Refer to my earlier guide in this post for configuration steps. Your client will then be able to navigate to your WCC URL, log in with the email address and password specified, then access the computer from this portal. The remote access feature will allow them to connect to the agent via ConnectWise Control (ScreenConnect). If you have issues with this, let me know and I'll be happy to help if I'm able. Have a great night! Thanks, Jared Brantley
  8. Glad to help. It may be a couple of hours if that's ok. Running a few errands before starting vacation tomorrow morning :-) I'll definitely get you going though. Jared
  9. Hey, I'll be glad to help you out. I'll post some instructions as soon as I get home. Jared
  10. Hey guys, sorry I didn't see this yesterday. The notifications were flagged as spam. Anyway, you absolutely can associate a computerid to more than one contactid. There's many ways to do this including usage of a stored procedure or modifying the script. In efforts to manage the people that have access, I tend to copy the script then edit it based on the additional contact that needs access. If you were to look at my script folder tree, you'd see this script in the Client Access - ScreenConnect folder and then underneath that folder you would find an instance of that script for each contact with access. They follow the Client Name - SC Access - Contact Name format. This allows anyone else in our organization to see exactly what clients and what people have access with this method since no one else in our organization does any scripting or work in Automate at all. If you have any other questions or feedback, please let me know!
  11. Ok, are you on slack? Give me a few to grab my laptop and message me at jbrantley
  12. Do you have any VNC applications installed on the agent?
  13. Glad to hear it! Thank you for the feedback!
  14. Hey everyone, A few of you may have seen me present this solution in the partner quickfire session at AN17 last week. As requested, here is that solution available for your use: 1. Create a contact record for the customer/client associated with his company. Ensure that the email address is unique as this will be the username. 2. After saving the contact, open the record. On the permissions tab, set any permissions desired but ensure that "Remote Access" is enabled. Note the ContactID in the top of the window and click Save. 3. Open the attached script. Specify the ContactID in line 3 as the ContactID you just created/noted. Set the clientid in line 2 as the target client to grant access. 4. Schedule the script to run at your desired interval. The script will perform the below actions on each run: a. Remove access for any machines with the exclusion set b. Grab all computer ID's associated with the client ID specified that are not servers. c. Loop through each result and add the contactid and computerid to the ContactComputers table. 5. This will allow the contact/customer to login to your web control center (without 2FA). He/She will use the specified email address as password as credentials. Once inside, simply click the Computers option and a searchable list of computers will be available. Open the desired computer, click "Remote Access" and a ScreenConnect session will then launch. Ensure that the browser has the pop up blocker disabled or otherwise excluded. This is really helpful for clients that have an internal IT person that may need remote access without implementing another solution. Not to mention, You can charge a recurring monthly fee! Please let me know if you find this helpful! If so, I'll continue to post things like this periodically. Thanks, Jared Brantley Set ScreenConnect Access.zip
  15. Hey everyone, I saw a post in the #labtech channel this morning and it has prompted this post. I'd like to build one, all-inclusive disk cleanup script. I'd like your feedback and opinions on folders and paths that you would cleanup in your maintenance routines taking in to consideration specific applications. The goal is to start a running list that can be added to a script. On completion, the script will be posted here for all to use. Thanks in advance for any contributions you may have! -JB
  • Create New...