Good database schema documentation can be hard to find, especially for Connectwise Automate (labtech database). The current schemas published elsewhere are outdated, especially considering the application modifies the schema with each patch.
Here at Automation Theory, we wanted to fix that. Our goal is to provide an always-up-to-date schema guide. To do this we’re leveraging an automated schema mapping tool, and we intend to update the schema for each Automate patch. The current schema is for Connectwise Automate 2021 Patch 10.
IMPORTANT NOTE: To improve the diagramming we set labtech.computers.computerid to be a primary key (in the default schema the computerid column of the computers table is not a PK; it’s only a unique index).
Helpful schema navigation tips
The labtech database has a structure that is less than ideal. However, certain things are not easily changeable once in production. The first implication of this is that the schema mapping tool doesn’t always detect relationships correctly (it’s making a lot of educated guesses). The second implication is that there’s a steep learning curve in navigating the database. Below are a few tips for Automate administrators new to the database:
- The computers, clients, and location tables are going to be your bread and butter
- Certain tables (like dependancy and aditionalschedules) and certain columns are misspelled
- The delimiter in delimited data is not consistent (you’ll see a mix of pipes, colons, etc.)
- There are no foreign keys or constraints (this leads to orphaned data in certain tables)
- The data structure is not normalized in any meaningful way
- Certain tables for core features have lt_ prefixed (for example, the actual data of a script is stored in lt_scripts, not scripts)
- The schema was generated from an NFR server with only a few agents (and no heavy customization). The row count data should not be used to imply any meaning for the structure
Using the labtech database schema documentation
- On the top of each page for tables, columns, and routines you’ll find a search box; it’s useful for rapid navigation
- Constraints is empty due to the schema itself not having any
- The relationships page shows a graphical representation of the implied relationships. You can navigate by clicking/dragging on the graphic
- Every table in the schema appears to be an orphan due to the lack of foreign keys
- The anomalies information shows some of the implied relationships and poor structure. Please note that certain column names (like GUID) are reused and can’t be relied upon to determine a relationship
With that, we hope you enjoy the schema documentation. If you’ve found this helpful we also have a blog series for MySQL for Automate administrators.