📊 SCCM SQL Queries for Server Role Inventory and Client Health Analysis

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