SQL query on Configuration Manager 2012 to get a list of required updates

There is and never was a nice report to get a list of required updates per computer in SCCM.

I created a SQL query directly on the SQLserver and wanted to share this with you.
This allows me to import this information in our CMDB so we have a nice view about the required patches for a specific server.

 

SELECT     dbo.v_R_System.Name0 AS 'Computername', dbo.v_UpdateInfo.Title AS 'Updatename', dbo.v_StateNames.StateName, 
           dbo.v_Update_ComplianceStatusAll.LastStatusCheckTime, dbo.v_UpdateInfo.DateLastModified, dbo.v_UpdateInfo.IsDeployed, dbo.v_UpdateInfo.IsSuperseded, 
           dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.DateRevised
FROM       dbo.v_StateNames INNER JOIN
           dbo.v_Update_ComplianceStatusAll INNER JOIN
           dbo.v_R_System ON dbo.v_R_System.ResourceID = dbo.v_Update_ComplianceStatusAll.ResourceID INNER JOIN
           dbo.v_UpdateInfo ON dbo.v_UpdateInfo.CI_ID = dbo.v_Update_ComplianceStatusAll.CI_ID ON 
           dbo.v_StateNames.StateID = dbo.v_Update_ComplianceStatusAll.Status
WHERE     (dbo.v_StateNames.TopicType = 500) AND (dbo.v_StateNames.StateName = 'Update is required') AND (dbo.v_R_System.Name0 IN
           (SELECT     TOP (100) PERCENT SD.Name0 AS 'Machine Name'
            FROM          dbo.v_R_System AS SD INNER JOIN
            dbo.v_FullCollectionMembership AS FCM ON SD.ResourceID = FCM.ResourceID INNER JOIN
            dbo.v_Collection AS COL ON FCM.CollectionID = COL.CollectionID LEFT OUTER JOIN
            dbo.v_R_User AS USR ON SD.User_Name0 = USR.User_Name0 INNER JOIN
            dbo.v_GS_PC_BIOS AS PCB ON SD.ResourceID = PCB.ResourceID INNER JOIN
            dbo.v_GS_COMPUTER_SYSTEM AS CS ON SD.ResourceID = CS.ResourceID INNER JOIN
            dbo.v_RA_System_SMSAssignedSites AS SAS ON SD.ResourceID = SAS.ResourceID
            WHERE      (COL.Name LIKE 'All Server%to patch%')))

this will result in this (click to zoom)