Dear team,

Ca anyone help on this for Reports customization :

We want to get deployed updates report for multiple collections and data should be on one page. We try to club multiple collections in one report and see the possibilities. Please help if anyone is doing this kind of practice...Thanks.

Regards,

S

Plugin your collection Ids and modify accordingly...

-----------------------------------------------------------

SELECT

                rs.Netbios_name0 as 'PC Name',

                case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Security Updates Missing')

                else 'Good Client'

                end as 'Updates Status',

                rs.User_Name0 as 'Last Logged On User',

                asite.SMS_Assigned_Sites0 as 'SMS Site',

                rs.Client_Version0 as 'Client Version',

                ws.lasthwscan as 'Last Hardware Scan',

            uss.LastScanPackageLocation as 'Last Scan Location',

            uss.LastScanPackageVersion as 'Last Scan Pakage',

            st.StateName as 'Status',

            Convert(VarChar(10), OS.LastBootUpTime0, 101) 'Last Boot Date',

            DateDiff(D, OS.LastBootUpTime0, GetDate()) 'Last Boot (Days)'

 

from v_ClientCollectionMembers ccm

join v_R_System rs on rs.ResourceID = ccm.ResourceID

left outer join v_UpdateComplianceStatus  UCS on UCS.ResourceID = ccm.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'

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

left join v_gs_workstation_status ws on ws.resourceid=rs.resourceid

left join v_UpdateScanStatus uss on ws.resourceid = uss.ResourceID

left join v_StateNames st on st.TopicType = 501 and st.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rs.Client_Version0, '4.0'),

1)<'4') then 7 else isnull(uss.LastScanState, 0) end)

left join v_Gs_Operating_System OS on ws.resourceid = OS.ResourceID

 

where

ccm.CollectionID='XXXXXX'

and catinfo2.CategoryInstanceName like 'Security Updates'

 

Group By

 

rs.Netbios_name0,

rs.User_Name0 ,

asite.SMS_Assigned_Sites0 ,

rs.Client_Version0,

ws.lasthwscan,

uss.LastScanPackageLocation,

uss.LastScanPackageVersion ,

st.StateName ,

OS.LastBootUpTime0

Hi,

Are you talking about SCCM 2012?

Please refer to the link below:

Create Custom Reports for ConfigMgr 2012 with Report Builder

http://www.scconfigmgr.com/2014/01/24/create-custom-reports-for-configmgr-2012-with-report-builder/