Jump to content
[[Template core/front/profile/profileHeader is throwing an error. This theme may be out of date. Run the support tool in the AdminCP to restore the default theme.]]

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. 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
  2. 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
  3. 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
  4. 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
  5. Hey, I'll be glad to help you out. I'll post some instructions as soon as I get home. Jared
  6. 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!
  7. Ok, are you on slack? Give me a few to grab my laptop and message me at jbrantley
  8. Do you have any VNC applications installed on the agent?
  9. Glad to hear it! Thank you for the feedback!
  10. 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
  11. 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
  12. My apologies for not updating this post. A Support Manager at LabTech provided the below answer: Thanks for contacting LT support I reviewed this ticket and I think I have information you are looking for in regards to these questions. Q. If the alert template calls creates the script, does the %ticketid% variable get set at that time as it is outside of the Script Engine? A: If you have a monitor that uses the alert template to create a ticket and then run a script the %ticketid% variable will not have that ticket id set. If you have the script create the ticket using the Ticket Create function it will populate. Q: The variables %status% and %when% that are created by the monitor fail state; can those variables be called inside of a script initiated by the same alert template that the monitor initiates? A: Variables passed from a monitor can be used just like any other variable in your scripts If you want to get a list of all the available variables add a line to the script you want to know about that runs the _System Automation\Functions\Show Variables script it will log all the variables available including ones that are passed by monitors or other scripts. See for more https://docs.labtechsoftware.com/knowledgebase/article/5826 If you still have questions simply respond to this email and we'll continue working on your inquiry accordingly. Thank you for contacting Labtech Support
  13. Out of curiosity, are you guys disabling the P2P update downloads on Windows 10 machines? We've encountered some bandwidth issues as a result of this function.
  14. We're getting patches through the pilot program right now. Just ran patch 4 last night and supposedly got a working Patch Compliance Report. While I can view the report now, there is no data in it. Apparently Patch 4 was identified today as breaking the report that was just fixed? Yeah, I'm lost on that one too.
  • Create New...