A New Tool for your Toolbox: SCCM Collection Specific Report Template in PowerBI



AskPFEPlat is in the process of a transformation to the new Core Infrastructure and Security TechCommunity, and will be moving by the end of March 2019 to our new home at https://aka.ms/CISTechComm (hosted at https://techcommunity.microsoft.com). Please bear with us while we are still under construction!

We will continue bringing you the same great content, from the same great contributors, on our new platform. Until then, you can access our new content on either https://aka.ms/askpfeplat as you do today, or at our new site https://aka.ms/CISTechComm. Please feel free to update your bookmarks accordingly!

Why are we doing this? Simple really; we are looking to expand our team internally in order to provide you even more great content, as well as take on a more proactive role in the future with our readers (more to come on that later)! Since our team encompasses many more roles than Premier Field Engineers these days, we felt it was also time we reflected that initial expansion.

If you have never visited the TechCommunity site, it can be found at https://techcommunity.microsoft.com. On the TechCommunity site, you will find numerous technical communities across many topics, which include discussion areas, along with blog content.

NOTE: In addition to the AskPFEPlat-to-Core Infrastructure and Security transformation, Premier Field Engineers from all technology areas will be working together to expand the TechCommunity site even further, joining together in the technology agnostic Premier Field Engineering TechCommunity (along with Core Infrastructure and Security), which can be found at https://aka.ms/PFETechComm!

As always, thank you for continuing to read the Core Infrastructure and Security (AskPFEPlat) blog, and we look forward to providing you more great content well into the future!


Hello again everyone! Christopher Scott, Premier Field Engineer here. Recently I have started publishing some of my PowerBi report templates and received some positive feedback, so I decided to keep them going. This next template was designed to provide data insights into a filtered System Center Configuration Manager (SCCM) collection. Below is a detailed summary of each page as well as some important configurations and the download links for the template and PowerBI desktop.

These reports are scoped by collection so that they can be generated for individual organizations\collection owners that fall under a larger entity.

Important Configurations:

Data-Source Parameters:

  • If you are importing from the template file:
    • you will be prompted for the “SCCM_SQL_Instance”, “SCCM_DB”, “CollectionID” and “AV_Software”. Fill these fields with the appropriate SQL Information, SCCM Collection ID and Antivirus Software information for your environment and click ok.
  • If you are using the PBIX file:
    • Once you open the PowerBI file the first thing you will need to do is configure the data source by editing the parameters. You can do this simply by clicking the “Edit Queries” button ion the Home Toolbar and then select “Edit Parameters”

  • Replace the “SCCM_SQL_Instance”, “SCCM_DB”, “CollectionID” and “AV_Software” fields with the appropriate SQL Information, SCCM Collection ID and Antivirus Software information for your environment and click ok. Click ok to continue and Run to allow the Native queries to run and import the data.

Report Previews:

  • Page 1 is an Overview page for client imaging dates. The bar graph and corresponding table give a visual timeline of when clients were imaged over a period of time. The Date slider on the top left can be used to filter that period to a specific range. On the bottom right, I’ve added a last refresh timestamp to ensure the data is an accurate reflection of the environment.
  • This report page is generally used to determine when clients should be refreshed.

  • Page 2 shows the Update Compliance for the collection specified against each Software Update Group assigned to it. On this page starting from the top left I provide a count of systems in the collection and the number of devices reporting as non-compliant (In terms of Update Compliance only), Those numbers are broken down to provide a percentage calculation for quick insight. Also on the left hand side is a table of clients and the Antivurus versions (or Definition versions) installed on each client. Lastly on the left hand side is the number of machines reporting to SCCM as Unknown. These are machines that little or no inventory and usually have been powered off or disconnected for extended periods, but not limited to those situations.
  • Inside the Box 1 (The Top outlined box pane) I break down the overall Deployment status with a summarized compliancy table and pie chart. Box 2 (The bottom outlined box pane) provides detailed deployment status first summarzied in the pie chart and then broken out in the graph by individual deployment.
  • This has been a favorite for my customers as it provides a high level insight into the compliance of the desired collection.

I hope this report gives you the necessary data points to empower your organization. Please feel free to comment below with any feedback or ideas that you would like to see templated or improved.