It works good but one change to the query : To get unique software Updates,you may have to remove uc.ContentCI_ID . More about contentCI_ID via https://technet.microsoft.com/en-us/library/dd334594.aspx. the SQL query which i have is 'list software updates which are downloaded but not part of any Software update groups'. --which is slightly different from what Stephen provided. here it is if someone interested: --List software updates that are downloaded but not in any software update groupsselect ui.Title, ui.ArticleID, ui.BulletinID, case when ui.IsSuperseded=0 then 'No' else 'Yes' end as [IsSuperseded], case when ui.IsExpired=0 then 'No' else 'Yes' end as [IsExpired] FROM v_updateinfo uiINNER JOIN V_UpdateContents uc ON uc.CI_ID=ui.CI_IDWHERE ui.CI_ID NOT IN (SELECT upd.CI_ID from vSMS_CIRelation as crINNER JOIN fn_ListUpdateCIs(1033) upd ON (upd.CI_ID = cr.ToCIID AND cr.RelationType = 1)INNER JOIN V_UpdateContents CC ON cc.CI_ID=upd.CI_IDINNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIIDwhere CC.ContentProvisioned='1')AND uc.ContentProvisioned='1'AND (ui.CIType_ID=1 OR ui.CIType_ID=8)GROUP BY ui.Title, ui.ArticleID, ui.BulletinID,ui.IsSuperseded,ui.IsExpiredorder by 2
RegardsEswar Koneti www.eskonr.com From: [email protected] To: [email protected] Subject: RE: [mssms] RE: Software Updates Report Date: Mon, 16 Mar 2015 16:06:37 -0500 My bad... The original query I sent needs some modification as one of the join conditions is wrong and the where clause needs to be updated. Change the join condition for fn_rbac_UpdateContents and we need to ensure that no software update groups show up in the report so add to the where clause ui.IsUserDefined = 0. Let me know how this works out: So... --Updates downloaded but not in SUG 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_IDWHEREui.IsUserDefined = 0 AND ali.Title IS NULLAND uc.ContentProvisioned = 1GROUP 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_IDORDER BY ui.DatePosted ASC --All downloaded updates ( take out the "AND ali.Title IS NULL" in the where clause) 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_IDWHEREui.IsUserDefined = 0 AND uc.ContentProvisioned = 1GROUP 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_IDORDER BY ui.DatePosted ASC From: [email protected] To: [email protected] Subject: RE: [mssms] RE: Software Updates Report Date: Mon, 16 Mar 2015 17:37:19 +0000 This is odd though, if I edit the where clause to this WHERE --ali.CI_ID IS NULL AND uc.ContentProvisioned IS NOT NULL I'd expect to see all downloaded updates but that's not the case. I only see 32 updates. I have over 900 downloaded. From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Leuthold Sent: Monday, March 16, 2015 10:26 AM To: [email protected] Subject: RE: [mssms] RE: Software Updates Report I think this will work for you or at least get you started: 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.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.ContentCI_ID = ui.CI_ID WHERE ali.CI_ID IS NULL AND uc.ContentProvisioned = 1 GROUP BY ali.Title, ui.CI_ID, ui.CI_UniqueID, ui.Title, ui.ArticleID, ui.BulletinID, ui.DatePosted, ui.DateRevised, uc.ContentProvisioned ORDER BY ui.DatePosted ASC From: [email protected] To: [email protected] Subject: [mssms] RE: Software Updates Report Date: Mon, 16 Mar 2015 14:22:13 +0000 That doesn't meet my requirement. There's no search criteria for "member of SUG" From: [email protected] [mailto:[email protected]] On Behalf Of Jason Lang Sent: Monday, March 16, 2015 9:20 AM To: [email protected] Subject: [mssms] RE: Software Updates Report You can use “add criteria” on the “All software Updates” node to select: Downloaded �CYes Deployed �C No You may want to add “superseded” �C No and “expired” �C No to filter out superseded and expired updates, which commonly fit this criteria as well. You could also save as a “saved search” if you need to. Jason Lang From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Monday, March 16, 2015 9:56 AM To: SMS List ([email protected]) Subject: [mssms] Software Updates Report Anyone have a report, query or anything that will show me all software updates that are downloaded but are not in a deployment group? John Marcum MCITP, MCTS, MCSA Desktop Architect Bradley Arant Boult Cummings LLP Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
