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 Custom report/SQL query to find out the Operating System details with service pack of the machines/workstations 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 return 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 ConfigMgr SQL Query to Find Out OS Details

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

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/ConfigMgr 2007 and SCCM/ConfigMgr 2012.

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

SQL query to add IP Address to the above report for SCCM 2012 (this won't work for SCCM 2007)

select Distinct
v_R_System.Name0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.DNSHostName0,
v_GS_NETWORK_ADAPTER_CONFIGURATION.DNSDomain0,
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 '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
 Inner join v_GS_NETWORK_ADAPTER_CONFIGURATION ON v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID=v_R_System.ResourceID
 where (dbo.v_FullCollectionMembership.SiteCode != 'NULL') and (Operating_System_Name_and0 != 'NULL') 
 and (IPAddress0 IS NOT NULL) AND (IPAddress0 LIKE '10.%')
 and (Active0 = '1') and (Client0 = '1')
ORDER BY dbo.v_FullCollectionMembership.SiteCode

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

1E Nomad

Resources

SCCM Related Posts Real World Experiences Of SCCM Admins (anoopcnair.com)

2 thoughts on “SCCM ConfigMgr SQL Query to Find Out OS Details with SP Site Code Country details Configuration Manager”

Leave a Comment

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