SCCM SQL Query to Find Out OS Details with Site Code Country

Let’s check SCCM SQL Query to Find Out OS Details with the assigned Site Code and Country. This report is going to help to understand the count of SCCM clients from each country.

SCCM Custom report/SQL query to find out the Operating System details of Windows 10 or Windows 11 devices in your organization with Site code and country details.

I know, it’s useful to create an SCCM custom report for the same but I didn’t get time to create one. Also, I’m not an expert in SQL queries. I used SQL “Inner join” to join the different views of SCCM. 

An SQL INNER JOIN is simple to join which means returning all rows from multiple tables where the join condition is met. Make sure you replace the site code and Country names as per your requirement.

Patch My PC
SCCM ConfigMgr SQL Query to Find Out OS Details with SP Site Code Country details Configuration Manager
SCCM ConfigMgr SQL Query to Find Out OS Details with SP Site Code Country details Configuration Manager

SCCM SQL Query to Find Out OS Details with SP Site Code Country

SCCM ConfigMgr SQL Query to Find Out OS Details with SP Site Code Country details Configuration Manager? You can follow the below steps to create the SQL Query to Find Out OS Details with SP Site Code Country.

  • Open the SQL Server Management Studio (aka SSMS).
  • Connect your Database Engine.
  • Right Click on your database CM_XXX and click on ‘New Query’
  • Copy the following SQL query to find the report SCCM SQL Query to Find Out OS Details.
  • Click on the Execute button.

Following is the SQL query that you can use to find out the operating system, Service Pack, Site Code, and Location of machines in your organization. I tested this SQL query in SCCM.

select Distinct
v_R_System.Name0,
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System', 
v_GS_OPERATING_SYSTEM.CSDVersion0 as 'Service Pack',
dbo.v_FullCollectionMembership.SiteCode,
CASE
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'C%' THEN 'Japan'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'D%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'E%' THEN 'HongKong'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'F%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'G%' THEN 'US'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'H%' THEN 'Belgium'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'I%' THEN 'Germany'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'J%' THEN 'France'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'K%' THEN 'Italy'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'L%' THEN 'PORTUGAL'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'M%' THEN 'SPAIN'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'N%' THEN 'UK'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'O%' THEN 'UK'
ELSE 'Unidentified' END AS 'Country'
 from v_R_System INNER JOIN
 dbo.v_FullCollectionMembership 
 ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID 
 Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
 where (dbo.v_FullCollectionMembership.SiteCode != 'NULL') and (Operating_System_Name_and0 != 'NULL') 
 and (Active0 = '1') and (Client0 = '1')
ORDER BY dbo.v_FullCollectionMembership.SiteCode
SCCM SQL Query to Find Out OS Details with SP Site Code Country
SCCM SQL Query to Find Out OS Details with SP Site Code Country

SCCM Custom Report for Client with IP Address Details

SCCM custom Report for Client with IP Address Details. SQL query to add IP Address to the above report for SCCM 2012 (this won’t work for SCCM 2007).

The additional SQL query has been uploaded to the GitHub repository – SCCM-ConfigMgr-SQL-Query-to-Find-Out-OS-Details/SCCM-ConfigMgr-SQL-Query-to-Find-Out-OS-Details.sql at main · AnoopCNair/SCCM-ConfigMgr-SQL-Query-to-Find-Out-OS-Details (github.com)

Adaptiva

SCCM ConfigMgr SQL Query to Find Out OS Details with SP Site Code Country details Configuration Manager?

SCCM SQL Query to Find Out OS Details with SP Site Code Country SCCM Custom Report for Client with IP Address Details
SCCM SQL Query to Find Out OS Details with SP Site Code Country SCCM Custom Report for Client with IP Address Details

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.

2 thoughts on “SCCM SQL Query to Find Out OS Details with Site Code Country”

Leave a Comment

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