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.

The lt_scripts table. This screenshot shows it sorted showing the newest scripts first (highest scriptid). We can see that several scripts don’t have EditPermission values.

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.

The userclasses table. Here we can see the ClassID of the new class we created.

-- 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,');
The results of our update command; we can see the new permissions have applied. Please note we had to disable the safety to execute the insert statement.

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.

Want to get the latest from our blog delivered to your inbox?

Post Author: Jeremy Oaks

Jeremy is the founder of Automation Theory. He is passionate about all things technology, specifically in developing creative solutions. He received his bachelor's degree in Computer Science from the University of Wisconsin-Superior, and is also a certified MySQL DBA and penetration tester.