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: http://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: http://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

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: http://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

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