Connecting To ConnectWise Manage On Premise

Do you use ConnectWise Manage as your PSA system? Do you have it setup on a local SQL database? Great! BrightGauge has a featured integration with ConnectWise Manage On Premise. Here’s a quick document to help you get it connected:

On first login, from the account Overview page, click on the "Add a Datasource" button:

Or, from the first "Welcome to BrightGauge" dashboard page, click on the "Connect a Datasource":

Existing Accounts

From the account Overview page (to access click on the BrightGauge logo on the top left hand corner), click on the green "+" towards the bottom right of the page (next to the list of your exiting datasources):

Or, click on "Data" menu on the the top righthand corner of the screen, and select "Datasources" from the drop down menu:

Now click on “Add a New Datasource” and then click on the "ConnectWise Manage" icon:

Whether a new or existing account, you'll come to datasource settings screen below.Leave this page open as it will be used in Step 4. You can now proceed onto the next steps.

Step 2 - Download and Install BrightGauge Agent

Since ConnectWise Manage runs off a SQL database, you’ll need to download and configure the BrightGauge Agent. The link to download the BrightGauge Agent is on this screen (Item "A" above). Documentation to help setup configure the BrightGauge Agent can be found here. Make sure to install the BrightGauge agent on a machine somewhere within the same network as your MS-SQL database for ConnectWise Manage. If you have issues, there are some known issues here or email support@brightgauge.com. If you want to limit the firewall exception to inquiries from our IP addresses, you can see them above item "B" in the image above (blurred out here, login to see the addresses).

Step 3 - Create a SQL user within ConnectWise Manage database

Once the agent is up and running, you’ll now need to create a SQL user within the ConnectWise Manage database. ConnectWise Manage runs off of MS-SQL, so here’s a guide on how to do that. Make sure to enable the user to log into the ConnectWise Manage database. The ConnectWise Manage database starts with cwwebapp_ and is usually followed by your company ID (Ex. "cwwebapp_brightgauge"). You’ll also need to put this database name into #5 in the screenshot above.

For ConnectWise Manage, we also need the EXECUTE permission. This is a requirement for some of our more advanced datasets. This is related to technician costs, so if you're not interested in the data related to that, used in metrics looking at Agreement Profitability, Project Profitability, or Time Entry cost, then let us know and we'll remove the fields for you.

This permission is made at the database level, so if using SQL Server Management Studio (SSMS):

find your database for ConnectWise on the left

Right click and go to properties

Go to permissions

Select the user created for BrightGauge and scroll down and Grant the Execute permission. If you see 2 Execute permissions, grant it for the current schema (dbo is the default schema).

This is a bit complicated, so if you need guidance, here's a video:

Step 4 - Configure the Datasource Settings

Refer to the screenshot below and match the corresponding numbered fields:

1. Connection Name

Agent

2. The external IP address for the BrightGauge Agent on your network

3. Port Opened on our firewall

Database

4. Internal IP Address for the Database or Machine Name (if using multiple SQL instances, use the IP or machine name followed by a back slash and then the instance name: 192.168.1.100\instance or SQLServer\instance)

5. Database name (Ex. "cwwebapp_brightgauge")

6. This field is for advanced SQL setups not using the default SQL port (1433). Enter the non-default port in this field. Leave BLANK if using the default SQL port (1433).

7. Username you created in the database

8. Password you created for that username

Deep Linking (This section is optional. More information can be found here.)

9. Your ConnectWise Manage URL

10. Your ConnectWise Manage Locale

11. Your ConnectWise Manage company name (Used to log into ConnectWise Manage)

Step 5 - Test Connection

Once you've filled out all of the fields, make sure to test the connection. If successful, save the connection.

Please note, a common issue we have seen clients experience is with the "AutoFill" feature in web browsers inputting the wrong the credentials for the database user field. When a test connection is successful, the page is refreshed and the autofill takes effect automatically without the user realizing. The user then selects save, accidentally saving the autofilled credentials which are incorrect. A way to prevent this issue is to open a private browsing window (or incognito window) to fill out the datasource configuration page, or simply retyping the credentials after the test connection and clicking "Save".

Once you’ve saved the connection then the datasets and gauges will be added to your account.