Configuration Manager in Microsoft IT

Real World Experiences from Microsoft IT

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

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

Rate This
  • Comments 9

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

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

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

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

Comments
  • I love the ConfigMgr Dashboard so this post from the Configuration Manager in Microsoft IT blog about

  • 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?

  • @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.

  • "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

  • how to backup the dataset config file(.xml) from the sharepoint server? thanks.

  • how to backup the dataset config file(.xml) from the sharepoint server? thanks.

  • 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

  • @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.

  • 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é

Page 1 of 1 (9 items)
Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post