a starting point; stolen and slightly tweaked from http://www.madisonusergroup.com/2012/11/28/octobernovember-2012-meeting-notes/
You might want to tweak it more; remove the 0 (unknown) and only keep the 2 (missing), and/or use v_r_system_valid instead of just v_r_system. ----------- --author: John Nelson -- get update info DECLARE @Updates TABLE ( CI_ID INT, BulletinID VARCHAR(64), ArticleID VARCHAR(64), Title VARCHAR(512), Description VARCHAR(3000), InfoURL VARCHAR(512), Severity INT, IsSuperseded INT, IsExpired INT, DatePosted Date, DateRevised Date ); INSERT INTO @Updates SELECT CI_ID, BulletinID, ArticleID, Title, Description, InfoURL, Severity, IsSuperseded, IsExpired, DatePosted, DateRevised FROM dbo.v_UpdateInfo ui -- get filesizes for update files DECLARE @UpdateFiles TABLE ( CI_ID INT, Size BIGINT ); INSERT INTO @UpdateFiles SELECT uc.CI_ID, MAX(FileSize) Size FROM dbo.CI_ContentFiles cf JOIN dbo.v_UpdateContents uc ON cf.Content_ID = uc.Content_ID GROUP BY uc.CI_ID ORDER BY CI_ID SELECT s.Netbios_Name0, upd.BulletinID, upd.ArticleID, upd.dateposted, upd.daterevised, ucs.Status, --CASE WHEN ucs.Status IN(1,3) THEN 'GreenCheck' ELSE 'RedX' END AS StatusImage, sn.StateName, uf.Size as UpdateSize, upd.Severity, upd.IsSuperseded, upd.Title, upd.Description, upd.InfoURL FROM @Updates upd JOIN @UpdateFiles uf ON upd.CI_ID = uf.CI_ID JOIN dbo.v_Update_ComplianceStatusAll ucs ON upd.CI_ID = ucs.CI_ID -- AND ucs.ResourceID = @ResourceID JOIN dbo.v_StateNames sn ON ucs.Status = sn.StateID AND sn.TopicType = 500 AND sn.StateID IN(0,2)--,3) --unknown (0), required(2), installed(3) AND upd.Severity > 0 AND upd.IsExpired = 0 join dbo.v_R_System s on s.ResourceID=ucs.resourceid ORDER BY s.Netbios_Name0, upd.DatePosted ---------------- Sherry Kissinger Microsoft MVP - ConfigMgr [email protected] ________________________________ From: Jason Wallace <[email protected]> To: "[email protected]" <[email protected]> Sent: Wednesday, February 19, 2014 6:13 AM Subject: [mssms] Systems Requiring Updates Hello folks I have a requirement to know which updates are missing from which devices. is there a report which is easy enough to plagiarise for this purpose? Sent from Windows Mail
