Software Update Dashboard by Collections

___________________________________________________________________________________________________________________________

IMPORTANT ANNOUNCEMENT FOR OUR READERS!

AskPFEPlat is in the process of a transformation to the new Core Infrastructure and Security TechCommunity, and will be moving June 20, 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!

__________________________________________________________________________________________________________________________

 

This content has also been posted on the Core Infrastructure and Security TechCommunity @ https://techcommunity.microsoft.com/t5/Core-Infrastructure-and-Security/Software-Update-Dashboard-by-Collections/ba-p/615822

**Please visit the above URL for any future updates!

 

Hello everyone, Matt Novitsch (SCCM Premier Field Engineer) here to talk to you about a Power BI Dashboard that I created using SCCM data.

<#
Script Disclaimer. The sample scripts provided here are not supported under any Microsoft standard support program or service. All scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose.
#>

https://gallery.technet.microsoft.com/SCCM-Patching-Dashboard-d0eb8b21

Summary:

This dashboard will present software updates status for the Collection and provide with the systems that are in that collection. This is meant to be used for management overview along with some details for the system admin to track down problem machines.

How to install:

First thing we need to do is get the collection(s) that you are targeting with your software update groups. Open the SoftwareUpdateByCollectionScript.sql file in SQL Server Management Studio or your preferred SQL Editor. You will need to change the FILENAME path to your desired location. Change NOV000015 to the collection(s) of your workstations. Change NOV000016 to the collection(s) of your servers. Change SMSDM003 to the collection(s) of your Windows Defender and other updates. Please note if you want to add more than one collection for your workstations add a comma between the two collections. An example of multiple collections is below.

Example:

vFCM.CollectionID in(‘NOV000015′,’NOV000029′,’NOV000190’)

Originally, I had the query limited to just the Cumulative Updates, I have commented those out and allowed everything that is applied to the collection. If you wish to filter the dashboard to certain updates, I have left the code in place so you can uncomment them out and it will work. The lines you want to change for this are just after the comment “This is where you would add the title of the update(s) if you want them filtered”. If you chose to filter on the Cumulative updates just delete the /* and the */. If you want to filter on a list of updates, then you will need to copy the row with the like in it and paste it however many different updates you are looking to filter from. An example of multiple filters of software updates is below.

Example:

Vui.title like ‘%Cumulative Update for Windows%’ and

Vui.title like ‘%Adobe%’ and

Finally, you will need to run the SoftwareUpdateByCollectionScript.sql on the CAS/Primary database server. This script creates a database called SCCM_PBI_Reporting and then creates the tables and stored procedure needed to run the SoftwareUpdateByCollection Dashboard in Power BI so you will need admin rights to create those on the database.

Using the dashboard:

Once the script has ran successfully on, make sure you have Power BI Desktop x64 installed (Version: 2.67.5404.581 64-bit (March 2019)). Open SoftwareUpdatesByCollections.pbit, the first thing that you will see is the Welcome to Power BI Desktop. Please create an account or sign in if you already have one.

Once logged in a window will appear asking for your server and database, these are parameters for all the queries. The first parameter is your SQL Server of your CAS/Primary. The second parameter is your database of your CAS/Primary (Default is SCCM_PBI_Reporting).

Once you click load, you may be prompted with the message box stating the Native Database query needs approval to run. Click run to continue, if you do not click run the data will not load in the database. It should appear no more than 3 times (one for each query).

You should be able to see the dashboards if all the changes were made correctly. It should look something like this.

Finally, please remember to save the file to your desired location so you don’t have to make the changes to the template every time. Open your file with the PBI extension next time and click the refresh button, you will be good to go then.

How to uninstall:

Open the SoftwareUpdateByCollectionScript.sql file in SQL Server Management Studio or your preferred SQL Editor. Go to the bottom of the file. You will see about 10 lines that are commented out. Highlight the 10 lines and execute them. Below is what the code looks like.

–Uninstall SCCM PBI_Reporting database

/*

–Sets database to single user mode so it drops all other connections

USE [master]

GO

ALTER DATABASE [SCCM_PBI_Reporting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

–Deletes the database from SQL Server

Drop database [SCCM_PBI_Reporting]

*/