Jump to content

Search the Community

Showing results for tags 'database'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


  • MSPGeek
    • The Geek Cast
    • Code of Conduct
  • ConnectWise Automate / Labtech
    • ConnectWise Automate / LabTech
    • ConnectWise Automate / LabTech - Development


  • ConnectWise Automate
    • Scripts
    • Plugins
    • SQL Snippets
    • Role Definitions
    • Automate PowerShell Code
    • Reports
    • Internal Monitors
    • Remote Monitors
  • ConnectWise Manage
    • API Interacting Code

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start



About Me


Agent Count













Found 5 results

  1. With recent events, the demand for remote connectivity is high. For partners using the MAC Signup functionality issues can arise when remote connectivity solutions use duplicate MAC addresses. Like most issues in Automate, it's possible to work around this and use monitors/scripts/searches/etc. to create a creative solution. However, basic validity checking is a native function of MySQL, and there's an elegant approach that will help in this situation (and may not completely replace creative solutions). This guide is designed to demo how to use database triggers in MySQL to enforce data validation. This can be useful for several things, but we'll use the "duplicate software MAC" problem (Cisco AnyConnect VPN) as our example. Important notes: Database triggers are rather low-level items and are difficult to troubleshoot You can only have one trigger per time per operation per table This is unsupported by CW (both on the grounds of database modification and the manipulation of how the application works). With that, open up your favorite MySQL client and let's get started. DELIMITER $$ CREATE TRIGGER `computer_update` BEFORE UPDATE ON `labtech`.`computers` FOR EACH ROW BEGIN IF NEW.mac IN ('00-05-9A-3C-7A-00', '00-00-00-00-00-00') AND OLD.mac IS NOT NULL THEN SET NEW.mac = OLD.mac; END IF; END$$ DELIMITER ; The above code creates a trigger for the updating of the MAC address in the computers table. MySQL triggers can reference the old and new values, and they do so via KEYWORD.COLUMNNAME syntax. We begin by checking if the new MAC address is in the list of known bad MAC addresses ('00-00-00-00-00-00' is just added here for example) and if the old MAC address is not null. IMPORTANT: This means that if there's no existing MAC address (like on agent signup) this MAC could still be written to the DB. This is why the creative solutions might still have a place depending on the particular issue you're solving. For this particular issue, we're going to assume that these MAC addresses are already blacklisted for signup and we're just trying to solve for inventory updates. Next, we set the new value for the column to be the old value, as we'd want to keep the old MAC address if a user connects a VPN client. You'll notice that we switch the delimiter when creating this trigger. This is needed since our trigger itself contains semicolons, and to parse correctly we temporarily switch how we're terminating our lines. This may throw your particular SQL client for a loop. At this point, once we execute the code to create this trigger we are set. If you try to update a computer's MAC address to one of the values in the list the statement will still run without errors, but the value won't change (this would appear to be black magic to anyone who did not know the trigger was in place -- please be advised if you're considering a broader use case). Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  2. Hi Everyone, Since ConnectWise no longer supports MariaDB I've created a guide to help partners migrate to MySQL. The vendor provided documentation involves a long and convoluted logical backup process that can be avoided. This guide is the fastest method of upgrading to MySQL while allowing 100% ability to revert back in case of failure (since we'll be leaving MariaDB intact). Before we go any further please note that you can farm out this task if you want to focus on automation and not database administration: https://automationtheory.org/product/mysql-patching/ Now, onto the upgrade process (with pictures!)! Please note that this is a development server with four different versions of SQL installed -- you may see items in screenshots that are not applicable to a normal server (like LabMySQL not running when restarting the Database Agent). Here's a screenshot showing the existing version of MariaDB installed: Before we get started make sure you have your root password and enough disk space to hold twice your DB (we're going to duplicate it in order to do a "fail-safe" upgrade). This guide assumes that the DB upgrade is taking place on the same server and does not cover any application-level changes required for moving the database to a new host. Download and extract the ZIP file installer for MySQL 5.7 (link). Standard deployments extract to C:\MySQL, but it's not all that important (just remember where you put it). MySQL 5.7 does not include a data directory -- this is good, as we're going to do what's known as a physical backup where we move the existing MariaDB data directory to our new install. If you have downloaded MySQL 5.6 for whatever reason it DOES have this directory and it should be deleted. Open up SQLyog (or your editor of choice) and run the command "SET GLOBAL innodb_fast_shutdown=0" (not case sensitive). This will force data in the buffer files to be written to the actual tables (the buffer file formats aren't compatible, so we'll be deleting them). WARNING: Failure to do this will lead to potential data loss in three steps! Stop the service LabMySQL. Once the service has stopped copy the entire data folder from the MariaDB base directory to the extracted MySQL base directory. Also, your my.ini file can be moved there (MySQL will automatically apply it on startup without special instructions if it's in this location). Delete the files ib_logfile0 and ib_logfile1 (and if you're running an exotic configuration and you have more than two ib_logfileX files delete any others). Open an administrative command prompt in the bin directory of the new MySQL server and run the command "mysqld.exe --console". Scary error messages are to be expected, but the application should stay running. If it closes something has gone wrong (feel free to troubleshoot with the provided console messages -- but please note that this would be a good time to get a second opinion if the issue seems exotic). Open a second administrative command prompt in the bin directory (leaving the other one open). This time run the command "mysql_upgrade.exe -u root -p --force". This command will produce lots of crunchy messages; it's MySQL seeing the table files in a different format and doing all the under the hood conversions to make them match MySQL 5.7 formatting (note: the screenshot shows the wrong syntax). Once the mysql_upgrade has finished, go back to the command window where the server (mysqld) is running. Press CTRL+C to stop it, then up arrow to get the start command and re-launch it. When the server starts up it should have fewer error messages, but it will likely have errors about the mysql.user table. If you do not see errors about the mysql.user table, skip this step. Otherwise, open SQLyog and connect to the DB (note: if you can't get logged in this would be another good stopping point for a second opinion). Run the following commands to remove the extra columns that MariaDB has that MySQL doesn't. alter table mysql.user drop column default_role; alter table mysql.user drop column max_statement_time; alter table mysql.user drop column password_expired; At this point, it's worth repeating steps 8 and 9 to make sure that the application will be happy when connecting. Restart the Database Agent service. In the console you'll see some failed login attempts; this is normal. After 1-2 minutes try logging into the application. As shown here, success! We have upgraded to MySQL 5.7. Important: We're not done yet! The service LabMySQL is still pointed at the old database instance. If the console closes or the server reboots you'll lose MySQL. To correct this close the application, stop the server, delete the old service (sc delete LabMySQL), and run the following command "mysqld --install LabMySQL". Please note that you can add the option " --defaults-file=C:\path\my.ini" but as previously mentioned this is not required if the my.ini file is in the base directory of the MySQL server. At this point, MySQL should be the persistent database for the Automate server. Start the service LabMySQL, restart the Database Agent, and verify the application launches. To be extra sure you can restart the server to make sure that the service starts on boot. Please note that the old database is 100% intact at this point. Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  3. Forgive me if this write-up is crap, I'm fairly new to Automate. But we have the Warranty Master plugin to help pull warranty information for devices. Unfortunately, Warranty Master does not sync with the computers table in the labtech database by default. We are connected to Manage and want warranty dates to sync so that we can automatically start ticketing based on Warranty expiration dates. We have found that the WarrantyEnd dates in the computers table is often random and totally unreliable. To solve this problem, I wrote a few database modifications to sync the dates. Disclaimer: All code is provided for reference use only. You MUST customize it to your own use case. Prerequisites: Just an EDF for PurchaseDate at the Computer Level. Solution: Since our labtech database is running on a MySQL database the solution for this is easy. Triggers. You can learn more about MySQL triggers here: https://dev.mysql.com/doc/refman/8.0/en/triggers.html Basically, the purpose of a trigger is to detect when a change is made to a table and to perform a certain action when it does. In our case, we are detecting changes to the `plugin_warrantymaster_aux` table and updating the `WarrantyEnd` field in the appropriate rows in the computers table with the new values. We are also updating the `extrafielddata` Value field where the row shares the same `ComputerID` and `ExtraFieldID`. This can get a little tricky as the `extrafielddata` table is a junction table for the many-to-many relationship between computers and `extrafield` tables. Be sure you understand what my code does before running it. Scripts: I have attached two scripts which I used to create this sync: AGAIN, ALL CODE IS PROVIDED FOR REFERENCE USE ONLY. !PRNWarrantySyncCommands.sql This script will provide an initial sync for your database. Since our triggers only run AFTER UPDATE and AFTER INSERT on the plugin_warrantymaster_aux table It may be necessary to do an inital sync. MAKE SURE YOU USE THE CORRECT ExtraFieldID(Not 577). !PRNWarrantyMasterSyncTriggersSetup.sql This script will automatically setup the triggers required for the sync to work automatically. MAKE SURE YOU USE THE CORRECT ExtraFieldID(Not 577).
  4. The root password is something that is easy to forget about until you really need it. Then it becomes a wild goose chase looking through password managers, documentation systems, and any other place that password might be used. This guide is meant for the situation where database maintenance is required and the root password is unknown. Items worth noting Every super admin account in Automate has the same permissions in the database as the root user. If your server has been around for a while (pre 10.5) it might be plain text in the registry under the key HKLM\Software\WOW6432NODE\LabTech\Setup There's nothing special about the root account -- it's very similar to Administrator in Windows. Best security practice is to create a new account with the super permission and not use root (unfortunately in the CW Automate world this best practice is difficult to implement). IMPORTANT: This guide is actually going to instruct you how to create a new account with the super and grant permissions -- we won't actually be resetting the root password. The root password used at server install is encrypted and stored in the registry and is used by Automate patches and other support utilities. If you change the root password you're going to break things in Automate down the road. An example, the 2020.2 patch: Now, without further ado, the new super account creation process. To begin, here's a screenshot to show that we're locked out of a new MySQL 5.7 instance: [In case you're wondering, MySQL 5.7 randomly generates a root password if one is not specified, and it's recorded in the error file.] Create a text file with the following commands, and save it as a .sql file: CREATE USER 'rootbeer'@'localhost' IDENTIFIED BY 'abc123!'; GRANT ALL PRIVILEGES ON * . * TO 'rootbeer'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'rootbeer'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; I'll be calling the account 'rootbeer' and using an insecure password for the sake of demonstration. Good security practice is strongly advised. Stop the service LabMySQL Open an administrative command prompt, and navigate to the bin folder of your MySQL install, wherever mysqld.exe is located. Start MySQL passing the script you created in step one as the init-file parameter. mysqld.exe --console --init-file C:\path\to\reset.sql When the server starts it should show in its output that it ran the script. Press CTRL+C to shutdown the server once you see this. As you can see in the other window, we're successfully able to login. Start LabMySQL again. Login as your new super account. As you can see here, ours has all global privileges and the DBA role. Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  5. Question, I want to edit a stored procedure but not sure if I can access the DB. We currently have Sqlyog but a old version and it won't let me update anything if we don't purchase the full version. I was wondering, My question is can I use a different DBMS i.e. Mysql workbench to access the DB?
  • Create New...