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.

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

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

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

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.

@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…

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