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.
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.
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.
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
ALTER DATABASE [SCCM_PBI_Reporting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--Deletes the database from SQL Server
Drop database [SCCM_PBI_Reporting]