Part 5 – SharePoint to Power BI
In Part 4 of this series, I showed you an alternative way to get data from Jamf Pro into a JSON file stored on SharePoint. We used this method as a workaround for the roadblock regarding scheduled updates on the Power BI site when using manual queries. We covered the manual query in parts one through 3. In this post we will be using the Power BI desktop application on Windows. So fire up your Windows laptop or Windows VM and let’s dig into it.
Getting Data Into Power BI
We will create a new Blank report in Power BI and utilize the SharePoint connector to ingest our JSON file from Part 4. The easiest way is to click the “Get data from other sources” button on the home page when you open Power BI.
Next, choose the SharePoint connector.
You will be asked to provide the SharePoint site URL we used in our previous post.
Once you click OK, you will be presented with a list of all documents stored at that SharePoint site. Since I created a site specifically for my Power BI reports and only have one search setup, I only see one JSON file. However, if you have other file types stored on this site or JSON files from other Jamf Pro searches, you will need to filter the files. The image below is an example of having several different file types or JSON files available. Click the Transform Data button to filter and choose the specific JSON file we want.
Clicking the Transform Data button will open a new Power Query Editor window showing a table of all files found on the site. We want to locate the Extension column in the table and click on the down arrow to the right of the title. You’ll want to change the filter at the bottom so that the ONLY extension chosen is our “.json” extension.
You should now have a filtered list of all files that end in .json. In my example, two .json files show up. Since I only want to pull in data from one of these JSON files, I will perform the following tasks on that file: Right-click on the word Binary in the Content column of the file you want to use and choose Drill Down.
This will drill into the data contained in that file. You should now see a list of records. Each record represents a computer inventory record from our Advanced Search. Right-click on the column title, List, and choose “To Table.” This will convert the list into a table format that we can expand.
Click OK on the pop-up dialog you receive, accepting the default values in the boxes.
Now that we have our data in a table to expand on, we will first rename some things. On the left side of the window, you should see a list of Queries. Right-click on the query we are working with (I have two shown and am working on Query2) and rename it to something descriptive. I am going to rename mine to “computers.” Once you have done that, right-click on the column name, “Column1,” in my screenshot, and rename that to something descriptive. I will rename mine to “inv,” short for inventory.
Now that we’ve cleaned that up, we need to expand the “inv” column so that the records stored inside are in individual columns. Go ahead and click on the icon to the right of the name that looks like a left-turn arrow and a right-turn arrow.
You should be presented with a list of the information columns stored within each record. Since we controlled the columns we wanted via the Advanced Search, go ahead and click the OK button to expand them.
You should now see data that looks like inventory data from Jamf Pro.
We’re going to do one more transformation before we close this window. For each of the columns that contain date-formated information, like Last Enrollment or Last Check-in, right-click on the column title, choose Change Type, and then change to the Date/Time type. Doing this will allow for date-based calculations in your visualizations.
Once you have done that for all of the date columns, click the Close & Apply button on the left side of the menu bar.
We’ve done it! We have now imported our data into Power BI so that we can begin to create our visualizations.
Power BI Visualizations
You should now have a blank canvas to work on, a list of visualizations on the right, and our data to the right of the visualizations. We will start with a fundamental visualization of data, a simple table showing our computer information. If you click on the disclosure triangle next to our data set, it will expand to show you the various columns we have to choose from. Placing a checkmark in the multiple columns you want to show in the table. This will create the table visualization for us.
Ta-da! We’ve created our very first visualization in Power BI. I know it’s basic, and it doesn’t provide us, as Jamf admins, anything we couldn’t get from within Jamf Pro. But it can provide this information to people we do not want to have access to Jamf Pro, like our bosses, our C-level folks, and others.
If you notice, our column names look pretty ugly, so we’ll want to change them. Click on the visualization to ensure it is selected, and then on the right side, under the Visualizations tab, you should see the columns listed. For each column you want to rename, click on the column and choose “Rename for this visual.”
For the columns that are date-related, where we changed the data type to Date/Time, you will need to change them from Date Hierarchy before you can rename them.
Once you have renamed all the columns, your visualization should look like the image below.
We’re done creating our first very basic visualization. There are a ton of things you can add to this report and plenty of other options, like slicers and filters, that I won’t get into. I would highly suggest doing a search on YouTube for “power bi” and running through some of the many videos on creating visualizations. Or, if your organization has a data analyst, ask them to help you build visualizations that provide the information your management is asking for.
Next Episode
In the next post in the series, I will show you how to create a Measure in Power BI to capture all of the devices by macOS version. One of the top questions asked by management is, “How many devices are running Sonoma?” A measure can help you answer that question.