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:
Last 24 hrs. Client & MP Communication Status
>90% and <95%
Last 24 hrs. Client & SUP Scan Status
Last WSUS Server Sync with Windows Update
> 1 day
< 1 day
Software Update Dependent Component Status for last 24 hrs.
>1 and <5 error msgs
>5 error msgs
Distribution Points with less than 10 GB drive space
< 5 GB
>5GB and <10 GB
Last 24 hrs. SUP Scan Client Errors
>1000 Clients errors
<1000s Client errors
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.
I attended your MMS presentation and really loved your dashboards. Do you have more detailled how to steps to create those dashboards. I already have The acelerator installed and I am able to see the default ones. I just need the steps to add your SQL Queries.
These are fantastic, many thanks for sharing this information
Thanks Shitanshu, these are great. I've added a count of computer models chart to my dashboard and blogged the process here in case anyone needs help with the web part config:
Wouldn't it be great if you could create a ConfigMgr Dashboard that showed you your Patch compliance
Anyone know what kind of access the SCCM dashboard application pool account needs to the SCCM site server and database as this is where we are installing the Dashboard session database as well? I've only been able to get it to work when the account is in the local admin. Problem is, that isn't going to fly in our environment.
Could we get the query for the Software Update Dependent Component Error Details for Last 24 hrs query? There is one listed above that is similar, but it lists the message ID of the error and not the Generic Error Description as the image shows. Would love to put this on our dashboard.