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 building custom reports (based off a custom table created in our last installment). 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.
Building Reports
For our purposes building reports won’t be limited to the Report Center in Automate, but that is what we’ll show here. This will apply to any reporting tools that can query MySQL.
What we’re going to do is create a view, which is a stored select statement that can be referenced like a table. Our first step is to create a select statement for the information we want. This would be a great time to leverage custom tables, or if you wanted to simplify RAWSQL monitors the base joins (computers, locations, clients, etc.) could all be stored in a view as well.
For our example, we’ll create a view of our custom table created in the previous installation in this series to report on Windows 10 versions and their end of life date. The query is below. [Please note that the computers table in a default install of Automate does not index on OS name or version number. This query would by default trigger a full scan of the computers table. We’ve appended a limit clause to minimize performance impact.] Views don’t allow for duplicate columns, so when joining tables that have overlapping column names we must specify each column.
select computerid, computers.name as ComputerName, version, eol_date, locations.name as LocationName, locations.locationid, computers.clientid, clients.name as ClientName from computers join custom_windows10_versions on computers.Version = custom_windows10_versions.version_number join Locations ON Locations.LocationID=Computers.Locationid LEFT JOIN Clients ON Clients.ClientID=Computers.clientid where OS like '%Windows 10%' limit 10;
Now to create a view from this statement we’ll simply prepend create view v_custom_report_win10_eol as in front of our select statement. Execute that statement, and please note in the Database Commander UI it’s normal to see “Error” in the last query status for operations that don’t return any columns. Once that statement is complete we can run select * from v_custom_report_win10_eol; and get all our data.
Now that the view is setup we can modify a report to include it. To do this you’d open the report center, select a report, choose modify, and then you’d see something like a screenshot below. You’d add a data source, select the newly created custom view, and add the requested columns. Once that’s done a relationship to the existing data is required. Since this is a computer base report and the OS EoL date is a property of a computer we create a relationship based on computerid. However, the appropriate mapping depends on your context.
After this point, the task turns into making meaningful reports, and that’s beyond the scope of this guide (and there are other guides and consultants specifically for reporting in Automate).
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.
