Jamf Pro
Power BI is an incredible data visualization tool that allows you to connect to various data sources, transform and clean data, and create beautiful and interactive reports and dashboards. One such data source is the Jamf Pro data source, which is a mobile device management solution for Apple devices. In this article, we will explore the Power Query M language code for connecting to the Jamf Pro data source from inside Power BI.
What is Power Query M Language Code?
Power Query M Language Code is a language used to create custom data transformations and data connectors in Power BI. It is a functional programming language that allows you to transform and reshape data from various sources. With Power Query M Language Code, you can create complex data transformations that would be difficult or impossible to do with traditional data manipulation tools.
What is Jamf Pro?
Jamf Pro is a mobile device management (MDM) solution for Apple devices. It allows organizations to manage and secure their Apple devices, deploy and manage apps, and enforce security policies. Jamf Pro also provides reporting and analytics capabilities, allowing organizations to get insights into their device usage and security.
Connecting to the Jamf Pro Data Source from Inside Power BI
To connect to the Jamf Pro data source from inside Power BI, you will need to use the Power Query M Language Code. Here are the steps:
1. Open Power BI Desktop.
2. Click on the “Get Data” button on the Home tab.
3. In the “Get Data” window, select “Blank Query” under the “Other” category.
4. In the formula bar, enter the following code:
let
Source = Json.Document(Web.Contents(“https://your.jamfpro.server.com/JSSResource/mobiledevices”, [Headers=[Authorization=”Basic yourbase64encodedusernamepassword”]]))
in
Source
5. Replace “https://your.jamfpro.server.com” with the URL of your Jamf Pro server.
6. Replace “yourbase64encodedusernamepassword” with your base64-encoded username and password. To encode your username and password, you can use an online tool such as https://www.base64encode.org/.
7. Press Enter to execute the query.
This code uses the Power Query M Language Code to make a request to the Jamf Pro API and retrieve data about mobile devices. It uses the Json.Document function to parse the JSON response and return a table of data.
Transforming the Jamf Pro Data
Once you have connected to the Jamf Pro data source and retrieved the data, you may need to transform it before you can use it in your reports and dashboards. Here are some examples of transformations that you may need to do:
Rename Columns
To rename columns in the table, you can use the Table.RenameColumns function. Here is an example:
let
Source = Json.Document(Web.Contents(“https://your.jamfpro.server.com/JSSResource/mobiledevices”, [Headers=[Authorization=”Basic yourbase64encodedusernamepassword”]])),
#”Converted to Table” = Record.ToTable(Source),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Name”, “Device Name”}, {“SerialNumber”, “Serial Number”}})
in
#”Renamed Columns”
This code renames the “Name” and “SerialNumber” columns to “Device Name” and “Serial Number”, respectively.
Filter Rows
To filter rows in the table, you can use the Table.SelectRows function. Here is an example:
let
Source = Json.Document(Web.Contents(“https://your.jamfpro.server.com/JSSResource/mobiledevices”, [Headers=[Authorization=”Basic yourbase64encodedusernamepassword”]])),
#”Converted to Table” = Record.ToTable(Source),
#”Filtered Rows” = Table.SelectRows(#”Converted to Table”, each [#”Device Name”] = “John’s iPhone”)
in
#”Filtered Rows”
This code filters the table to only include rows where the “Device Name” column is “John’s iPhone”.
Expand Nested Tables
The Jamf Pro data source includes nested tables, such as the “Location” table that contains information about the device’s location. To expand these nested tables, you can use the Table.ExpandRecordColumn function. Here is an example:
let
Source = Json.Document(Web.Contents(“https://your.jamfpro.server.com/JSSResource/mobiledevices”, [Headers=[Authorization=”Basic yourbase64encodedusernamepassword”]])),
#”Converted to Table” = Record.ToTable(Source),
#”Expanded Location” = Table.ExpandRecordColumn(#”Converted to Table”, “Location”, {“username”, “realname”, “email_address”, “position”, “phone_number”, “building”, “room”, “department”}, {“Location.username”, “Location.realname”, “Location.email_address”, “Location.position”, “Location.phone_number”, “Location.building”, “Location.room”, “Location.department”})
in
#”Expanded Location”
This code expands the “Location” table to include columns for each of the nested fields.
Conclusion
Connecting to the Jamf Pro data source from inside Power BI is easy with the Power Query M Language Code. With the ability to transform and reshape data from various sources, you can get valuable insights into your device usage and security. Using the examples provided in this article, you can start creating custom data transformations and reports that meet your organization’s specific needs.
