Part 4: SQL queries for creating Configuration Manager Client Health and Problem Management Dashboard

Article
10/09/2010

In my previous posts for Configuration Manager Dashboard I talked about what to expect in next update for this series and here are awaiting SQL queries for “Configuration Manager Client Health & Problem Management Dashboard”. I want to acknowledge my colleague Benjamin Reynolds who helped in fine tuning these below queries.

Configuration Manager Client Health Dashboard

Client Count & Percentage with Hardware & Software Inventory Reported in last 7 days

DECLARE @olddate datetime
,@NullVal datetime
SET @olddate = DATEADD(day,-7, GETUTCDATE())

SELECT sites.SMS_Assigned_Sites0 AS AssignedSite
,TotalSys.Total AS TotalActiveClients
,SuccSys.Succ AS HWSuccess
,SuccSW.Succ AS SWSuccess
,CONVERT(decimal(5,2),(SuccSys.Succ*100.00/TotalSys.Total)) AS 'HW Percentage'
,CONVERT(decimal(5,2),(SuccSW.Succ*100.00/TotalSys.Total)) AS 'SW Percentage'
FROM v_RA_system_smsassignedsites sites
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_gs_workstation_status sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastHWScan > @olddate
GROUP BY sit.sms_assigned_sites0
) SuccSys
ON sites.SMS_Assigned_Sites0 = SuccSys.AssSite
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Succ
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_System sis
ON sit.ResourceID = sis.ResourceID
INNER JOIN v_GS_LastSoftwareScan sts
ON sis.ResourceID = sts.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
AND sts.LastScanDate > @olddate
GROUP BY sit.SMS_Assigned_Sites0
) SuccSW
ON SuccSW.AssSite = sites.SMS_Assigned_Sites0
INNER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS AssSite
,COUNT(DISTINCT sis.Netbios_Name0) AS Total
FROM v_RA_System_SMSAssignedSites sit
INNER JOIN v_R_system sis
ON sit.ResourceID = sis.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) TotalSys
ON sites.SMS_Assigned_Sites0 = TotalSys.Asssite
GROUP BY sites.SMS_Assigned_Sites0
,TotalSys.Total
,SuccSys.Succ
,SuccSW.Succ
ORDER BY 4 DESC
GO

Client Count & Percentage for WSUS Scan, Heartbeat and MP Communication in last 24 hrs.

DECLARE @olddate datetime
SET @olddate=DATEADD(hour,-24, GETUTCDATE())

