Find more details on SCCM Client Version Dashboard SQL Query Custom Report. Let’s see how to create a custom SCCM Client Version Dashboard using a report builder and SQL query.
Since the release of the Configuration Manager (aka SCCM) Current Branch, admins must analyze and keep their environment to the latest version of the client along with the server. No registration No Email ID is required to get this query. Here you go!
If we are managing a large environment then upgrading clients can be a headache to monitor. There is always the ‘Automatic Client Upgrade’ option but depending on your Infrastructure many follow to upgrade the client in a Controlled manner. More details here.
How to track down different client versions in your environment and how it’s progressing?
So here it is sharing a SQL Query to fetch these details and then subscribe it to your mailbox to track the progress.
You can check the latest version of the SCCM client from the following blog post. More Details: SCCM Versions Build Numbers Client.
SQL Query SCCM Client Version Dashboard
This SQL Query will help you to use the ‘when’ statement and then declare it according to your environment. I have taken specific client versions in this query you can add others according to your environment.
NOTE! – To check the client versions follow SCCM Client Versions.
• Open the SQL Management Studio.
• Connect to your Database Engine.
Right Click on your database and click on ‘New Query.’
You will see a new page to write SQL query will Open.
Copy the below SQL Query to find the client version status.
select sys.Client_Version0, "CM Name"= case sys.Client_Version0 when '5.00.8325.1000' then 'CM1511 without hotfix (5.00.8325.1000)' when '5.00.8355.1306' then 'CM1602 (5.00.8355.1306)' when '5.00.8355.1000' then 'CM1602 without hotfix (5.00.8355.1000)' when '5.00.8498.1008' then 'CM1702 (5.00.8498.1008)' when '5.00.8577.1005' then 'CM1710 (5.00.8577.1005)' when '5.00.8692.1509' then 'CM1806 with hotfix (5.00.8692.1509)' when '5.00.8692.1008' then 'CM1806 without hotfix (5.00.8692.1008)' when '5.00.8740.1012' then 'CM1810 without Hotfix (5.00.8740.1012)' when '5.00.8740.1042' then 'CM1810 with Hotfix (5.00.8740.1042)' when '5.00.8853.1020' then 'CM1906 with Hotfix (5.00.8853.1020)' when '5.00.8913.1012' then 'CM1910 (5.00.8913.1012)' when '5.00.8968.1021' then 'CM2002 with Hotfix (5.00.8968.1021)' when '5.00.9012.1015' then 'CM2006 (5.00.9012.1015)' when '5.00.9040.1010' then 'CM2010 (5.00.9040.1010)' when '5.00.9049.1008' then 'CM2103 (5.00.9049.1008)' when '5.00.9058.1012' then 'CM2107 (5.00.9058.1012)' when '5.00.9058.1047' then 'CM2107 with Hotfix (5.00.9058.1047)' when '5.00.9068.1005' then 'CM2111 (5.00.9068.1005)' else 'Others(non-Clients)' End,count(*) [Total] from v_R_System sys where sys.Name0 not like 'unknown' and sys.Client_Version0 not like '' and sys.Client_Version0 not like '0.0%' group by sys.Client_Version0 order by [Total] desc
Click on Execute to get the results.
NOTE! – Feel free to add more components to the SCCM Client Version Dashboard dashboard. Let me know in the comments if you have any specific requirements.
Results – SQL Query SCCM Client Version Dashboard
You can get the details of the SCCM client version from the following results. This SQL Query-based SCCM Client Version Dashboard is useful to track the client upgrade scenarios for your SCCM environment.
Additional Tips – Follow my post Here on how to create this using report builder, just copy and paste this query and get the desired results, give it a name like ‘SCCM Client Upgrade Tracker Dashboard’.
Create a Report Subscription – SCCM Client Version Dashboard
Once you have created this report, right-click on this report and click ‘create subscription’.
Fill in the details accordingly, try different options as I have chosen email, provided a name to the report, recipient’s email address, and description, and selected ‘Include Report’ as an excel file and click on next.
You can choose the schedule at what time and interval you want the report, select that and next>>next>>next, and done. That’s it!
How to Configure a Report server for email delivery follows Configure report server email delivery.
Identify SCCM Client Issues
You have identified the unhealthy clients, and now it’s time to identify issues. I would recommend segregating or grouping unhealthy clients based on issues. This method will help you to fix the problems systematically.
I have listed down the most common issues which I found in different environments. My recommendation would be to create collections for the following issue and add devices to the respective collections depending on the issue. These collections would help to track the client issues in a better way.
It’s not easy to identify & segregate the clients as per the issues. But I would recommend using client status node and SCCM SQL reports to segregate the unhealthy SCCM clients. I’m sure SCCM reports will help you to find out and help to fix SCCM client health issues.
Resources
- https://docs.microsoft.com/en-us/sql/sql-server/install/configure-a-report-server-for-e-mail-delivery-ssrs-configuration-manager?view=sql-server-2014
- https://www.anoopcnair.com/sccm-version-numbers-build-number-client/
Author
Hi, My Name is Ankit Shukla, and I have been working in IT since 2012. For the last 7 years, I have been working on SCCM/MECM. In these years have worked on different features of this tool, including Migration/designing the infrastructure/OSD/Custom SQL Reporting/Client-Side troubleshooting, and others. With my Blog Posts, you will find my experience on the issues which I have faced and solutions for them. The major objective of these posts is to reach out to the audience who are working as Support team members as SCCM Admins in their organizations.