Part 3 – Getting Paginated Data from Jamf Pro
In my previous posts, we covered how to set up Power BI and Jamf Pro to get ready to pull data and how to start pulling data into Power BI using API calls. In this post, I am going to go over how to utilize the Jamf Pro API (JPAPI) to get data. Since the JPAPI uses pagination, we will need to work around page limits to get all of our data into Power BI.
Not Datasets
Before we dive in, I want to make a correction to something I’ve been saying. In my last post I discussed something I knew as datasets in Power BI. Several people reached out to let me know that Microsoft no longer uses the term dataset. In classic Microsoft fashion, they changed the name from dataset to semantic model. The functionality is the same, just the name has changed.
Paginated Data
Thanks to some help from Harald Brouwers, I was able to quickly come up with a way to grab paginated data. Harald let me know about a post on Medium about grabbing paginated data into Power BI. To test the method, I decided to use the Mobile Device detail endpoint in the JPAPI. This endpoint allows us to gather different parts of a mobile device inventory record, like a list of applications installed on a device. Combining this endpoint with our method for getting a bearer token, I came up with the following Power Query:
let
BaseUrl = "https://stevewood.jamfcloud.com/api/v2/mobile-devices/detail?section=APPLICATIONS",
EntitiesPerPage = 10,
GetJson = (Url) =>
let Options = [Headers=[Authorization=fnGetToken()]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[#"totalCount"]
in Count,
GetPage = (Index) =>
let PageN = "page=" & Text.From(Index),
PageS = "page-size=" & Text.From(EntitiesPerPage),
Url = BaseUrl & "&" & PageS & "&" & PageN,
Json = GetJson(Url),
Value = Json[#"results"]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
I used a Blank Query in Power BI to enter this Power Query code. Just like the previous queries we discussed in Part 2, we will be presented with a table representation of the data being returned. In the case of this query, I was presented with a single column of data that was, in fact, a table of data for each entry. Using the icon at the top of our column, I was able to expand this out and select the data that was important to me. In my case, I chose ‘mobileDeviceId’ and Applications.
Once I clicked on OK, I was presented with a table that showed the Applications column was a table of values. When I clicked to expand this table, I was asked if I wanted to expand to New Rows or expand to Values. In this instance, you want to choose New Rows. This will expand each application that is installed on a device and create a new row for each one.
Notice that for each entry in the ‘applications’ column, the entry in the ‘mobileDeviceId’ column may get repeated. Each item in the ‘applications’ column represents an application installed on the device with the ID that is in the ‘mobileDeviceId’ column. We’re going to want to expand the ‘applications’ column so that we can see the data about each of those applications. I am going to choose a few columns to expand: name, identifier, version, short version, and management status.
This gives us some pretty good information about the applications installed on our mobile devices. Since we pulled in that Mobile Device ID information, we will be able to link this application data with more general device information, like the hardware information, device name, and more, to generate rich reports about our fleet.
Grab More Data
Just having the list of applications is not enough. We will want to have other data imported on our devices, like the general information that will show things like the device name and OS version or build or the hardware information that will give us a device’s serial number. Pulling this data into separate tables within our semantic model will allow us to tie the data together. I am going to go ahead and create a new query to pull the General inventory information for my mobile devices into a new table.
Next Post
Now that we’ve tackled how to pull data in from the Classic API and how to pull data in from the Jamf Pro API, in our next post, we are going to discuss how to place this data, this semantic model, into our workspace in Power BI in the cloud. Once we have uploaded or published our semantic model, we will be able to connect to it to generate our visualizations. So until next time, stay frosty.