CW Automate: Getting Started with Power BI (UPDATE)

Quinntin Comer Uncategorized Leave a Comment

Updated Information

ConnectWise requires the Client ID to be sent in all API requests for Automate now. We have made modifications to the data source information and have updated this article to reflect the changes.

Useful Information/Links

  • Postman – Postman makes getting started with API and API Development a breeze. For a newbie, they make it very easy and there is a lot of documentation and guides out there surrounding Postman in use for testing and developing with numerous APIs.
  • ConnectWise Developer Network – This website provides access to the API library, database library and everything else you could desire for getting started with developing/automating/reporting from the ConnectWise suite. Best part — its FREE.
  • MSPGeek (Formerly LabTechGeek) Community – This community is a good resource for “all things ConnectWise” and is a fantastic resource for anyone new to the community.


  • PowerBI (obviously) – To one up the CW Manage getting started — the way its handled in this article will support Scheduled Refreshes no problem.
  • Automate User – We need to have an Automate user to authenticate with for getting a token on each refresh. Automate tokens expire hourly and Power BI doesn’t have a “built in” way of refreshing it. So, we request a token each time. One thing you will need to do is check “Integrator Login” on this user as our method does not support MFA.


  1. Create an Automate user. Give this user an extremely complex password. In a future release, Automate API will change using CW Portal so this process will clean up A LOT. This user needs to be an Integrator account to work around MFA requirement.
  1. Open Power BI. Right Click in Queries > New Query > Blank Query.
  2. You will need to use the following code. For right now we will be paying attention to the end of ” APIToken=#”Converted to Table”{0}[Value]“:
    A couple items to note:
    1. url: This will be the URL of your Automate server with a trailing /cwa
    2. body: This is what we are POSTing to the url to retrieve an Authorization token.
    3. source: this is the URL above with a trailing path to the API authentication URL.
    4. Basically, everything until line 7 is for a POST statement to get the API token and bring the response back into a table. Line 8 we are storing the token only as a variable.
  3. Now, each time we run a refresh we will have the latest Authorization token and we will not run into issues with it expiring.
  4. Now we need to build our actual query to bring data back in. Unfortunately, Power BI has to verify a URL prior to making a connection to it. If the URL contains conditions or filters, it will not be able to connect to a static page to pass the test. So, what we do is establish a Base URL for the connection, then add on values after.
    Here is the code:
    Source2 = Json.Document(Web.Contents(url,[RelativePath="api/v1/computers",Query=[conditions="status=offine",pageSize="1000"],Headers=[Authorization="Bearer "&APIToken,ContentType="application/json",ClientID="<CLIENTIDHERE>"]])) in Source2
    1. In this portion of the query we are creating a new source called “Source2“. We are using the url variable from earlier in this portion. We need to point PBI to a URL that is static and responds to online requests. It cannot contain any dynamic information.
    2. After the static URL, we are adding a Relative Path and then a query to the end of the Base URL + Relative Path. So, it looks like this: Base URL + Relative Path + Query.
    3. In our Headers, we are specifying an Authorization header item and its a Bearer token. Now, you will see we reference “&APIToken“. This is using the variable from the first half of our script. We are also now specifying the CLIENTID in the headers. You will need to have a client ID from to use here.
    4. We are then telling it to output it. You can copy the ENTIRE code below.
  5. Now, we will have the data in a single column. We need to convert this to a table.
    1. click on the column. Then, under Transform > click “To Table“. Accept the defaults.
    2. Now, click the “expand” icon on the top right of the column of the table. This will allow you to expand and select the columns you would like.

More will be updated to this article soon.

Full Code

The full code can be found here:

Leave a Reply

Your email address will not be published. Required fields are marked *