Part 1 – Building a SCCM Query
Part 2 – Fleshing out a machine information SQL Query
Part 3 – Adding SCCM SQL Queries to PowerBi
In this write up I’m going to take the query that I created in THIS POST and drop that into PowerBi so you can show your data in a more professional capacity.
Depending on your PowerBi setup there may be different ways you can get the data in, however for this example I’m going to use a Direct Query. Not exactly pretty but getting the data in is not really what I’ve been concerned about in this document, its been more around gathering the data you need and building up the reporting skills in SQL for SCCM this section is just to add a little ‘sparkle’ to it.
So I’m going to first load PowerBi and Sign In. Next I’m going to click on Get Data from the tool bar, and select SQL DB.
I’m going to input my server name & click on Advanced options…
Input the DB and in the SQL Statement section, I’m going to drop the SQL query we created.
I’m going to use my credentials I’m already logged in with, however you can edit here:
You should be faced with a preview of what the data looks like.. Like so…
Click on OK
Now you have loaded your data in, you are now ready to start manipulating and showing your data as you see fit.
Let’s start by adding on a pie chart for the split in x86/x64 architecture. Under “Visualisations” I select the Pie Chart option
Under Fields… I select “Architecture”
I will also drag the Architecture section over to the “Values” section of the Pie Chart… Like so…
You should notice that as soon as I do this, the Pie Chart changes…
Now I want to edit the title of the Pie Chart, so I click here…
And edit the title (I also chose to center align it) and I’ll repeat with Manufacturer and PowerShell version, now I have three sections on my PowerBI report. I have also added a text box title (don’t think I need to explain that in detail) Now I’m looking at this…
I hope by now you’re getting excited at the endless possibilities of representing your data here… This is pretty good.
On the bottom of my report I’m going to add a table, and select all the columns, I also delve into the settings and turn off the “Total” columns so now I have the following…
You can edit the order of the columns by dragging and dropping the sections around in here…
Now that I have this table, you should notice that you can click on sections in the three pie charts and the table will update, if for example I select PowerShell v5, the table changes to all devices with PowerShell version 5. This is fantastic for drilling down through the data. So now lets add some drop down menu selections to get more granular, say OS, make, model and domain.
I’ll now add those as “Slicers”…. This can be selected here:
I will sort the size, select it to be a ‘Dropdown” and position where I need it to be. Once the slicer is selected I simply change the field value to what I want it to be. Now I have the following:
In this particular example I’ve selected a particular domain and also Windows 10 Enterprise from the Slicer drop downs, this has edited both the table and the pie charts on the report.
Really excited about the way in which this data is represented now! I will leave you to explore the other options available to you for displaying your own data.
Sure, there are better ways to do this, Direct Queries for example – not necessarily the best way but used on this write up for ease. This is also ‘out-of-the-box’ stuff there may be add-ons or configurations I haven’t explored myself yet to make this really great, and of course there is a SCCM Specific Dash Board available HERE which might save you some of the time & effort but I always feel it’s best to have some grasp of what you’re using. I have really enjoyed even building a basic Dash Board like in the example above so I can fully understand why the dashboard was developed to such a high standard, the data in invaluable.
I hope you have enjoyed reading this post & that I have started you on your way to representing your data & drawing up custom dashboards for your own needs.
Thanks for reading,
Part 1 – Building a SCCM Query