When working with System Center Configuration Manager (SCCM),
having detailed visibility into site systems, their roles, and client health is
crucial for proactive monitoring and reporting. Below are two powerful SQL
queries designed to help administrators:
1. Identify server roles deployed within the SCCM hierarchy.
2. Audit client health, hardware configuration, OS details,
and system usage.(SCCM Server
🔍 Query 1: SCCM Server Role
Inventory
This query returns a consolidated view of SCCM site systems
and the roles assigned to each server. It helps you answer:
📘 SQL:
SELECT
srl.ServerName,
srl.SiteCode,
vs.SiteName,
vrs.AD_Site_Name0 as
ADSite,
vs.ReportingSiteCode
as Parent,
vs.Installdir,
MAX(CASE
srl.rolename WHEN 'SMS Site System' THEN 'Yes' ELSE ' ' END) as SiteSys,
MAX(CASE
srl.rolename WHEN 'SMS Component Server' THEN 'Yes' ELSE ' ' END) as CompSer,
MAX(CASE
srl.rolename WHEN 'SMS Site Server' THEN 'Yes' ELSE ' ' END) as SiteSer,
MAX(CASE
srl.rolename WHEN 'SMS Management Point' THEN 'Yes' ELSE ' ' END) as MP,
MAX(CASE
srl.rolename WHEN 'SMS Distribution Point' THEN 'Yes' ELSE ' ' END) as DP,
MAX(CASE
srl.rolename WHEN 'SMS SQL Server' THEN 'Yes' ELSE ' ' END) as 'SQL',
MAX(CASE
srl.rolename WHEN 'SMS Software Update Point' THEN 'Yes' ELSE ' ' END) as SUP,
MAX(CASE
srl.rolename WHEN 'SMS SRS Reporting Point' THEN 'Yes' ELSE ' ' END) as SSRS,
MAX(CASE
srl.RoleName WHEN 'SMS Reporting Point' THEN 'Yes' ELSE ' ' END) as RPT,
MAX(CASE
srl.rolename WHEN 'SMS Fallback Status Point' THEN 'Yes' ELSE ' ' END) as FSP,
MAX(CASE
srl.rolename WHEN 'SMS ServerName Locator Point' THEN 'Yes' ELSE ' ' END) as
SLP,
MAX(CASE
srl.rolename WHEN 'SMS PXE Service Point' THEN 'Yes' ELSE ' ' END) as PXE,
MAX(CASE
srl.rolename WHEN 'AI Update Service Point' THEN 'Yes' ELSE ' ' END) as AssI,
MAX(CASE
srl.rolename WHEN 'SMS State Migration Point' THEN 'Yes' ELSE ' ' END) as SMP,
MAX(CASE
srl.rolename WHEN 'SMS System Health Validator' THEN 'Yes' ELSE ' ' END) as
SysVal,
MAX(CASE
srl.rolename WHEN 'SMS Notification Server' THEN 'Yes' ELSE ' ' END) as
NotiSer,
MAX(CASE
srl.rolename WHEN 'SMS Provider' THEN 'Yes' ELSE ' ' END) as SMSPro,
MAX(CASE
srl.rolename WHEN 'SMS Application Web Service' THEN 'Yes' ELSE ' ' END) as
WebSer,
MAX(CASE
srl.rolename WHEN 'SMS Portal Web Site' THEN 'Yes' ELSE ' ' END) as WebSite,
MAX(CASE
srl.rolename WHEN 'SMS Branch distribution point' THEN 'Yes' ELSE ' ' END) as
BranDP
FROM v_SystemResourceList as srl
LEFT JOIN v_site vs on srl.ServerName = vs.ServerName
LEFT JOIN v_R_System_Valid vrs on LEFT(srl.ServerName,
CHARINDEX('.', srl.ServerName) - 1) = vrs.Netbios_Name0
GROUP BY
srl.ServerName,
srl.SiteCode,
vs.SiteName,
vs.ReportingSiteCode,
vrs.AD_Site_Name0,
vs.InstallDir
ORDER BY srl.sitecode, srl.ServerName;
🧠 Query 2: SCCM Server
Client Health & Inventory Report
This advanced query returns a comprehensive inventory of all
devices acting as SCCM site systems and includes:
Client health status
Hardware details (RAM,
disk, CPU)
Operating System info
Last boot time, OS
install age, client age
Manufacturer and model
Assigned site code and
domain details
📘 SQL:
SELECT DISTINCT
VRS.Netbios_Name0 AS
'Server Name',
CASE WHEN
VRS.Client0 = 1 THEN 'Yes' ELSE 'No' END AS 'Client',
CASE WHEN
VRS.Active0 = 1 THEN 'Yes' ELSE 'No' END AS 'Active',
CASE
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 1 THEN 'VMWare'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN ('3','4') THEN 'Desktop'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 IN ('8','9','10','11','12','14') THEN
'Laptop'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 THEN 'Mini Tower'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 7 THEN 'Tower'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 13 THEN 'All in One'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 THEN 'Space-Saving'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 17 THEN 'Main System Chassis'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 21 THEN 'Peripheral Chassis'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 22 THEN 'Storage Chassis'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 23 THEN 'Rack Mount Chassis'
WHEN
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 24 THEN 'Sealed-Case PC'
ELSE 'Others'
END AS 'CaseType',
LEFT(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0),
ISNULL(NULLIF(CHARINDEX(',',
MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)) - 1, -1),
LEN(MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0)))) AS 'IPAddress',
MAX(v_GS_NETWORK_ADAPTER_CONFIGUR.MACAddress0) AS 'MACAddress',
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 AS 'AssignedSite',
VRS.Client_Version0
AS 'ClientVersion',
VRS.Creation_Date0
AS 'ClientCreationDate',
DATEDIFF(D,
VRS.Creation_Date0, GETDATE()) AS 'ClientCreationDateAge',
VRS.AD_Site_Name0 AS
'ADSiteName',
dbo.v_GS_OPERATING_SYSTEM.InstallDate0 AS 'OSInstallDate',
DATEDIFF(D,
v_GS_OPERATING_SYSTEM.InstallDate0, GETDATE()) AS 'OSInstallDateAge',
CONVERT(VARCHAR,
v_Gs_Operating_System.LastBootUpTime0, 100) AS 'LastBootDate',
DATEDIFF(D,
CONVERT(VARCHAR, v_Gs_Operating_System.LastBootUpTime0, 100), GETDATE()) AS
'LastBootDateAge',
PC_BIOS_DATA.SerialNumber00 AS 'SerialNumber',
v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS 'AssetTag',
PC_BIOS_DATA.ReleaseDate00 AS 'ReleaseDate',
PC_BIOS_DATA.Name00
AS 'BiosName',
PC_BIOS_DATA.SMBIOSBIOSVersion00 AS 'BiosVersion',
v_GS_PROCESSOR.Name0
AS 'ProcessorName',
CASE
WHEN
Computer_System_DATA.Manufacturer00 LIKE 'VMware%' THEN 'VMWare'
WHEN
Computer_System_DATA.Manufacturer00 LIKE 'Gigabyte%' THEN 'Gigabyte'
WHEN
Computer_System_DATA.Manufacturer00 LIKE 'VIA Technologies%' THEN 'VIA
Technologies'
WHEN
Computer_System_DATA.Manufacturer00 LIKE 'MICRO-STAR%' THEN 'MICRO-STAR'
ELSE
Computer_System_DATA.Manufacturer00
END AS
'Manufacturer',
Computer_System_DATA.Model00 AS 'Model',
Computer_System_DATA.SystemType00 AS 'OSType',
v_GS_COMPUTER_SYSTEM.Domain0 AS 'DomainName',
VRS.User_Domain0 +
'\' + VRS.User_Name0 AS 'UserName',
v_R_User.Mail0 AS
'EMailID',
CASE
WHEN v_GS_COMPUTER_SYSTEM.domainrole0
= 0 THEN 'Standalone Workstation'
WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 1 THEN 'Member
Workstation'
WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 2 THEN 'Standalone
Server'
WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 3 THEN 'Member Server'
WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 4 THEN 'Backup Domain
Controller'
WHEN v_GS_COMPUTER_SYSTEM.domainrole0 = 5 THEN 'Primary Domain
Controller'
END AS 'Role',
CASE
WHEN Operating_System_DATA.Caption00 LIKE '%2003, Enterprise%'
THEN 'Microsoft Windows Server 2003 Enterprise Edition'
WHEN Operating_System_DATA.Caption00 LIKE '%2003, Standard%'
THEN 'Microsoft Windows Server 2003 Standard Edition'
WHEN Operating_System_DATA.Caption00 LIKE '%2003, Web%' THEN
'Microsoft Windows Server 2003 Web Edition'
ELSE Operating_System_DATA.Caption00
END AS 'OSName',
Operating_System_DATA.CSDVersion00 AS 'ServicePack',
Operating_System_DATA.Version00 AS 'Version',
((v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024)/1000) AS
'TotalRAMSize(GB)',
MAX(v_GS_LOGICAL_DISK.Size0 / 1024) AS 'TotalHDDSize(GB)',
v_GS_WORKSTATION_STATUS.LastHWScan AS 'LastHWScan',
DATEDIFF(D, v_GS_WORKSTATION_STATUS.LastHwScan, GETDATE()) AS
'LastHWScanAge'
FROM V_R_System VRS
LEFT OUTER JOIN PC_BIOS_DATA ON PC_BIOS_DATA.MachineID =
VRS.ResourceId
LEFT OUTER JOIN Operating_System_DATA ON
Operating_System_DATA.MachineID = VRS.ResourceId
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS ON
v_GS_WORKSTATION_STATUS.ResourceID = VRS.ResourceId
LEFT OUTER JOIN Computer_System_DATA ON
Computer_System_DATA.MachineID = VRS.ResourceId
LEFT OUTER JOIN v_GS_X86_PC_MEMORY ON
v_GS_X86_PC_MEMORY.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_GS_PROCESSOR ON v_GS_PROCESSOR.ResourceID =
VRS.ResourceId
LEFT OUTER JOIN v_GS_SYSTEM_ENCLOSURE ON
v_GS_SYSTEM_ENCLOSURE.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_Gs_Operating_System ON
v_Gs_Operating_System.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_RA_System_SMSAssignedSites ON
v_RA_System_SMSAssignedSites.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_GS_COMPUTER_SYSTEM ON
v_GS_COMPUTER_SYSTEM.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_GS_NETWORK_ADAPTER_CONFIGUR ON
v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = VRS.ResourceId
LEFT OUTER JOIN v_GS_LOGICAL_DISK ON
v_GS_LOGICAL_DISK.ResourceID = VRS.ResourceId AND v_GS_LOGICAL_DISK.DriveType0
= 3
LEFT OUTER JOIN v_R_User ON VRS.User_Name0 =
v_R_User.User_Name0
WHERE (VRS.Obsolete0 = 0 OR VRS.Obsolete0 IS NULL)
AND VRS.Netbios_Name0 IN (
SELECT LEFT(ServerName, CHARINDEX('.', ServerName) - 1)
FROM v_SystemResourceList
WHERE RoleName = 'SMS Site System'
)
GROUP BY
VRS.Netbios_Name0, VRS.Client0, VRS.Active0,
v_GS_SYSTEM_ENCLOSURE.ChassisTypes0,
v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0,
VRS.Client_Version0, VRS.Creation_Date0,
VRS.AD_Site_Name0, v_Gs_Operating_System.InstallDate0,
v_Gs_Operating_System.LastBootUpTime0,
PC_BIOS_DATA.SerialNumber00,
v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0, PC_BIOS_DATA.ReleaseDate00,
PC_BIOS_DATA.Name00, PC_BIOS_DATA.SMBIOSBIOSVersion00,
v_GS_PROCESSOR.Name0,
Computer_System_DATA.Manufacturer00,
Computer_System_DATA.Model00, Computer_System_DATA.SystemType00,
v_GS_COMPUTER_SYSTEM.Domain0, VRS.User_Domain0,
VRS.User_Name0, v_R_User.Mail0,
v_GS_COMPUTER_SYSTEM.domainrole0,
Operating_System_DATA.Caption00, Operating_System_DATA.CSDVersion00,
Operating_System_DATA.Version00,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, v_GS_WORKSTATION_STATUS.LastHWScan
ORDER BY VRS.Netbios_Name0;
💡 Final Thoughts
These queries are extremely useful for:
- Daily SCCM health checks
- Capacity planning
- Hardware lifecycle management
- Compliance and audit readiness
