Advertisement

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

SCCM 2012 and 2007 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 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 2007 and 2012. An SQL INNER JOIN is simple join that 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.

SCCM 2012 Custom Report SQL Query to find out OS details

Following is the SQL query that you can use to find out 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

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), Script

One Comment

  1. Rohit Taygi says:

    While i am running the query to get details for the server the OS field returns Null value for the few servers

Leave a Comment and Contact Anoop