Part 2: Using Configuration Manager Dashboard for Software Update Deployment Readiness (Patch Tuesday Checklist)

In my previous post I talked about some of the critical checks performed for Software Update Deployment readiness as part of routine ConfigMgr administrator task prior to every 2nd Tuesday of month to ensure ConfigMgr hierarchy is ready for deploying any critical security updates to all systems.

Now with the recent release of Configuration Manager dashboard, I thought to automate  this readiness checks by creating a single view to review the overall status for Configuration Manager component status which are critical for Software Update Deployment readiness.

Here is sneak view for the dashboard threshold, Scorecard, and queries I used to create this Configuration Manager dashboard:

SQL Queries I used to create above dashboard.

Last 24 hrs. Client & MP Communication Status (%)

declare @olddate datetime
set @olddate=DATEADD(HOUR,-24, getdate())
SELECT  sub.[Site]
       ,SUM(CASE sub.HealthState
                 WHEN 1 THEN sub.Cnt
            END) AS 'Client successfully communicating with MP'
       ,SUM(CASE sub.HealthState
                 WHEN 2 THEN sub.Cnt
            END) AS 'Client failing to communicate with MP'
       ,SUM(sub.Cnt) AS 'Total'
       ,ROUND((CAST(SUM(CASE sub.HealthState
                      WHEN 1 THEN sub.Cnt
                 END) AS float) / SUM(sub.Cnt)) * 100,2) AS 'Success Percentage'
  FROM (
        SELECT  sit.SiteCode AS 'Site'
               ,chs.HealthState
               ,COUNT(chs.HealthState) AS 'Cnt'
          FROM v_Site sit
               INNER JOIN v_ClientHealthState chs
                  ON sit.sitecode = chs.assignedsitecode
                 AND chs.HealthType = '1000'
                 AND chs.lasthealthreportdate > @olddate
                 AND sit.[Type] = 2
         GROUP BY  sit.SiteCode
                  ,chs.HealthState
        ) sub
GROUP BY sub.[Site]
ORDER BY sub.[Site]

Last 24 hrs. Client & SUP Scan Status (%)

declare @olddate datetime
set @olddate=DATEADD(HOUR,-24, getutcdate())
declare @dn1 table (clients numeric, SiteCode varchar(3),statename varchar(20))
declare @dn2 table (SiteCode varchar(3), totals numeric)
declare @dn3 table (SiteCode varchar(3), pclients numeric, tstatename varchar(50))
insert into @dn1(clients,SiteCode,statename)
select count(*)as clients,site.sms_assigned_sites0 as SiteCode,statename
from v_updateScanStatus upp
  join v_statenames stat on stat.stateid = upp.lastscanstate
join v_RA_System_SMSAssignedSites site on site.resourceid = upp.resourceid
and stat.topictype ='501' and upp.lastscanpackagelocation like'http%' where statename in ('Scan Completed ','Scan Failed ')
group by upp.lastscanstate,stat.statename,site.sms_assigned_sites0
order by site.sms_assigned_sites0,clients desc
insert into @dn2(SiteCode,totals)
select SiteCode as SiteCode, SUM(clients) from @dn1 group by SiteCode
insert into @dn3(SiteCode,pclients,tstatename)
select t1.SiteCode as SiteCode,(clients/totals*100),statename from @dn1 as t1, @dn2 as t2 where t1.SiteCode = t2.SiteCode
select * from @dn3 PIVOT (sum(pclients) for [tstatename] in ([Scan completed],[Scan failed]) ) as A

Last WSUS server sync with Windows Update

select SiteCode, ContentVersion, SyncTime from dbo.update_syncstatus order by SyncTime

Site Servers Not Communicated in Last 24 hrs.

SELECT Distinct SiteCode, Role
    , (SELECT TOP 1 TimeReported) AS LastReportedTime
    , getdate() AS CurrentTime
FROM Summarizer_SiteSystem NOLOCK
where TimeReported < DATEADD(HOUR,-24, getutcdate())

Software Update Dependent Component Status

Please change the text in RED below with specific site code and we have custom database and table created for mapping all error code with error descriptions so that is not included in the below query.

SELECT MessageID
    ,sm.Severity
    ,MachineName
    ,COUNT(*) as 'Count'
    ,MAX(Time) as 'LastOccurred' 
    ,Component         
FROM v_StatusMessage sm WITH (NOLOCK)
WHERE ModuleName = 'SMS Server'
    AND Sm.Severity != 1073741824
    and Component in
    ('SMS_OBJECT_REPLICATION_MANAGER',
    'SMS_WSUS_Configuration_Manager',
    'SMS_WSUS_Control_Manager',
    'SMS_WSUS_Sync_Manager',
    'SMS_Despooler',
    'SMS_Distribution_Manager',
    'SMS_Executive',
    'SMS_SQL_Monitor',
    'SMS_State_System')
    AND Time > DATEADD(hour, -24, GetDate())
    AND SiteCode in ('[SiteCode1]','[SiteCode2]','[SiteCode3]')           
GROUP BY MessageID, MachineName, Component, sm.Severity
ORDER BY 4 desc

Distribution Points with less than 10 GB free disk space

Please change the text in RED below with specific drive for DP package location or remove it if it’s not standard on all DPs

select distinct RoleName, ServerName, SR.SiteCode, (SELECT TOP 1 SS.TimeReported)AS LastReportedTime, BytesTotal/1048576 as TotalDriveSpaceinGB, BytesFree/1048576 as TotalFreeDriveSpaceinGB, percentfree from SysResList SR
Left join Summarizer_SiteSystem SS on SS.SiteSystem = SR.NALPath
where BytesFree < 5242880 and
RoleName = 'SMS Distribution Point' and
SS.SiteObject like '%F$\' and
TimeReported > DATEADD(HOUR,-24, getdate())

Last 24 hrs SUP Scan Client Errors

We have custom database and table created for mapping all error code with error descriptions so that it is not included in the below query as shown in the above dashboard for error description.

select Top 10 LastErrorCode,
COUNT(*) as ClientCount from v_updateScanStatus up
join v_r_system sys on sys.resourceid = up.resourceid
where lastscantime >DATEADD(hour,-24, getutcdate())
and lastscantime < getutcdate()
and LastErrorCode != 0
group by LastErrorCode
order by count (*) desc

Please share your comments and thoughts for this dashboard dataset or any queries for using the Configuration Manager Dashboard.

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified in the Terms of Use