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 –Yes
Deployed – No
You may want to add “superseded” – No and “expired” – 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.
