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

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

image

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.

image

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

image

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

image

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

image

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

image

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

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

Percentage of Inactive & Obsolete Client Count

image

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

image

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