SQL Query: All Software Updates Downloaded But Not in a Software Update Group – #SysCtr #ConfigMgr – Houston Area Systems Management User Group

UPDATE 3/16/2015 4:07 PM EST: I had the join clause wrong and needed to update the where clause to not show software update groups in the results.

John Marcum on the ConfigMgr myITForum mailing list today was asking if anyone had a query to show all software updates downloaded but do not exist in a software update group. I’ve had to write a ton of queries and reports on the software update schema in ConfigMgr so I thought I would give a stab at it. Here is the query:

DECLARE @UserSIDs nvarchar(255)='DISABLED',@locale nvarchar(8)='en-us'
DECLARE @lcid AS INT SET @lcid = dbo.fn_LShortNameToLCID(@locale)
 
SELECT 
            ali.Title [SUG],
            ui.IsUserCI,
            ui.IsUserDefined,
            ui.CI_ID,
            ui.CI_UniqueID, 
            ui.Title, 
            ui.ArticleID, 
            ui.BulletinID, 
            ui.DatePosted,
            ui.DateRevised,
            uc.ContentProvisioned [Downloaded]
FROM 
            fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui 
            LEFT JOIN fn_rbac_CIAssignmentToCI(@UserSIDs)  aci ON ui.CI_ID = aci.CI_ID
            LEFT JOIN fn_rbac_CIAssignment(@UserSIDs)  a ON aci.AssignmentID = a.AssignmentID
            LEFT JOIN v_CIAssignmentToGroup atg ON atg.AssignmentID = a.AssignmentID
            LEFT JOIN fn_AuthListInfo(@lcid) ali ON ali.CI_ID = atg.AssignedUpdateGroup
            LEFT JOIN fn_rbac_UpdateContents(@UserSIDs) uc ON uc.CI_ID = ui.CI_ID
WHERE
ui.IsUserDefined = 0 
AND ali.Title IS NULL
AND uc.ContentProvisioned = 1
GROUP BY
            ali.Title,
            ui.CI_ID, 
			ui.IsUserCI,
			ui.IsUserDefined,
            ui.CI_UniqueID, 
            ui.Title, 
            ui.ArticleID, 
            ui.BulletinID, 
            ui.DatePosted,
            ui.DateRevised,
            uc.ContentProvisioned,
			uc.ContentCI_ID
ORDER BY 
            ui.DatePosted 
ASC

SQL Query: All Software Updates Downloaded But Not in a Software Update Group – #SysCtr #ConfigMgr

Share this:

0 Comments on “SQL Query: All Software Updates Downloaded But Not in a Software Update Group – #SysCtr #ConfigMgr”