CW Automate: Getting Started with Power BI

Quinntin Comer Uncategorized Leave a Comment

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.

Requirements

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

Process

  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.
  2. Open Power BI. Right Click in Queries > New Query > Blank Query.
  3. 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]“:
    let
    url = "https://company.hostedrmm.com/cwa/api/v1/apitoken", body = "{ ""username"": ""<USERNAMEHERE>"", ""password"": ""<PASSWORDHERE>""}", Parsed_JSON = Json.Document(body), BuildQueryString = Uri.BuildQueryString(Parsed_JSON), Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )), #"Converted to Table" = Record.ToTable(Source), APIToken=#"Converted to Table"{0}[Value], Source2 = Json.Document(Web.Contents("https://company.hostedrmm.com/cwa",[RelativePath="api/v1/computers",Query=[conditions="status=offine",pageSize="1000"],Headers=[Authorization="Bearer "&APIToken,ContentType="application/json"]])) in Source2
    A couple items to note:
    1. url: This will be the URL of your Automate server and go to the apitoken endpoint.
    2. body: This is what we are POSTing to the url to retrieve an Authorization token.
    3. 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.
  4. Now, each time we run a refresh we will have the latest Authorization token and we will not run into issues with it expiring.
  5. 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("https://company.hostedrmm.com/cwa",[RelativePath="api/v1/computers",Query=[conditions="status=offine",pageSize="1000"],Headers=[Authorization="Bearer "&APIToken,ContentType="application/json"]])) in Source2
    1. In this portion of the query we are creating a new source called “Source2“. We are defining a static URL (to pass the check): https://company.hostedrmm.com/cwa. This is a URL that is active and responds which is what Power BI is expecting.
    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.
    4. We are then telling it to output it. You can copy the ENTIRE code below.
  6. 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

Leave a Reply

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