I received a request a few weeks ago to create a report that had one row for each server and then one column per month / year. In the column would be an asterisks if the server is compliant for all that patches for that month (meaning, all that months patches are installed).
Well, after a ton of work I came up with something that meets their requirements. However, the tables that the report creates takes around 90 minutes to complete for around 750 servers!! So, I would recommend scheduling a job to run at night.
-- *** Starting with [Your Company Name]_ServerPatchStatus *** -- Create a temp table to hold the ResourceID and machinename Create table #ResourceID (ResourceID int, name varchar(64)) Insert into #ResourceID (ResourceID, name) select resourceID, name from dbo.v_FullCollectionMembership where CollectionID = '[Enter the collectionID that you deploy patches to]' --select * from #ResourceID --drop table #ResourceID -- Create a table to dump the results of the query to drop table [Your Company Name]_ServerPatchStatus create table [Your Company Name]_ServerPatchStatus ( MachineName varchar(255), BulletinID varchar(64), ArticleID varchar(64), Title varchar(512), DatePosted datetime, DateRevised datetime, Targeted varchar(1), Installed varchar(1), IsRequired varchar(1), Notrequired varchar(1), LastBootUpTime datetime, SCCM_Last_Hardware_Inventory datetime, Last_WSUS_Scan datetime, WSUSscan_State varchar(255), ErrorStatusID int, ErrorCode int, HexErrorCode nvarchar(10), MaintenanceWindowName varchar(200), Description varchar(512), StartTime datetime, DurationMinutes int, MaintenanceWindowEnabled bit) Insert into [Your Company Name]_ServerPatchStatus ( MachineName, BulletinID, ArticleID, Title, DatePosted, DateRevised, Targeted, Installed, IsRequired, Notrequired, LastBootUpTime, SCCM_Last_Hardware_Inventory, Last_WSUS_Scan, WSUSscan_State, ErrorStatusID, ErrorCode, HexErrorCode, MaintenanceWindowName, Description, StartTime, DurationMinutes, MaintenanceWindowEnabled) select distinct #ResourceID.name as 'MachineName', ui.BulletinID as BulletinID, ui.ArticleID as ArticleID, ui.Title as Title, ui.dateposted, ui.daterevised, Targeted=(case when ctm.ResourceID is not null then '*' else '' end), Installed=(case when css.Status=3 then '*' else '' end), IsRequired=(case when css.Status=2 then '*' else '' end), NotRequired= (case when css.Status=1 then '*' else '' end), os.LastBootUpTime0 as 'LastBootUpTime', ws.LastHWScan as 'SCCM_Last_Hardware_Inventory', Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last_WSUS_Scan' , scan.StateName 'WSUSscan_State' , scan.ErrorStatusID , scan.ErrorCode , scan.HexErrorCode, maint.MaintenanceWindowName as 'MaintenanceWindowName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled' from v_Update_ComplianceStatusall css join #ResourceID on css.ResourceID = #ResourceID.ResourceID join v_UpdateInfo ui on ui.CI_ID=css.CI_ID join v_CIRelation cir on cir.ToCIID = ui.CI_ID join v_CICategories_All catall on catall.CI_ID=ui.CI_ID join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = #ResourceID.ResourceID left join ( select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID group by atc.CI_ID) cdl on cdl.CI_ID=css.CI_ID left join ( select fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence' when sw.RecurrenceType = 2 then 'Daily' when sw.RecurrenceType = 3 then 'Weekly' when sw.RecurrenceType = 4 then 'Monthly' else 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow' , col.name as 'CollectionName' , col.CollectionID from v_ServiceWindow sw join v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID join v_Collection col on sw.collectionid = col.collectionid where sw.Name <> 'NO RUN' ) Maint on maint.ResourceID = css.ResourceID left join v_GS_WORKSTATION_STATUS ws on css.ResourceID = ws.ResourceID left join v_UpdateScanStatus uss on css.ResourceID = uss.ResourceID left join v_GS_Operating_System os on css.ResourceID = os.resourceid left join ( select uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID, isnull(uss.LastErrorCode,0) as ErrorCode, dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode from v_UpdateScanStatus uss join v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1 join v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID join v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID join v_StateNames sn on sn.TopicType = 501 and sn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end) where 1= 1 and (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}') ) scan on css.ResourceID = scan.ResourceID where css.ResourceID = #ResourceID.ResourceID and (css.Status=2 or css.Status=3 or css.Status=1) and cir.FromCIID in ( select CI_ID from v_AuthListInfo where Title like '%[enter the name of the update list(s) that you are inquiring about]%' ) and cir.RelationType=1 drop table #ResourceID -- *** Done with [Your Company Name]_ServerPatchStatus *** -- *** Starting [Your Company Name]_ServerPatchComplianceCalender *** -- Create a tempory table to hold the distinct months from the [Your Company Name]_ServerPatchStatus table Create table #Month (ID int identity(1,1), Month int) Insert into #Month (Month) select distinct datepart(month,DatePosted) from [Your Company Name]_ServerPatchStatus order by datepart(month,DatePosted) -- Select * from #Month -- drop table #Month -- Create a tempory table to hold the distinct years from the [Your Company Name]_ServerPatchStatus table Create table #Year (ID int identity(1,1), Year int) Insert into #Year (Year) select distinct datepart(year,DatePosted) from [Your Company Name]_ServerPatchStatus order by datepart(year,DatePosted) -- Select * from #Year -- drop table #Year drop table [Your Company Name]_ServerPatchComplianceCalender -- This is the calender table that needs to be recreated each time this job runs create table [Your Company Name]_ServerPatchComplianceCalender (Machinename varchar(255), Last_WSUS_Scan datetime, WSUSscan_State varchar(255)) -- select * from [Your Company Name]_ServerPatchComplianceCalender -- drop table [Your Company Name]_ServerPatchComplianceCalender -- Create a tempory table to hold the distinct machines from the [Your Company Name]_ServerPatchStatus table Create table #Machines (ID int identity(1,1), MachineName varchar(255), Last_WSUS_Scan datetime, WSUSscan_State varchar(255)) Insert into #Machines (MachineName, Last_WSUS_Scan, WSUSscan_State) select distinct machinename, Last_WSUS_Scan, WSUSscan_State from [Your Company Name]_ServerPatchStatus order by machinename -- select * from #Machines -- drop table #Machines -- Create a variable to hold the minimum month. This is used so that the While loop knows when to end. Note, the table is in reverse chronological order DECLARE @MinMonth int SET @MinMonth = (Select MIN(ID) FROM #Month) -- Select @MinMonth -- Create a variable to iterate through the months DECLARE @IterMonth int SET @IterMonth = (SELECT MAX(ID) FROM #Month) -- Select @IterMonth -- Create a variable to hold the maximum year. This is used so that the While loop knows when to end. Note, the table is in reverse chronological order DECLARE @MinYear int SET @MinYear = (Select MIN(ID) FROM #Year) -- Select @MinYear -- Create a variable to iterate through the years DECLARE @IterYear int SET @IterYear = (SELECT MAX(ID) FROM #Year) -- Select @IterYear -- Create a variable to hold the maximum machine count. This is used so that the While loop knows when to end DECLARE @MaxMachines int SET @MaxMachines = (SELECT MAX(ID) FROM #Machines) -- select @MaxRownum -- Create a variable to iterate through the months DECLARE @IterMachines int SET @IterMachines = (SELECT MIN(ID) FROM #Machines) -- select @IterMachines -- Build the columns of the table WHILE @IterYear >= @MinYear BEGIN DECLARE @Year int set @Year = (Select YEAR from #Year where ID = @IterYear) -- Start a new loop with one year and one month at a time WHILE @IterMonth >= @MinMonth BEGIN DECLARE @Month int Set @Month = (Select Month from #Month where ID = @IterMonth) DECLARE @MonthYear varchar(20) Set @MonthYear = convert(varchar,@Month) + '_' + convert(varchar,@Year) SET @MonthYear = '[' + @MonthYear + ']' DECLARE @SQL VARCHAR(MAX) -- Build the [Your Company Name]_ServerPatchComplianceCalender by adding the current month and year as a column name IF @Year < DATEPART(YEAR,GETDATE()) or (@Year = DATEPART(year,Getdate()) and @Month = DATEPART(month,Getdate())) SET @SQL = 'ALTER TABLE [Your Company Name]_ServerPatchComplianceCalender ADD ' + @MonthYear + ' Varchar' -- Select @SQL EXEC(@SQL) Set @IterMonth = @IterMonth - 1 --select 'This is IterMonth - ' + convert(varchar,@IterMonth) end --select 'This is IterYear - ' + convert(varchar,@IterYear) SET @IterMonth = (SELECT MAX(ID) FROM #Month) Set @IterYear = @IterYear - 1 end SET @IterMonth = (SELECT MAX(ID) FROM #Month) SET @IterYear = (SELECT MAX(ID) FROM #Year) SET @SQL = '' -- Start the loop with one machine WHILE @IterMachines <= @MaxMachines BEGIN DECLARE @MachineName varchar(255) set @MachineName = (SELECT machinename FROM #Machines WHERE ID = @IterMachines) --select @MachineName DECLARE @Last_WSUS_Scan datetime set @Last_WSUS_Scan = (SELECT Last_WSUS_Scan from #Machines where ID = @IterMachines) --select @Last_WSUS_Scan DECLARE @WSUSscan_State varchar(255) set @WSUSscan_State = (SELECT WSUSscan_State from #Machines where ID = @IterMachines) --select @WSUSscan_State insert into [Your Company Name]_ServerPatchComplianceCalender (Machinename, Last_WSUS_Scan, WSUSscan_State) Values (@MachineName, @Last_WSUS_Scan, @WSUSscan_State) -- Start a new loop with the one machine and one year at a time WHILE @IterYear >= @MinYear BEGIN set @Year = (Select YEAR from #Year where ID = @IterYear) -- Start a new loop with the one machine,one year and one month at a time WHILE @IterMonth >= @MinMonth BEGIN Set @Month = (Select Month from #Month where ID = @IterMonth) Set @MonthYear = convert(varchar,@Month) + '_' + convert(varchar,@Year) SET @MonthYear = '[' + @MonthYear + ']' --Select @MonthYear -- If the machinename has at least one required patch for the given month and year then the column in the table will remain NULL IF @Year < DATEPART(YEAR,GETDATE()) or (@Year = DATEPART(year,Getdate()) and @Month = DATEPART(month,Getdate())) --SET @SQL = 'update [Your Company Name]_ServerPatchComplianceCalender set ' + @MonthYear + ' = ''*'' where Machinename = ''' -- + @MachineName + ''' and (' + cast(@year as varchar(4)) + ' < datepart(year,getdate()) OR ( ' + cast(@month as varchar(2)) + ' = datepart(month,getdate()) and ' + cast(@year as varchar(4)) + ' = datepart(year,getdate()))) and Machinename not in (select machinename from [Your Company Name]_ServerPatchStatus where datepart(month,DatePosted) = ' -- + CAST(@Month as varchar(2)) + ' and datepart(year,dateposted)= ' + CAST(@Year as varchar(4)) + ' and isrequired = ''*'' and machinename = ''' + @MachineName + ''' )' SET @SQL = 'update [Your Company Name]_ServerPatchComplianceCalender set ' + @MonthYear + ' = ''*'' where Machinename = ''' + @MachineName + ''' and Machinename not in (select machinename from [Your Company Name]_ServerPatchStatus where datepart(month,DatePosted) = ' + CAST(@Month as varchar(2)) + ' and datepart(year,dateposted)= ' + CAST(@Year as varchar(4)) + ' and isrequired = ''*'' and machinename = ''' + @MachineName + ''' )' -- Select @SQL EXEC(@SQL) Set @IterMonth = @IterMonth - 1 --select 'This is IterMonth - ' + convert(varchar,@IterMonth) end --select 'This is IterYear - ' + convert(varchar,@IterYear) SET @IterMonth = (SELECT MAX(ID) FROM #Month) Set @IterYear = @IterYear - 1 end SET @IterYear = (SELECT MAX(ID) FROM #Year) SET @IterMachines = @IterMachines + 1 END drop table #Year drop table #Month drop table #Machines -- *** done with [Your Company Name]_ServerPatchComplianceCalender ***
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }