SCCM Client Version Dashboard | SQL Query | ConfigMgr | Step by Step

0
SCCM Client Version Dashboard SQL Query ConfigMgr Step by Step

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 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 required to get this query.

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.

SQL Query

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.

SCCM Client Version Dashboard | SQL Query | ConfigMgr | Step by Step 1
SCCM/MECM Client Upgrade Tracker Dashboard – SCCM Client Version Dashboard
  • 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)'
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’s me know in the comments if you have any specific requirement.

Results – SCCM Client Version Details

SCCM Client Version Dashboard | SQL Query | ConfigMgr | Step by Step 2
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, 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.

Resources

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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