Page History

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

steps for connecting the MSPbots SQL agent.

What's in this article:

Complete the prerequisites

...

Please create a new read-only account in your SQL Server or MySQL as follows: How to Create a Read-only Account for Your SQL Server or MySQL. If you already have a read-only account specifically for communicating with MSPbots, please skip this prerequisite.

Connect the MSPbots agent

...

...

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.
7. 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 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. 1. Image Added
   2. After the download is complete, follow the prompts to install the MSPbots SQL Agent client.
   3. During the setup, select Automatic Start and Create a desktop shortcut, then click Next. 
      Image Added
   4. In the last step, tick the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts.
      Image Added
   5. 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. 
      Image Added

Set up the MSPbots Agent Connector

...

1. Go to Integrations on the MSPbots app menu.
   Image Added
   
2. Search for SQL Agent and click it to open the connection settings. 
   Image Added
   
3. Click the +Add Connector button. 
   Image Added
   

   Info
   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.

   
   
4. 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. 

     Image Added

     Note
     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.

5. Go back to SQL Agent under Integrations.
6. Click the +Add Connector button. This action will show SQL Server // below Data Server.
   Image Removed
7. Click SQL Server // to show the Database Type section.
8. Select MySQL and enter the configuration information. 
   Image Removed
9. Click Save And Sync. 

Create

...

a dataset and validate the data

...

1. Go

...

Refresh your browser and navigate
1. to Datasets on the MSPbots app
. On the Datasets tab,
2. menu and click the New
SQL
3. Agent Dataset button. 
   
Image Removed
4. Image Added
On

5. When 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.  
6. In the SQL field, select the data from your databases. The best practice is to test the SQL in your databases first.
7. Provide a description. 
8. Click the DB field and select your dataset. 
9. 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


10. window appears, click Integration and select SQL Agent. Don't forget to fill in the dataset Name and Description fields.

    Image Added
11. Next, go to the Data Cleaning tab to show the Validate SQL section below.
    1. Select a database from the DB dropdown list. 
    2. Scroll down to the Validate SQL section, fill in the SQL field. The most common SQL statement to use here is “select * from TABLENAME”. 
    3. Input your desired Sync Frequency in minutes.
       

       Tip
       Sync Frequency is currently limited to 30 minutes and can only be decreased for smaller datasets. For larger datasets, the frequency may be limited due to a large amount of data ingestion and the constraint it would place on both your server and ours.

       
       Image Added
12. 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.
    Image Added
13. Once you confirm the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes.

Save the logs and send the file to MSPbots Support Team

...

1. Open the MSPbots Agent client and click the Save Log button on the upper right corner of the window. 
   Image Added
2. Click Confirm on the Tips popup that appears. 
   Image Added
3. Next, select the location to save the logs, provide a filename, and click Save.
4. Send the file to support@mspbots.ai. 

Related Topics

...