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

Check the SCCM SQL Query to Find Out OS Details with Site Code Country. This report will help to understand the count of SCCM clients from each country.

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

I know it’s helpful to create an SCCM custom report for the same, but I didn’t have time to do so. 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. Ensure you replace the site code and Country names as required.

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

• SCCM SQL Query Windows 10 Version Count Dashboard
• SCCM Report for Windows 11 Version Count Dashboard
• SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager
• FIX SCCM SQL Replication Issues using Replication Link Analyzer
• Troubleshoot and FIX SCCM SQL Backlog Issues using SSMS
• FIX SCCM SQL-Based Database Replication Failure Between CAS Primary

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

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

SCCM SQL Query to Find Out OS Details

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

SCCM SQL Query to Find Out OS Details
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.

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 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)

We are on WhatsApp now. To get the latest step-by-step guides, news, and updates, Join our Channel. Click here. HTMD WhatsApp.

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