Update Compliance query

Question

Hi, we're trying to create a Power BI report using update compliance report that will only show the latest security update (excluding previews) installation status for all computers.

Has anyone ever tried to create such query?

Thks in advance and don't hesitate if you have any questions

Answer 1

Hi @Stéphane Lalancette ，

The following is an approximate SQL query template for the status of the latest update installation.

Select v_R_System.name0 , CASE
WHEN (ucs.Status = '3') OR (ucs.Status = '1') THEN 'Compliant'
WHEN (ucs.LastEnforcementMessageID = '9') OR (ucs.LastEnforcementMessageID = '10') THEN 'Pending Reboot'
WHEN (ucs.LastEnforcementMessageID = '3') OR (ucs.LastEnforcementMessageID = '8') OR (ucs.LastEnforcementMessageID = '12') OR (ucs.LastEnforcementMessageID = '13') THEN 'Pending Install'
WHEN (ucs.Status = '0') OR (ucs.LastEnforcementMessageID IS NULL) THEN 'Compliance state unknown'
WHEN ucs.Status = '2' THEN 'Non-Compliant'
else 'UNKNOWN'
END AS 'PatchStatus',Title,ucs.CI_ID,ucs.Status,ucs.LastEnforcementMessageID,*

from v_R_System
left join v_Update_ComplianceStatus ucs on v_R_System.ResourceID = ucs.ResourceID
left join V_UpdateInfo ui on ucs.CI_ID = ui.CI_ID
left JOIN v_BundledConfigurationItems bci on ui.CI_ID = bci.BundledCI_ID

Please note the key: ucs.Status, ucs.LastEnforcementMessageID.
Here is the article about explanation of 'LastEnforcementMessage ID ='
https://systemcenter.in/2014/09/22/sccm-sql-query-computers-with-pending-restart-or-other-update-enforcement-states/
Note: This is non-official Microsoft article just for your reference.

About the way how to create a Power BI report, we could refer to Power BI engineer. Hope it help.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.