The Geeky Gordo

Part 1: Jamf Pro & Power BI Setup

Customers and prospects frequently inquire about Jamf Pro’s dashboard reporting capabilities, especially for executive-level presentations. Given the prevalence of Microsoft licensing in organizations, Power BI emerges as a popular choice for data analysis. However, integrating Jamf Pro with Power BI, particularly with evolving authorization methods, can pose a significant challenge in this process.

In the upcoming series of blog posts (the exact number is yet to be determined), I’ll guide you through the process of integrating Jamf Pro and Power BI. Topics will include implementing bearer token authentication through the new API Roles & API Clients, extracting data from Jamf Pro using the API, and creating foundational dashboard graphs in Power BI.

We will start our journey by creating our API Role and API Client in Jamf Pro and creating the necessary query in Power BI to generate our bearer token.

Jamf Pro API Role and API Client

As we embark on the journey of utilizing APIs to fetch data for Power BI, it’s crucial to generate a bearer token for authentication. To streamline this process, we’ll configure an API Role and API Client within Jamf Pro. While using a Jamf Pro user for authentication is an option, employing an API Client is preferred as it provides a more modern method of authentication.

Our first task will be to create an API Role with the necessary read permissions for the data we want to gather. For the posts in this series, we’ll focus on the fundamentals, granting read access to key components like Computers, Mobile Devices, Extension Attributes, both Smart and Static groups, PreStage Enrollments, and other essentials. The specific role privileges I’ve utilized in my example are outlined below. For additional details on the required privileges for the Jamf Pro API and Classic API, refer to the provided links.

Now that we have our Role created, we can flip over to the API Client tab in Jamf Pro and create the client. Provide a Display Name and choose the API Role we created for this client. Make sure to click the Enable Client button to enable the client. Once you have saved the API Client, click the “Generate client secret” button to generate the client credentials. Be sure to copy down the client ID and client secret because they will only be displayed once (you can rotate the client secret if you lose it).

Now that we have created our API Client, we want to test it to make sure that we are able to get a bearer token. The easiest way to do this is using a curl command to request a token using the Client ID and Client Secret from our API Client.

curl --location --silent --request POST 'https://yourserver.jamfcloud.com/api/oauth/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'client_id=<yourClientID>' \
--data-urlencode 'grant_type=client_credentials' \
--data-urlencode 'client_secret=<yourClientSecret>'

You should get back a JSON response with a token that looks similar to this:

{"access_token":"eyJhbGciOiJIUzI1NiJ9.eyJzdWIiOiI3ODFiZGFhOS05ZGUxLTQ2MjQtOGQwNS03NjZiYTQ5MDdlODciLCJhdWQiOiI3ODFiZGFhOS05ZGUxLTQ2MjQtOGQwNS03NjZiYTQ5MDdlODciLCJuYmYiOjE3MDQzODc4MDEsInRva2VuLXV1aWQiOiIyNWM4YTBkYi1hZWE3LTQyYjMtOWRiZS0yZjE5OWM3Y2UyOTciLCJzdWJqZWN0LXR5cGUiOiJSRUdJU1RFUkVEX0NMSUVOVF9JRCIsImF1dGhlbnRpY2F0aW9uLXR5cGUiOiJDTElFTlRfQ1JFREVOVElBTFMiLCJzY29wZSI6WyJhcGktcm9sZTo4Il0sImlzcyI6Imh0dHA6Ly9zdGV2ZXdvb2QuamFtZmNsb3VkLmNvbSIsImV4cCI6MTcwNDM4Nzg2MSwiaWF0IjoxNzA0Mzg3ODAxfQ.duVxnJwtZI3NnxRsnNQdWAKa4WkO16TcaMTWlzYwIPc","scope":"api-role:8","token_type":"Bearer","expires_in":59}

If you were able to successfully get a token, you can move on to setting up Power BI to use that API Client for generating API calls. Let’s jump over to Power BI and get started.

Power BI Connection to Jamf Pro

Now that we have our API client information validated, we will want to move over to Power BI. For the work we are going to be doing, you will need a Windows device or a Windows VM and the Power BI Desktop software installed. Since I am not a Windows guy anymore, I leave you this article on Microsoft’s website for how to get Power BI Desktop.

Now there may be a simpler way to accomplish what we are about to do. The process I used was born from reading this blog post on connecting using a service principal.

Once you have Power BI Desktop installed and running, you can click on “Get data” on the welcome screen or on the toolbar. In the Get Data window that opens, type “Blank” in the search box, choose “Blank Query” from the list, and click the Connect button.

You should have a Power Query window open with “Query1” listed on the left “Queries” list. We will ignore that for the moment while we create a couple of parameters to store our “client_id” and “client_secret” values. Click on the Manage Parameters toolbar icon title and choose “New Parameter” from the dropdown.

Go ahead and create two parameters: paramClientID and paramClientSecret. Set the type to Text and set the Current Value to their respective values. For example, put your actual client_id value in the “Current Value” box for “paramClientID.” Once you have created the two parameters, click “OK.” (there is a “New” link towards the top of the Manage Parameters window)

Next, we will want to re-arrange the order of items in the Queries list on the left so that our blank query is listed below the two parameters. Go ahead and drag the query below. After you’ve done that, go ahead and rename the query to “GetToken” so it is easy to identify. If you click on the query on the left side of the window, you will see the Name listed under Properties on the right side.

While you have the query highlighted, click on the Advanced Editor item in the Toolbar. This will open a Power Query window where we can enter our M language code. Replace all of the code in the window with the following. Be sure to edit the URL to point to your Jamf Pro server.

let

// built from parts of https://pbi-guy.com/2023/03/25/connect-with-power-bi-to-the-power-bi-rest-api-with-a-service-principal

url = "https://yourserver.jamfcloud.com/api/oauth/token",

client_id = Text.Combine({"client_id",paramClientID}, "="),
client_secret = Text.Combine({"client_secret",paramClientSecret}, "="),
grantType = Text.Combine({"grant_type", "client_credentials"}, "="),

headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
body  = Text.Combine({client_id, client_secret, grantType}, "&"),

tokenResponse = Json.Document(Web.Contents(url, [ Headers = headers, Content = Text.ToBinary(body) ])),

AccessToken = tokenResponse[access_token],
Bearer = Text.Combine({"Bearer", AccessToken}, " ")

in
Bearer

Hopefully, that code is fairly easy to understand. We’re pulling the two parameters we set up to fill in the “client_id” and “client_secret” variables before combining those into the body of our request. If you set the proper values in the two parameters above, when you click the Done button, you should see a token show up in the query window.

Now that we have our Query, we want to convert it into a function that can be used in other queries. This is pretty simple to do, just right-click on our GetToken query and choose “Create Function.” In the window that comes up, give that function a new name like fnGetToken so we can identify it.

Wrap Up

We now have the basics set up to generate bearer tokens for our API queries. This is just the groundwork for what comes next: actually querying Jamf Pro for data.

You can click the “Close and Apply” button in the toolbar to return to the main Power BI window. Make sure to save your Power BI work so you do not lose it.

In our next post, we will create an API query in Power BI to pull in some data to display.

Be sure to drop a comment or hit me up on Slack or X if you have questions.