Patch Compliance Calendar

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; }