Skip to content

MECM Administrator Dashboard

Jump to bottom
Matthew Novitsch edited this page Jun 9, 2021 · 18 revisions

Update 6/9/2021
Minor updates to the template to add the Windows 10 versions.

Summary:
This dashboard will present the following information:

  • Software Updates status via Collections
  • Client Health
  • Installed Server roles across your environment
  • Installed MECM Server Roles in your MECM Environment
  • OS Version and build numbers
  • Collection dashboard (credit for this Matt Balzan)
  • System Security
  • Bitlocker Status
  • Deployed Configuration Baseline and Configuration Items
  • Default Hardware Inventory classes and Custom Hardware Inventory classes
  • List of Maintenance Tasks and schedules
  • Application Summary
  • Software Inventory
  • Software Usage
  • Hardware Inventory

This is meant to be used for MECM Administrator overview along with some details for the management.

Prerequisites:

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 MECM Administrator Dashboard.sql file in SQL Server Management Studio or your preferred SQL Editor. You will need to change the FILENAME path to your desired location. Select Edit > Find and Replace > Quick Replace. In the Find... box put in CM_NOV which is my MECM lab database. In the Replace... box put in your MECM Database name, this should be CM_ plus your site code. Click the Replace All button the right of the tab(there should be 58 items replaced). 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 MECM Administrator Dashboard.sql on the CAS/Primary database server. This script creates a database called MECM_PBI_Reporting and then creates the tables and stored procedure needed to run the MECM Administrator 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 PowerBI installed. Open MECM Administrator Dashboard.pbit, the first thing that you will see is a window asking for your server, database, and Production MECM, 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 MECM_PBI_Reporting). The last is your MECM Production system should be CM_???.


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 12 times (one for each query).

You should be able to see the dashboards if all the changes were made correctly. Your pages for “all patching”, “workstation patching”, “server patching”, and “other” should look something like this.


Your “ClientHealth” page should look something like this.


Your “Installed Server Roles” page should look something like this.


Your “Installed MECM Roles” page should look like this.


Your “OSVerions” page should look like this.


Your “Collections” page should look like this.


Your next three pages are 3 collections pages with should look like this one. First one is collections under 10 seconds, the next is between 10 and 20 seconds, and the last one is for collections running longer than 20 seconds.


Your "SystemSecurity" page displays the following information:

  • UEFI
  • Secure Boot
  • TPM
  • Virtualization-Based Security (VBS)
  • Bitlocker
  • Credguard Status


Your "Bitlocker" page displays the current status of your bitlocker for workstations in your environment.


Your "Baseline & Items" page displays the current status of your configuration baselines and configuration items deployed to your collections in your environment.


Your "HWInventoryEnabled" page displays the current status of your hardware inventory and any customizations that you have deployed to your collections in your environment.


Your "MaintenanceTasks" page displays the current status of your maintenance tasks in your environment.


Your "AppSummary" page displays the current status of your Applications deployments in your environment.


Your "SWInventory" page displays the current software inventory in your environment, this could be useful for determining what licensing you need or will need.


Your "SWUsage" page displays the current software used in your environment, this could be useful for determining software used .


Your "HWInventory" page displays the current hardware inventory in your environment, this could be useful for determining tech refreshes needed or will need in the near future.


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 MECM Administrator Dashboard.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 MECM PBI_Reporting database
/*
--Sets database to single user mode so it drops all other connections
USE [master]
GO
ALTER DATABASE [MECM_PBI_Reporting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

--Deletes the database from SQL Server
Drop database [MECM_PBI_Reporting]
*/

MECM Administrator Install Video
MECM Administrator Training Video 

Offline Instructions

Clone this wiki locally