Page History

This guide will help you perform do the following tasksso you can connect the SQL agent to MSPbots:  

...

Download and install the SQL Agent

...

Client

1. Go to Integrations on the MSPbots app and search for SQL Agent.
   
2. On the SQL Agent, click the Download link to download the MSPbots SQL Agent Client file. You will need to install this on your SQL Server Management computer. 
   Execute the MSPbots SQL Agent as an administrator. Image Removed
3. The installer will require you to enter the Secret and Data Server values from Step 2.
   Image Removed 
4. Complete the installation on the Setup window.
   Image Removed
5. At the end of the Setup Wizard, you will be asked to launch the in the SQL Agent configuration. Enter the info in the Setup Wizard window and click Next. 
   Image Added 
   The MSPbots SQL Agent Client file will be automatically upgraded if an old version is installed. If this is your case, you will see the screen below. Select Automatically close the applications and click Next. 
   Image Added
   The previous;y installed service will be stopped and the new MSPbots MS-SQL Server Agent . Do not launch it because you need to run it as an administrator. 
   Image Removed
   Now that we have installed will be launched. 
   Image Added
   A successful installation will show the SQL Agent icon on your SQL Server, the next step that you need to do is to set up the configurations in MSPBots.

How to set up the MSPbots configuration 

1. the Taskbar. The agent will also be added to the Startup menu automatically.
   Image Added
   

Configure the database information   

1. Go back to SQL Agent under IntegrationsGo to Integrations on the MSPbots app and search for SQL Agent.
2. Click the +Add Connector button to add a data source then the . This action will show SQL Server //  button to expand the configuration space. 
   Image Removedbelow Data Server.
   Image Added
3. Click You should have the same view as below once you have expanded the SQL Server //. Next, enter the following: to show the Database Type section.
4. Select MySQL and enter the configuration informationDatabase Type - Select the SQL server that you have. 
   
5. IP 
6. Database Name 
7. User ID 
8. Password
   Image Removed

...

SQL User

The SQL user needs read permission only. Use the following SQL script as an example to create a read-only DB user and password. 

GRANT SELECT ON *.* TO 'mspbots_sql_agent'@'%' IDENTIFIED BY '123456';

Don't forget to change 123456 to your preferred password.

ConnectWise Automate (2021.1+)

1. Image Added

2. Click Save And Sync. 

Create the SQL Agent Dataset to pull data  

After creating the SQL user, add the user to the labtech.users table with a negative user ID to prevent the account from getting modified by ConnectWise Automate. Users that are not in the labtech.users table automatically get their passwords updated and ConnectWise Automate users are modified to not allow access to the SQL database. 

...

1. Refresh your browser and navigate to Datasets on the MSPbots app. 
2. On the Datasets tab, the New SQL Agent Dataset button. 
   Image Added
3. On the New popup enter a Nameand a Descriptionfor the dataset.  
   Image Added
4. Next, click the Data Cleaning tab to enter the data SQL data to be captured.  
   1. In the SQL field, select the data from your databases. The best practice is to test the SQL in your databases first.
   2. Provide a description. 
   3. Click the DB field and select your dataset. 
   4. Input your desired Sync Frequency. This setting is in minutes.
      Image Added 
   5. Click Confirm. This will send a test SQL and a preview of the data.
      Image Added
      

      Info
      If the aggregate function is used in SQL, please use the as statement to enable the alias function like this： Select status ,count(*) as counts from sys_usergroup by status

      
      Please wait until the data is synced in the next cycle. After sync, you can preview the data on the Dataset tab and use this dataset to create your widgets or bots.  
      Image Added