ConnectWise Manage - SQL Integration Setup

Prerequisites

You must have the following to ensure a successful connection between MSPbots and ConnectWise Manage:

• A new read-only account in your SQL Server or MySQL for communicating with MSPbots
• Advanced knowledge of SQL databases and scripts  
• Admin role for modifying the settings in the MSPbots app 

Limitations

Before connecting to the ConnectWise Manage - SQL integration, take note of the following limitations:

• Ensure that the server memory or CPU usage for installing the MSPbots SQL Agent Client is below 90%. If it reaches 90%, the SQL Agent stops syncing.
• The minimum sync frequency for each dataset is 30 minutes, and the maximum sync data volume is 250,000.
• A maximum of 2 datasets can be executed in parallel at the same time. If more than 2 datasets are run simultaneously, the extra ones are queued, which may cause synchronization delays for some datasets.

How to establish the MSPbots integration with ConnectWise Manage 

1. Connect the MSPbots Agent

1. 1. Go to Integrations on the MSPbots app menu. 
   2. Find the ConnectWise Manage - SQL and click the Config icon. 
      
      
      
      
   3. Next, click the download icon for the MSPbots SQL Agent Client to start downloading the installer file. 
      
      
      
      
   4. After the download completes, follow the prompts to install the MSPbots SQL Agent Client.
      
   5. During the setup, select Automatic Start and Create a desktop shortcut, then click Next. 
      
       
      
      
   6. In the last step, select the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts. 
      
      
      
      
   7. 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. 
      
      

2. Set up the MSPbots Agent Connector

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

      The last success auth shows the last time when the MSPbots Agent successfully pulled the database configuration information. This process is set to occur every 5 minutes by default.

       
      
   4. Fill in the following required fields.
      • Database Type - Select either SQL Server or MySQL.
      • Agent - This field is populated automatically with the name of the installed machine.
      • 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.
         
        
        
        

        It may take a few moments for the agent to check in after installation. 
        
        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. Click Save and Sync.

3. Create a dataset and validate the data

1. 1. Go to Datasets on the MSPbots app menu and click the New SQL Dataset button.
      
      
      
      
   2. When Warning popup opens, click OK. 
      
      
      
      

   3. When the New window appears, click Integration and select ConnectWise Manage - SQL. Don't forget to fill in the dataset Name and Description fields.
      
      

      
      
      
   4. 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 and fill in the SQL field. The most common SQL statement here is “select * from TABLENAME”. 
      3. Input your desired Sync Frequency in minutes.

          

         
         
         
   5. 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. 
      
      
      
      
   6. Confirm if the data is correct, then click OK to create and save the dataset. The data will be synchronized in 10 minutes.
   7. Back to Integrations and click the ConnectWise Manage - SQL integration to check the status of the datasets you created.
      
      
      
      
      • The status of the datasets includes Success, Failed, and Idle.
        
      • When a dataset fails,
        • The ConnectWise Manage - SQL integration will display the    icon. For example,    means there are currently 3 datasets that have failed.
          
        • The Error column will show error information.
          
      • Click the dataset link to jump to the dataset page to view the dataset information.
      • The Idle status means when editing the dataset, the dataset's Status switch has been turned off in Data Cleaning.
        
        
        
        

4. Save the logs and send the file to the MSPbots Support Team

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