MECM check for all updates that are required but not deploy SQL Query

Recently we had an issues where some software updates where missed during regular patching cycle, I wanted to have an automated report to list all updates that where required on more than one system but no deployed.

First I checked the Microsoft SQL views for software updates

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager

The three tables I used where,

V_UpdateComplianceStatus: This table is used to the get status code for updates. Status of 2 means the update is required.

v_UpdateInfo: This table is used to get information on each update

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager#v_updateinfo

v_Update_ComplianceSummary: This table is used to get compliance of each update to see how many devices are missing the updates.

https://docs.microsoft.com/en-us/configmgr/develop/core/understand/sqlviews/software-updates-views-configuration-manager#v_update_compliancesummary

select distinct
UI.DatePosted as ‘Release Date’,
UI.articleid as ‘ArticalID’,
UI.Title,
Update_Required=(case when UCS.Status=2 then ‘Yes’ end),
CS.NumMissing,
Updates_Deployed=(case when UI.IsDeployed=0 then ‘NotDeployed’ end),
UI.InfoURL as InformationURL

from V_UpdateComplianceStatus UCS
join v_UpdateInfo UI on UI.CI_ID=UCS.CI_ID
join v_Update_ComplianceSummary CS on CS.CI_ID=UCS.CI_ID
WHERE UCS.Status=2 and UI.IsDeployed=0
order by NumMissing desc

Below is what the query return looks like in SQL Management Studio.missing1To confirm the query is working we can create a  search filter for all software updates to make sure they match.missing2Last step was to create a weekly email report using SQL Server Reporting Services (SSRS).

To create a new report (MECM reporting point role will need to be installed for this) go to Monitoring > Reporting > Reports.missing3I created a custom Reports folder to keep any report I created.

Right click on reports and click create reportmissing4

Give the report a name and a folder path the report will be stored. missing5Follow the report wizard to finish the report creation. missing6missing7Once the report wizard completed the report builder should load. missing8First create a new dataset. Right click on Datasets > Add Datasetsmissing9

Give the Dataset a name, Use a dataset embedded in my report, Data source and select the MECM data source and query type as text. Last part is to copy the query to the query field.missing10Once that click ok and the dataset should show under datasets. missing11

Next we create a table for the report view. missing12Select the dataset we create.missing13I just wanted to use all values so drag all row to the value box.missing14Next just go through the rest of the wizard. Once completed you should see something similar to the below. I  added a text box at the top for a tile and added in some grey filling on the table. missing16Now save the report and we can run from the MECM console to see what the report will look like. missing17Last step is to create subscription to send a the weekly report missing18Select report delivered by Email and put in the required detail and report to be included.(Email settings need to be configured in SSRS before you can select email as a deliver option)missing19Set the schedule that is required and complete the wizard. missing20The report should now be sent out weekly as a csv file.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s