The Geeky Gordo

Part 2: Pulling Data from Jamf Pro

In my last post, I walked you through creating an API Client in Jamf Pro and then using that information to configure Power BI to connect to Jamf Pro. In this post, we will configure Power BI to grab some information from Jamf Pro using API calls. But before I do that, I want to address a question I’m sure I’ll get: Why don’t we just use the Power BI connector that Jamf has?

Jamf Power BI Connector

It is true that Jamf created a Power BI connector that is available on the marketplace. Once added to Power BI, you can create a very simple connection to your Jamf Pro instance to pull in the following information about Computers and Mobile Devices:

• Details
• Applications
• Extension Attributes
• Groups

There is even a set of three YouTube videos walking you through setting this up and creating your first dashboards: Module 1, Module 2, and Module 3. Module 3 even contains information on uploading your PBIX file (the completed dataset and dashboard) to Power BI online and setting up an automatic refresh of the dataset. We will reference this in our series.

There are two reasons why I am doing this manually and not with the connector. Firstly, the connector only uses basic authentication, a username, and a password. This type of authentication has been deprecated by Jamf and will eventually stop working. Not to mention the fact that it is a less secure method. Second, you are limited to the data that Jamf has chosen to make part of the connector. This means that if there is data that you want outside of the connector, you have to use the API to get it. A good example is Advanced Searches. These do not get pulled in by the connector, and the only way to do so is via the API.

Datasets

I am no data scientist/analyst/whatever, and I have very limited experience with Power BI. In my former job, I was able to hack together several dashboards for upper management, and I am sure there were better ways to accomplish the tasks. I learned from my bosses boss, who was a data nerd, some tricks to making Power BI data reusable for others. This was accomplished by creating datasets.

A dataset, in my terminology, is simply the method for pulling data in from various sources without creating visualizations. It’s very much like a database where you store the data and then reference it from other systems. In our case, we will create a dataset that pulls in data from Jamf Pro, and then we will create a new PBIX file to visualize the data. So let’s create our dataset.

Advanced Search

The first data we’re going to add to our dataset will come from an Advanced Search. A common method for keeping Jamf Pro license usage down is to un-manage computers; we may want to keep track of this. Unmanaged computers will not show up in Smart Groups, so we have to use an Advanced Search for this. If you do not have a search already, create one named “Unmanaged Computers” with a single criteria: “Managed” IS “Unmanaged”.

Once you have saved the search, grab the ID of the search. This is available in the URL of the Advanced Search:

https://<yourserver>.jamfcloud.com/advancedComputerSearches.html?id=23

In my case, this search is ID 23. We will now use that with the following API call, replacing <id> with the ID number we just captured (23 in my case).

https://<yourserver>.jamfcloud.com/JSSResource/advancedcomputersearches/id/<id>

You can use the swagger docs on your Jamf Pro server to test this code out to make sure you’re pulling in information. Your swagger docs are at:

https://yourserver.jamfcloud.com/api

In Power BI, go ahead and click on Get Data and choose Blank Query again. We are going to use a query to pull the Advanced Search into Power BI and then change the data. The data comes into Power BI as a table, like a database. Some data will come in as a table inside of a table. We’ll use Power BI’s transform features to change the way the data is stored. Click on the Advanced Editor item in the toolbar to open the Power Query editor. Delete the information that is in the editor and replace it with the following, replacing <yourserver> with your Jamf Pro server address and <id> with the ID of the Advanced Search.

let
    Source = Xml.Tables(Web.Contents("https://<yourserver>.jamfcloud.com/JSSResource/advancedcomputersearches/id/<id>", [Headers=[Authorization=fnGetToken(paramClientID,paramClientSecret)]]))
in
    Source

You may get a warning about privacy. Click on the Continue button and you can then set the privacy for each query. This can be done by clicking the Ignore checkbox if you wish or setting the level for each. I am not a privacy expert or a Power BI expert. Make these changes with the advice of those who have more information and understanding of these settings.

Once you have changed the privacy settings, you should be presented with a window showing the data that has been returned by your query. You have the ability here to remove columns that you may not need. In the Advanced Search, you can probably remove the ID column, the name column, and any other column that will have no impact on your reporting data. Once you have selected the columns to delete, you can right-click and choose “Remove Columns”.

The main column we want is the “computers” column. This will show that the data in the column is a table. Next to the name of the column is an icon with two arrows pointing in opposite directions. This button will expand the table of data that comprises the “computers” column into the data we want. Click that button. You will receive a dialog that shows you what columns are inside of this “computers” table.

Now that we have expanded that in many cases you may find another table to expand. It really depends on the API endpoint that you are ingesting into Power BI. In this case, an Advanced Search, we have another table in the “computer” column. Go ahead and click on the expansion button to see what the columns are inside of this table.

Here is the data that we really care about. We can uncheck any data that we do not care about in our dataset. In this example, I have left all of the boxes checked. Once you click OK, you will be brought back to the query data and see that all of your computer information from the search is now visible. If you leave the “computer.size” column (which I think you should), you have an easy way to visualize the number of computers in this search.

Now that you have the data, you can edit the various column headers, if you wish, to make them easier to read and understand. You can also rename this query to match which Advanced Search this came from. Remember, that is to the right of the query window.

You can use this same method for gathering data from Smart Groups. Simply get the ID of the Smart Group and change the API URL to pull from the Smart Group endpoint:

https://<yourserver>.jamfcloud.com/JSSResource/computergroups/id/<id>

Up Next

We’ve laid the ground work for building our visualizations. We will get to those soon, but in my next post I want to show how to pull in data from the Jamf Pro API, which is a paginated data source. We’ll need to loop through the API call to pull all of the data in.

As always, if you have questions please leave a comment or reach out to me on the interwebs.