Page History

This guide will help you do the following so you can successfully connect the SQL agent to MSPbots:  

...

1. Verify if you are using a previous version of SQL Agent.

If you have SQL Agent 3.0 installed, proceed to Step 2. If an older version is installed, uninstall it first with the following steps:  

1. 1. Go to the Agent installation directory. 
   2. Find the unins000.exe uninstall file and run it. 
      Image Added 

2. Download and install

...

SQL Agent

...

3.0.

1. 1. Go to Integrations on the MSPbots app
and search for
2. 1. menu. 
   2. Find the SQL Agent and click the Config icon.
      
Image RemovedOn the SQL Agent
3. 1. Image Added
   2. Next, click the
Download link to
4. 1. download icon for the MSPbots SQL Agent Client to start downloading the installer file.
 
5. 1. 
      
Image Removed
6. Run the MSPbots MS-SQL Server Agent as an administrator.
The installer will require you to enter the Secret and Data Server in the SQL Agent configuration. Enter the info in the Setup Wizard window and
7. 1. Image Added
   2. Locate and double-click on the downloaded installer.
   3. During the setup, select Automatic start and Create a desktop shortcut, then click Next. 
      
Image Removed 
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 Removed
The previous;y installed service will be stopped and the new MSPbots MS-SQL Server Agent will be launched. 
Image Removed
A successful installation will show the SQL Agent icon on the Taskbar. The agent will also be added to the Startup menu automatically.
Image Removed

Configure the database information   

1. Go back to SQL Agent under Integrations.
2. Click the +Add Connector button. This action will show SQL Server // below Data Server.
   Image Removed
3. Click SQL Server // to show the Database Type section.
4. Select MySQL and enter the configuration information. 
   Image Removed

5. Click Save And Sync. 

Create the SQL Agent Dataset to pull data  

1. 1. Image Added
   2. Follow the prompts. In the last step, tick the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts.
      
      Image Added 

3. Set up the MSPbots Agent Client.

1. 1. Open the Agent client by double-clicking on the shortcut you created in Step 2e above. 
   2. Log in using your MSPbots account. You can Minimize or Close the window after login but do not log out. 
      Image Added
   3. Go back to Integrations and click the Add Connector button.
      Image Added
   4. Scroll down to the SQL Server section and select a Database type.
      Image Added 
   5. Configure the following database information:
      For SQL Server 
      The Database IP information configuration rules are:
      Default instance + Default port (1433): IP
      Default instance + Custom port: IP,XXXX
      Default instance + Custom port: IP\ Instance Name,XXXX 
      For My SQL
      The Database uses the default port (3306): IP 
      The Database uses a custom port number: IP;port=XXXX 
      
      Image Added
   6. After the configuration is complete, click Save And Sync. 
   7. Refresh your browser. 

4. Create a dataset and validate the data.

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

Click Confirm. This will send a test SQL and a preview of the data.
Image Removed


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 statusPlease 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 Removed

...

1. 1. When the New window appears, fill in the dataset Name and Description fields.
      Image Added
   2. Next, go to the Data Cleaning tab to show the Validate SQL section below. Select a database from the DB dropdown list. 
      Image Added
   3. Scroll down to the Validate SQL section, fill in the SQL field, and input your desired Sync Frequency in minutes.
   4. 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.
      Image Added
   5. Once you confirm the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes. 

...