SELECT tot.SiteCode
,tot.TotalClient
,ptc.ScanTotal [ScanSuccess]
,CONVERT(decimal(5,2),(ptc.ScanTotal*100.00/tot.TotalClient)) [ScanSuccessPerc]
,hrt.HBCount [Heartbeat]
,CONVERT(decimal(5,2),(hrt.HBCount*100.00/tot.TotalClient)) [HeartbeatPerc]
,mpc.MPComunicatonSuccess
,mpc.MPComunicatonFailure
,mpc.MPComunicatonTotal
,CONVERT(decimal(5,2),(mpc.MPComunicatonSuccess*100.00/tot.TotalClient)) MPComunicatonSuccessPerc
FROM (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) TotalClient
FROM v_R_System sis
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.ResourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
AND sis.Active0 = 1
GROUP BY sit.SMS_Assigned_Sites0
) tot
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 [SiteCode]
,COUNT(1) [ScanTotal]
FROM v_updateScanStatus upp
INNER JOIN v_statenames stn
ON upp.LastScanState = stn.StateID
AND stn.TopicType = '501'
AND stn.StateName = 'Scan completed'
INNER JOIN v_RA_System_SMSAssignedSites sit
ON upp.ResourceID = sit.ResourceID
AND upp.LastScanPackageLocation LIKE 'http%'
AND upp.LastScanTime > @olddate
GROUP BY upp.LastScanState
,sit.SMS_Assigned_Sites0
) ptc
ON tot.SiteCode = ptc.SiteCode
LEFT OUTER JOIN (
SELECT sit.SMS_Assigned_Sites0 AS [SiteCode]
,COUNT (sis.name0) AS [HBCount]
FROM v_R_System sis
INNER JOIN (
SELECT a.ResourceID, a.AgentSite, b.AgentTime
FROM v_AgentDiscoveries a
INNER JOIN (
SELECT ResourceID, MAX(AgentTime) AS AgentTime
FROM v_AgentDiscoveries
WHERE AgentName LIKE '%Heartbeat%'
AND AgentTime > @olddate
GROUP BY ResourceID
) b
ON a.ResourceID = b.ResourceID
AND a.AgentTime = b.AgentTime
) hrt
ON sis.ResourceId = hrt.ResourceID
INNER JOIN v_RA_System_SMSAssignedSites sit
ON sis.resourceID = sit.ResourceID
AND sis.Client0 = 1
AND sis.Obsolete0 = 0
GROUP BY sit.SMS_Assigned_Sites0
) hrt
ON tot.SiteCode = hrt.SiteCode
LEFT OUTER JOIN (
SELECT sub.[Site] AS [SiteCode]
,SUM(CASE sub.HealthState WHEN 1 THEN sub.Cnt END) AS [MPComunicatonSuccess]
,SUM(CASE sub.HealthState WHEN 2 THEN sub.Cnt END) AS [MPComunicatonFailure]
,SUM(sub.Cnt) AS [MPComunicatonTotal]
FROM (
SELECT sit.SiteCode [Site]
,chs.HealthState
,COUNT(chs.HealthState) [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]
) mpc
ON tot.SiteCode = mpc.SiteCode
GO

Configuration Manager Client Health Problem Management Dashboard

Windows Update Scan Error for Last 7 days

select Top 10 LastErrorCode,
CU.Description,CU.[Symbolic Name],
COUNT(*) as ClientCount
from v_updateScanStatus up
join v_r_system sys on sys.resourceid = up.resourceid
left join Custom_Message_descriptions CU on up.LastErrorCode=cu.errorcode
where lastscantime >DATEADD(day,-8, getutcdate())
and lastscantime < getutcdate()
and LastErrorCode != 0
group by LastErrorCode,CU.Description,CU.[Symbolic Name]
order by 4 desc

Note: Above SQL query uses custom table named “Custom_Message_descriptions” which we have created to decode the error codes and messages IDs for reporting. The data for this table can be found on following link: https://cid-80514c55d60387d4.skydrive.live.com/redir.aspx?resid=80514C55D60387D4!1522&authkey=NPqj7qLAh9Y%24 . And it is not recommended to create custom table in ConfigMgr database so please consult your SQL administrator for creating custom table & custom database

Client Health State Error Messages for Last 7 days

select Top 10 CH.ErrorCode,
CU.Description,CU.[Symbolic Name],
COUNT(*) as ClientClient
from v_ClientHealthState CH
left join Custom_Message_descriptions CU on CH.ErrorCode=cu.errorcode
where healthstate != 1
and LastHealthReportDate >DATEADD(day,-8, getdate())
Group by CH.ErrorCode,CU.Description,CU.[Symbolic Name]
order by 4 desc

Note: Above SQL query uses custom table named “Custom_Message_descriptions” which we have created to decode the error codes and messages IDs for reporting. The data for this table can be found on following link: https://cid-80514c55d60387d4.skydrive.live.com/redir.aspx?resid=80514C55D60387D4!1522&authkey=NPqj7qLAh9Y%24 And it is not recommended to create custom table in ConfigMgr database so please consult your SQL administrator for creating custom table & custom database

Client Deployment Error Messages for Last 7 days

select Top 10 StateDescription,
LastMessageStateID, count(*) 'ClientsCount'
from v_ClientDeploymentState
where LastMessageStateID not in ('400','700','100','500')
and DeploymentBeginTime >DATEADD(day,-8, getdate())
group by StateDescription, LastMessageStateID
order by 3 desc

Client Error Status Messages for last 7 days

SELECT top 10 MessageID
, sm.Severity
, COUNT(*) AS 'Count'
, MAX(Time) AS 'LastOccurred'
, Component
,cu.Description
FROM v_StatusMessage sm WITH (NOLOCK)
join Custom_Message_descriptions CU on sm.messageid=cu.errorcode
WHERE ModuleName = 'SMS Client'
AND sm.Severity != 1073741824
AND Time > DATEADD(DAY, -8, GetDate())
GROUP BY MessageID, MachineName, Component, sm.Severity, cu.description
ORDER BY 3 desc

Percentage of Inactive & Obsolete Client Count

select sub.AssignedSite,
sum(sub.cnt ) 'Total Count',
SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS 'Total Obsolete Clients'
,ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
,ROUND((CAST(SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Obsolete Client Count'
FROM
(select sit.sms_assigned_sites0 AssignedSite,
sys.active0,sys.obsolete0 ,
COUNT(*) cnt
from v_R_System sys
join v_RA_System_SMSAssignedSites sit on sys.resourceID=sit.resourceID
and (sys.Active0 is not null and sys.Obsolete0 is not null)
group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
)sub
group by sub.AssignedSite

Unapproved Client Count by Site

Select AssignedSite.SMS_Assigned_Sites0,
COUNT(*) as ClientCount
from ClientKeyData CD
join v_RA_System_SMSAssignedSites AssignedSite on AssignedSite.resourceID=CD.RecordID
where CD.ApprovalStatus <> 1
group by AssignedSite.SMS_Assigned_Sites0
order by 2 desc

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

Comments

Anonymous
October 10, 2010
I love the ConfigMgr Dashboard so this post from the Configuration Manager in Microsoft IT blog about
Anonymous
October 27, 2010
Hi. this is great, Your SQL for Client Count & Percentage for WSUS Scan, Heartbeat and MP Communication in last 24 hrs does not return any values for the MP sections for many of my sites, while a few it does. My environment is all SCCM SP1. Any ideas why? Suggestions?
Anonymous
October 27, 2010
@Mike, The only reason you won't get the MP data if you have not provisioned FSP role or clients are not installed with FSP server paramater during installation. if its true you still add FSP role and update the client registry for FSP server FQDN.
Anonymous
December 12, 2010
"And it is not recommended to create custom table in ConfigMgr database..." If that's the case, then why are you suggesting that customers do this? More specifically, WHY is it not recommended, and does it make the an SCCM installation unsupported? The very last thing my organisation wants is a scenario where we can't pick up the phone to log a support call with Microsoft because we've done something we shouldn't have. Please could you clarify... Thank you
Anonymous
December 15, 2010
how to backup the dataset config file(.xml) from the sharepoint server? thanks.
Anonymous
December 15, 2010
how to backup the dataset config file(.xml) from the sharepoint server? thanks.
Anonymous
February 15, 2011
I am trying to import "Client Health" query into my dashboard but getting "Syntax error near "GO"" error on verification. I am using SQL 2005 may it affect the query somehow? Thank you, Alex
Anonymous
August 07, 2011
@Ivan ""And it is not recommended to create custom table in ConfigMgr database..." If that's the case, then why are you suggesting that customers do this?" If you finished reading the sentence he actually suggested to create a new database to store this information rather than creating it in the same database as ConfigMgr. As to the why I don't know but probably something along the lines of what you were suggesting. It usually isnt reccomended to change a database schema from how it is originally setup. Any change you make to the database just means its different from the baseline product and could affect it in unpredictable ways.
Anonymous
June 18, 2014
Hi, create what you build here. If i try Client Count & Percentage with Hardware & Software Inventory Reported in last 7 days i see no data in my query, can you tell me why? with best regards. André
Anonymous
January 28, 2015
Hello Shitanshu, Regarding Custom_Message_descriptions, can you please walk me through the steps as to how to create it?

Share via