SCCM Client Version Dashboard SQL Query Custom Report

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?

Patch My PC

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.

Adaptiva

• Open the SQL Management Studio.
• Connect to your Database Engine.

SCCM Client Version Dashboard SQL Query Custom Report
SCCM Client Version Dashboard SQL Query Custom Report

Right Click on your database and click on ‘New Query.’

SCCM Client Version Dashboard _ ConfigMgr
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard

You will see a new page to write SQL query will Open.

SCCM Client Version Dashboard _ ConfigMgr
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard _ ConfigMgr

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.

SCCM Client Version Dashboard SQL Query Custom Report 1
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard _ ConfigMgr

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

SCCM Client Version Dashboard _ ConfigMgr
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard _ ConfigMgr

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.

SCCM Client Version Dashboard _ ConfigMgr
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard _ ConfigMgr

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!

SCCM Client Version Dashboard _ ConfigMgr
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard _ ConfigMgr

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

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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.