Category: SQL Queries

ConfigMgr query all devices software update install status

During a recent audit we need to get a list of all update and there install status on every device.

We use Configmgr for applying software update but I was not getting the information (well that I could find) using the out of the box reports.

I decided to write a SQL query to all devices, applicable updates and some device information.

To get the compliance status I used the Microsoft doc for the v_UpdateComplianceStatus SQL view.

SMS_UpdateComplianceStatus Class – Configuration Manager | Microsoft Docs

Below is the full SQL query 

select rsys.name0 AS 'Computername',
 os.Caption0 AS 'Operating System',
 os.BuildNumber0 AS 'Build Number',
 ui.ArticleID AS 'Update ID',
 ui.Title,
 ui.Description,
 --Set Install Status
 Case
 when ucs.Status='0' then 'Detection state unknown'
 When ucs.Status='1' then 'Update is not required'
 when ucs.Status='2' then 'Update is required'
 When ucs.Status='3' then 'Update is installed'
 End As 'Install Status',
 --Set client Activity
 Case
 When ch.ClientActiveStatus='1' then 'Active'
 When ch.ClientActiveStatus='0' then 'Inactive'
 End As 'Client Active Status',
 ui.InfoURL,
 ui.DatePosted
 from v_updateinfo ui
 join v_UpdateComplianceStatus ucs on ucs.ci_id=ui.ci_id
 join v_CICategories_All catall on catall.CI_ID=UCS.CI_ID
 join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='UpdateClassification'
 join v_R_System rsys on rsys.resourceid=ucs.resourceid
 join v_GS_OPERATING_SYSTEM os on os.ResourceID = ucs.ResourceID
 join v_CH_ClientSummary ch on ch.resourceID = ucs.ResourceID
 order by rsys.name0

This is what the output should look like.

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.