Ben Whitmore ben@byteben.com 0 SQL Data Source=BB-SQL1 true Integrated a4f7bce3-f736-4b13-a3bb-25df802afa44 SQL Data Source=BB-SQL1;Initial Catalog=CM_BB1 true Integrated 5be2c861-82f8-4e55-b7e4-c49d5e8c2b33 DataSource2 =Parameters!COLLID.Value true =Parameters!StartDate.Value true =Parameters!StartDate30.Value true =Parameters!StartDate60.Value true =Parameters!StartDateOlder.Value true =Parameters!StartDate80.Value =Parameters!EndDate.Value true =Parameters!EndDate0.Value true =Parameters!EndDate31.Value true =Parameters!EndDate61.Value true =Parameters!EndDate81.Value true select CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID, FCM.collectionID, os.caption0 [OS], CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'Last Su Scan', IP.IPAddress AS [IP Address], Case when sum(case when bl.clientstate >=1 then 1 else 0 end) > 0 then '*' end as 'Restart Required', 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 INT))) else '0' end as 'Patch Status', case when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end)as INT))) else '0' end as '0 to 30', case when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end)as INT))) else '0' end as '31 to 60', case when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end)as INT))) else '0' end as '61 to 80', case when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end)as INT))) else '0' end as '81 plus', case when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end)as INT))) else '0' end as 'Critical', case when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end)as INT))) else '0' end as 'Security', case when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end)as INT))) else '0' end as 'Service Pack', case when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end)as INT))) else '0' end as 'Rollup' from v_UpdateComplianceStatus UCS left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.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_gs_workstation_status ws on ws.resourceid=CS.resourceid left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid left join BGB_LiveData bl on bl.resourceid=ucs.resourceid inner join v_UpdateInfo ui on ui.CI_ID=ucs.CI_ID left join v_GS_OPERATING_SYSTEM OS on os.ResourceID=ucs.ResourceID left join v_UpdateScanStatus USS on uss.ResourceID=ucs.ResourceID left JOIN (SELECT IP1.resourceid AS rsid2, IPAddress = substring ((SELECT (IP_Addresses0 + ', ') FROM v_RA_System_IPAddresses IP2 WHERE IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid ORDER BY resourceid FOR xml path('')), 1, 50000) FROM v_RA_System_IPAddresses IP1 GROUP BY resourceid) IP ON IP.rsid2 = ucs.resourceid Where FCM.collectionid = @COLLID and ui.IsExpired=0 and ui.IsSuperseded=0 and (catinfo2.CategoryInstanceID = '16777243' or catinfo2.CategoryInstanceID = '16777247' or catinfo2.CategoryInstanceID = '16777248' or catinfo2.CategoryInstanceID = '16777250') Group by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0 Order by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0 true UserName0 true OS true Last HW Scan true Last Su Scan true Last Su Scan All true IP Address true 81 plus true 61 to 80 true 31 to 60 true 0 to 30 true Last Reboot true collectionID true Name0 true Domain0 true ResourceID true Compliant ? true Patch Status true Restart Required true Critical true Security true Service Pack true Rollup true 4.73102in 1.98345in 1.5772in 1.40258in 1in 1in 1in 1in 0.05208in 0.87868in 0.84375in 0.80208in 0.83333in 0.35417in true true Textbox17 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true Textbox19 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true Textbox20 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true Textbox21 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true Update Classification Textbox27 CornflowerBlue Middle 2pt 2pt 2pt 2pt 4 true true Textbox22 DimGray Middle 2pt 2pt 2pt 2pt true true Patch Age (Days) Textbox23 CornflowerBlue Middle 2pt 2pt 2pt 2pt 4 0.72695in true =Fields!Name0.Value true Server Textbox14 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true Last Su Scan Textbox6 CornflowerBlue Middle 2pt 2pt 2pt 2pt true =Fields!Restart_Required.Value true Restart Required (may affect patch installation) Textbox8 CornflowerBlue Middle 2pt 2pt 2pt 2pt true =Fields!Patch_Status.Value true Patches Required Textbox7 CornflowerBlue Middle 2pt 2pt 2pt 2pt true true ="Critical" Textbox28 LightSteelBlue Middle 2pt 2pt 2pt 2pt true true ="Security" Textbox34 LightSteelBlue Middle 2pt 2pt 2pt 2pt true true ="ServicePack" Textbox31 LightSteelBlue Middle 2pt 2pt 2pt 2pt true true ="Rollup" Textbox25 LightSteelBlue Middle 2pt 2pt 2pt 2pt true true Textbox13 DimGray Middle 2pt 2pt 2pt 2pt true =Fields!D0to30daysold.Value true ="0 to 30" Textbox11 LimeGreen Middle 2pt 2pt 2pt 2pt true =Fields!D31to60daysold.Value true ="31 to 60" Textbox2 Orange Middle 2pt 2pt 2pt 2pt true =Fields!D61to80daysold.Value true ="61 to 80" Textbox2 Red Middle 2pt 2pt 2pt 2pt true =Fields!D81daysandolder.Value true ="81 plus" Textbox2 DarkViolet Middle 2pt 2pt 2pt 2pt 0.32195in true true =UCase(Fields!Name0.Value & "." & Fields!Domain0.Value) /ConfigMgr_BB1/Software Updates - A Compliance/Compliance 5 - Specific computer Name0 /ConfigMgr_SS1/Software Updates - A Compliance/Compliance 5 - Specific computer =Fields!ResourceID.Value =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =Fields!Last_Su_Scan.Value Last_Su_Scan =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =Fields!Restart_Required.Value Textbox10 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =Fields!Patch_Status.Value Textbox9 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!Critical.Value = 0, "-",Fields!Critical.Value) Textbox29 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!Security.Value = 0, "-",Fields!Security.Value) Textbox35 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!ServicePack.Value = 0, "-",Fields!ServicePack.Value) Textbox32 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!Rollup.Value = 0, "-",Fields!Rollup.Value) Textbox26 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true Textbox15 DimGray Middle 2pt 2pt 2pt 2pt true true =iif(Fields!D0to30daysold.Value = 0, "-",Fields!D0to30daysold.Value) Patch_Status =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!D31to60daysold.Value = 0, "-", Fields!D31to60daysold.Value) Textbox3 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!D61to80daysold.Value = 0, "-", Fields!D61to80daysold.Value) Textbox5 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt true true =iif(Fields!D81daysandolder.Value = 0, "-", Fields!D81daysandolder.Value) Textbox5 =iif(Rownumber(nothing) mod 2, “Transparent”, “Silver”) Middle 2pt 2pt 2pt 2pt After true After true true Detail Detail_Collection Output true true true true true DataSet1 =Fields!Name0.Value 1.20569in 0.25in 1.40307in 17.10417in Critical Updates, Security Updates, Service Packs and Update Rollups =Parameters!COLLID.Label textbox1 0.33416in 0.25in 0.81598in 17.10417in 1 1.15014in 0.25in 0in 17.10417in 2 2pt 4.0525in true true ="Report Run by: "& User!UserID 2pt 2pt 2pt 2pt true true ="Total Run time: "& CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " + CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)" 2pt 2pt 2pt 2pt true true ="Printed on " & Globals!ExecutionTime 2pt 2pt 2pt 2pt 8.5in 14in 0in 14in 0in 0in 0.25in 0.25in