Custom Software Update Compliance Report for ConfigMgr 2007

Had a special request recently to create a custom compliance report based on the selection of a software update list and collection name. Besides the columns shown below, other fields added to the report were security patch results, URL to the technical bulletin and collection id.

We named it ‘Compliance 10 – Update list (per collection)’

It turns out that it was not a trivial undertaking, as it required a sub-query to get the results we needed. The results are shown here:

image

Base report query, with prompts:

– final report SQL compliance report 10

declare @AuthListLocalID as int

select @AuthListLocalID=CI_ID from v_AuthListInfo where CI_UniqueID=@AuthListID

select CI_UniqueID as AuthorizationListID,

Title as AuthorizationListName

from v_AuthListInfo where CI_UniqueID=@AuthListID

select

m.Name0 as ComputerName0,

ui.ArticleID as ArticleID,

ui.BulletinID as BulletinID,

ui.Title as Title,

– ucs.Status,

case ucs.Status

when 1 then ‘NotRequired’

when 3 then ‘Installed’

else ‘Unknown’

END as Status,

ui.InfoURL,

@CollID as CollectionID

– ui.CI_UniqueID

from v_Update_ComplianceStatusAll ucs

join v_UpdateInfo ui on ucs.CI_ID=ui.CI_ID

join v_ClientCollectionMembers cm on cm.ResourceID=ucs.ResourceID

join v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1

left join v_RA_System_SMSAssignedSites asite on m.ResourceID=asite.ResourceID

where ui.CI_UniqueID IN

(

select UniqueUpdateID=ui.CI_UniqueID

from v_CIRelation cir

join v_UpdateInfo ui on cir.ToCIID = ui.CI_ID

left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID=@CollID

join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID=@CollID

where cir.FromCIID=@AuthListLocalID and cir.RelationType=1

)

and cm.CollectionID=@CollID

order by m.Name0

– End SQL query for primary report

Prompts:

clip_image002

clip_image004

begin

if (@__filterwildcard = ”)

select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo order by Title

else

select distinct CI_UniqueID as AuthListID, Title as Title from v_AuthListInfo

where ((CI_UniqueID like @__filterwildcard) or

(Title like @__filterwildcard))

order by Title

end

clip_image006

begin

if (@__filterwildcard = ”)

select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name

else

select CollectionID as CollectionID, Name as CollectionName from v_Collection

WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard

order by Name

end

email

Written by , Posted .
  • Author

    I could not get this to work.. Numerous errors trying to save the prompts and the main report itself..