CW Manage: Getting Started with PowerBI

Quinntin Comer Uncategorized 5 Comments

So…one article I have promised to post over and over is a “Getting Started” article on ConnectWise Manage and Power BI integration. So, here it goes…this will probably be rough just to get it up and then cleaned up later.

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.
  • Base64Encode – This website is useful for generating your authorization token instead of using Basic authentication.

Requirements

  • PowerBI (obviously) – If you are going to programatically refresh data or schedule data to refresh – when using an API/JSon datasource you need to configure an “on-premise data gateway”. Power BI will reference the gateway as a source which will reference the web source.
  • CW Manage API Member – https://comertechnology.com/getting-started-with-cw-manage-rest-api/
  • Base64 Conversion of the Public and Private key (Recommended, not required)
  • ConnectWise Client ID – You must sign up on the developer website and have a “Client ID”. https://developer.connectwise.com/ClientID

Process

  1. Convert your Basic authentication to an authorization token.
    1. Go to http://base64encode.org > Type in “CompanyID+PublicKey:PrivateKey” and encode it. This is your authorization token.
  2. Create a new Dashboard in Power BI
    1. Press Get Data > Select “Blank Query” (I will provide you a query below).
      1. In this example, we will use two queries. The API has a maximum return of 1,000 records. To get around this we will have an initial query to loop through all the pages until there are no more and a second to use that as our source.
      2. Right Click > on the new query.
        1. Select “Rename” > Name: PageData
        2. Select “Advanced Editor” and paste the following code. You will need to modify the following sections:
          1. <BOARDIDHERE> – If you want to specify a board, if not you can remove “?conditions=Board/id=x”
          2. <AUTHORIZATIONHERE> – This is the Authorization token you generated by encoding your CompanyID+PublicKey:PrivateKey to Base64
          3. <CLIENTIDHERE> – This is the Client ID you have generated at the CW Developer site.

            let
            Source = (PageNumber as number) => let
            Source = Json.Document(Web.Contents("https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets?conditions=board/id=<BOARDIDHERE>&pagesize=1000&page=" & Text.From(PageNumber), [Headers=[Authorization="Basic <AUTHORIZATIONHERE>",clientID="<CLIENTIDHERE>", ContentType="JSon"]]))
            in
            Source
            in
            Source

        3. Right click in the “Queries” section > Click New Query > Blank Query.
          1. Right click on our new query and rename it as you wish.
          2. Right click > Advanced Editor. This is where will specify our data from the PageData source. You can find the code at the bottom under “AllData”.

      3. In this case, I have already converted the results to a table and expanded several columns. You can modify these to your choosing or start from scratch.
      4. Press Close & Apply.
      5. The data will refresh and you are ready to create your visualizations.

AllData Code

let AllData = 
let Source = List.Generate( ()=> [Result= try PageData(1) otherwise null, PageNumber = 1], each List.Count([Result]) >1, each [Result=  try PageData([PageNumber]) otherwise null, PageNumber = [PageNumber] +1], each [Result]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1", {"id", "summary", "recordType", "board", "status", "company", "site", "addressLine1", "addressLine2", "city", "stateIdentifier", "zip","type","subType","priority","serviceLocation","contact","parentTicketId"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded Column3", "status", {"name"}, {"status"}),
#"Expanded Board" = Table.ExpandRecordColumn(#"Expanded status", "board", {"name"}, {"board"}),
#"Expanded type" = Table.ExpandRecordColumn(#"Expanded Board","type",{"name"}, {"type"}),
#"Expanded subType" = Table.ExpandRecordColumn(#"Expanded type","subType",{"name"}, {"subType"}),
#"Expanded company" = Table.ExpandRecordColumn(#"Expanded subType","company",{"name"}, {"company"}), #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded company","priority",{"name"}, {"priority"}),
#"Expanded location" = Table.ExpandRecordColumn(#"Expanded priority","serviceLocation",{"name"}, {"serviceLocation"}), #"Expanded contact" = Table.ExpandRecordColumn(#"Expanded location","contact",{"name"}, {"contact"}) 
in
#"Expanded contact"
in
AllData

Examples

We can create a basic table showing all “New” tickets with the below. Now, we have a number of statuses that are duplicates (i.e., New and New*). In our case, anything with an asterisk has a notification step. So, you can use a new column with “Fuzzy Matching” to concatenate the two statuses.

  1. Select the “Table” visual.
  2. In “Filters on this visual” > Drag “Status” over. Select the status you want to filter on. In our case, New*.
  3. Under Visualizations > Values (after selecting the table) > “Add data fields here” > Drag the Fields you would like to be displayed. Or, just check them.
    1. Voila! You have a basic table containing the columns you selected and filtering on the “New” status.

Comments 5

  1. Pingback: CW Manage: Getting Started with PowerBI (The Update!!) - Comer Technology Group

  2. I can’t seem to get it to authenticate. It asks me for credentials and I choose Anonymous, thinking it’ll use the creds built into the URL but it says it can’t authenticate with the credentials provided. No issues with my clientID and non-encoded basic auth info in Postman. Does this syntax look right?

    let Source = (PageNumber as number) => let Source = Json.Document(Web.Contents(“https://api-na.myconnectwise.net/v4_6_release/apis/3.0/service/tickets”,[RelativePath=”?pagesize=1000&Page=”&Number.ToText(PageNumber),Headers=[Authorization=”Basic DFKJSDFASDF4982398r8uDOIUFODF”,clientID=”534rw34ecde-sdfsd-sdfs-sdf-sdfsdfsd24″, ContentType=”JSon”]])) in Source in Source

    1. Nathan,

      You probably shouldn’t include your auth token in this post!

      However, it looks like the base64 encode of your auth token is missing at least the “=” at the end. You may have also converted the case to mostly caps which seems strange. Could be correct, but might want to re-encode your “companyid+privkey:pubkey” again using base64

  3. Hello, I have been trying to connect Power BI and CW Manage for dashboard reporting. I tried to follow this article but am still getting errors. Any help would be much appreciated.

    1. Post
      Author

Leave a Reply

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