SQL Agent Integration Setup

This guide will help you do the following so 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. 
   
3. Run the MSPbots MS-SQL Server Agent as an administrator.
4. 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. 
    
   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. 
   
   The previous;y installed service will be stopped and the new MSPbots MS-SQL Server Agent will be launched. 
   
   A successful installation will show the SQL Agent icon on the Taskbar. The agent will also be added to the Startup menu automatically.
   
   

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.
   
3. Click SQL Server // to show the Database Type section.
4. Select MySQL and enter the configuration information. 
   

5. Click Save And Sync. 

Create the SQL Agent Dataset to pull data  

1. Refresh your browser and navigate to Datasets on the MSPbots app. 
2. On the Datasets tab, the New SQL Agent Dataset button. 
   
3. On the New popup enter a Name and a Description for the dataset.  
   
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.
       

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

      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_user group 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.