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]<mailto:[email protected]>
To: [email protected]<mailto:[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]>
[mailto:[email protected]] On Behalf Of Jason Lang
Sent: Monday, March 16, 2015 9:20 AM
To: [email protected]<mailto:[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]>
[mailto:[email protected]] On Behalf Of Marcum, John
Sent: Monday, March 16, 2015 9:56 AM
To: SMS List ([email protected]<mailto:[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
________________________________
[H_Logo]
________________________________
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.
