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 MySQL language basics. 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.
First, let’s start with things you can do with the Automate database. After all, why learn MySQL when you have other things to do? Here are the big-ticket items that MySQL can unlock:
- Bulk application administration
- Creating custom monitors
- Facilitating custom integrations
- Building reports
With that, this guide will go over the basics of the language.
Getting started: Querying data with MySQL
MySQL has a set of keywords for what’s known as data manipulation language (DML). These are keywords like select, insert, update, and delete. The select keyword is read-only, and it will be the most commonly used statement. For example, to pull all the information from the computers table, we’d execute the statement:
select * from computers;To refine our select statement we can replace the asterisk with the names of the columns we want (fewer columns means less data to pull, and therefore faster queries). If we only needed the hostname and the computer ID we’d use the statement:
select `name`, computerid from computers;Since the column with the hostname (name) is a MySQL keyword we enclose it in back-ticks to specify it as a column name.
It’s also possible to do additional filtering with the use of a where clause. This clause will cause the query to only return rows matching the specified condition (and conditions can be strung together with the and & or keywords). To make the query return server-named OSes we can do the following:
select `name`, computerid from computers where os like '%server%';
In MySQL, strings can be in single or double-quotes. The percent sign is a multi-character wild card in MySQL (similar to * in other languages).
Joining tables in MySQL
A join query returns data from multiple tables (like the client name for computer x is in the clients table). Joining tables is easy syntactically, the potentially difficult part is determining the relationships. Most tables you’ll start using are fairly obvious.
To include client name, we’d modify our query as follows:
select computers.`name`, computerid, clients.`name` from computers join clients on computers.clientid = clients.clientid where os like '%server%';
There are two items to note here. First, both tables have a column called name, so we must prefix the table name when referencing them, otherwise, we’ll get an error since the reference is ambiguous.
Next, you’ll see that we’re just using the join keyword. If you’ve used any other database before you’ll know that there are multiple types of join operations. MySQL uses inner join as the default, which means only rows with matches in both tables are shown. If you’re querying for data that might be absent and want to see it (like computers joined on plugin_screenconnect_scinstalled, where a computer without Control wouldn’t show) using a different join (left join, full join) would be appropriate.
The on clause is how we map the data in separate tables together. In this example, we’re matching on clientid, and this of course is logical based on the column names. For multiple joins it’s normally more efficient to join off the table you’re selecting from rather than another joined table. For example, if joining computers, locations, and clients, selecting from computers the clause join clients on computers.clientid = clients.clientid is more efficient than join clients on locations.clientid = clients.clientid since the computers table is already being accessed (although both will technically work).
At this point, you should be able to spot-research the construction of any particular queries (or field your questions in the community forums or slack channels). This portion of the MySQL documentation will also be extremely valuable.
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.
