Leveraging the database of Automate can be intimidating, especially for partners who don’t have previous database experience. With the release of our new plugin Database Commander, we wanted to create an introductory guide to using MySQL in Automate, and simultaneously demonstrate the features of the plugin. Please note that you can use any MySQL query editor with the following guide.
We’ve split each item above into its own post, and in this installment, we’re going to look at bulk application administration. You can see all our MySQL 101 for Automate entries by clicking here.
Before we dive into MySQL, please note that we provide this content for informational purposes only. While we believe all the commands in this series are safe to run, please do so at your own risk. Automation Theory advises all partners to have good backups and to use a dedicated test environment wherever possible.
Bulk application administration
Let’s say you wanted to change permissions on all scripts in your system so only the designated user class “Script Editors” could edit the scripts in your system. This can be done with a few quick queries depending on the desired result.
Before we begin it’s very important to note that write operations to MySQL can’t be easily reversed and that extreme caution should be used. The below queries will modify permissions on scripts if executed on a live system.
A select * from lt_scripts; shows a column named EditPermission. This is a comma-separated list of user classes that are allowed to edit a script. Scrolling through the whole list on a default system will show that some scripts have “0,” as their permission, meaning that anyone could edit them. When the permission value contains non-zero values only those user classes have access. Please note that the values have a trailing comma, and to ensure that our changes don’t break anything our new permissions should have a trailing comma as well.
A select * from userclasses; shows the classID of all the user classes. In this example, the classID we want is 11. Now, we can either add this user class to the scripts missing permissions, or to all scripts. Examples for both methods are below.
-- Update scripts missing permissions
update lt_scripts set EditPermission = '11,' where EditPermission = '0,';
-- Add to all scripts
update lt_scripts set EditPermission = concat(EditPermission, '11,');
In our second example, we use the concat() operator to append the new user class to the existing ones.
The database can be leveraged to perform a wide variety of administrative tasks in Automate, but determining the correct query is important. Both the included SQL Spy application and the data dictionary can be useful in this regard (along with other community resources). If certain queries are frequently used they can be loaded into a script for quick access.
We hope that this installment has been helpful for you. Here at Automation Theory, we’re certified MySQL DBAs dedicated exclusively to the Connectwise Automate software stack. Be sure to check out our integrations and services.
