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.