SQL Agent is used to connect to your own SQL servers to convert local SQL server data into a dataset inside MSPbots that you can use for dashboard purposes.
Due to the super flexible use case and complexity nature of the custom SQL code and how the SQL agent data is pulled, please keep in mind MSPbots only suggest SQL agent datasets in these two situations with conditions:
- BrightGauge Migration, if you have dataset that inside BrightGauge and we offered free migration, we will support the dataset Migration and very limited modification. Only Standard templated ConnectWise Manage, ConnectWise Automate, N-able N-central and E-automate SQL dataset is supported, any new dataset should be developed using API dataset instead.
- If you are SQL expert yourself and you can write your own SQL dataset, we can't support the generic non MSPbots standard SQL dataset since there are just too many different situations that we are not able to train our employees to be capable handling all kinds of situations.
This guide will help you do the following steps for connecting the MSPbots SQL agent.
1. Complete the prerequisites
Create a new read-only account in your SQL Server or MySQL. Proceed to the next step if you already have a read-only account specifically for communicating with MSPbots.
2. Connect the MSPbots agent
- Go to Integrations on the MSPbots app menu.
-
Find the SQL Agent and click the Config icon.
-
Next, click the download icon for the MSPbots SQL Agent Client to start downloading the installer file.
- After the download is complete, follow the prompts to install the MSPbots SQL Agent client.
-
During the setup, select Automatic Start and Create a desktop shortcut, then click Next.
-
Select the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts.
-
Launch the MSPbots agent and log in using your MSPbots account. You can Minimize or Close the window after login but do not log out.
3. Set up the MSPbots Agent Connector
- Go to Integrations on the MSPbots app menu.
- Search for SQL Agent and click it to open the connection settings.
-
Click the +Add Connector button.
The last success auth shows the last time when the MSPbots Agent successfully pulled the database configuration information. By default, this process is set to occur every 5 minutes.
-
Fill in all of the following required fields. It may take a few moments for the agent to check in after installation.
- Database Type - Select either SQL Server or MySQL.
- Agent - The agent will auto-populate with the machine name you installed.
- IP - Provide the internal IP address or FQDN of the SQL or MySQL server.
- Database Name
- Time Zone - Select the time zone used by the connected database.
- User ID - This is the username of the user with read-only access to the database.
-
Password - This is the password of the user with read-only access to the database.
If you have installed multiple MSPbots agents with different databases in different network segments, you have to select the device name where you manually installed the MSPbots Agent. You can find the device name in the Agent selection box. Select the one under the corresponding gateway of the database.
- Click Save And Sync.
4. Create a dataset and validate the data
-
Go to Datasets on the MSPbots app menu and click the New Agent Dataset button.
-
When the New window appears, click Integration and select SQL Agent. Don't forget to fill in the dataset Name and Description fields.
-
Next, go to the Data Cleaning tab to show the Validate SQL section below.
- Select a database from the DB dropdown list.
-
Scroll down to the Validate SQL section, and fill in the SQL field. The most common SQL statement to use here is:
“select * from TABLENAME
If you encounter an error prompt, refer to Error Prompts for Validating SQL When Creating a Dataset. -
Input your desired Sync Frequency in minutes.
Sync Frequency is currently limited to 30 minutes and can only be decreased for smaller datasets. This frequency may be limited for larger datasets to prevent possible server issues that may arise from processing large data.
-
Click Confirm when done. This will show a preview of the data for your validation. You can customize or use the default character length for string-type fields, and the time field can be set with a timezone.
- If you confirm that the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes.
5. Save the logs and send the file to the MSPbots Support Team.
-
Open the MSPbots Agent client and click the Save Log button on the upper-right corner of the window.
-
Click Confirm on the Tips pop-up window.
- Next, select the location for saving the logs, provide a filename, and click Save.
- Send the file to support@mspbots.ai.
6. How to uninstall/disconnect SQL Agent connection
When the user is not using SQL Agent, the admin needs to refer to the following steps for operation:
1. Removing SQL Agent connection in MSPbots integration
- Go to Integration on the MSPbots app menu.
- Searching for SQL Agent in the search bar, click the ellipsis button and then select Remove.
- When the pop-up appears, click Remove.
2. Uninstalling SQL Agent from your computer
- Open the Control Panel on your computer.
- Click Uninstall a program.
- Locate the program MSPbots Agent version 3.0.x and right-click on it, then select Uninstall.
- When the MSPbots Agent Uninstall pop-up appears, click Yes.
7. How to update the MSPbots Agent
If you want to update the MSPbots Agent to the latest version, see 2. Connect the MSPbots agent to re-download and install the MSPbots Agent.
8. Known issues
When you encounter the prompts Unable to connect to database or Please check your User Id or Password, Error: Login failed for user, it may be due to a database connection failure.
Refer to How to Resolve "Unable to connect to database" Error to solve the issue.