SQL Query to get all SCCM servers with their roles

Most of the time system administrator struggle to find all the SCCM servers with their roles. I have created one sql query to get the details servers with the roles.

Query is below

SELECT
srs.ServerName,
srs.SiteCode,
vs.SiteName,
vst.AD_Site_Name0 as ADSite,
vs.ReportingSiteCode as Parent,
vs.Installdir,
MAX(CASE srs.rolename WHEN ‘SMS Site System’ THEN ‘Yes’ Else ‘ ‘ END) as SiteSys,
MAX(CASE srs.rolename WHEN ‘SMS Component Server’ THEN ‘Yes’ Else ‘ ‘ END) as CompSer,
MAX(CASE srs.rolename WHEN ‘SMS Site Server’ THEN ‘Yes’ Else ‘ ‘ END) as SiteSer,
MAX(CASE srs.rolename WHEN ‘SMS Management Point’ THEN ‘Yes’ Else ‘ ‘ END) as MP,
MAX(CASE srs.rolename WHEN ‘SMS Distribution Point’ THEN ‘Yes’ Else ‘ ‘ END) as DP,
MAX(CASE srs.rolename WHEN ‘SMS SQL Server’ THEN ‘Yes’ Else ‘ ‘ END) as ‘SQL’,
MAX(CASE srs.rolename WHEN ‘SMS Software Update Point’ THEN ‘Yes’ Else ‘ ‘ END) as SUP,
MAX(CASE srs.rolename WHEN ‘SMS SRS Reporting Point’ THEN ‘Yes’ Else ‘ ‘ END) as SSRS,
MAX(CASE srs.RoleName WHEN ‘SMS Reporting Point’ THEN ‘Yes’ Else ‘ ‘ END) as RPT,
MAX(CASE srs.rolename WHEN ‘SMS Fallback Status Point’ THEN ‘Yes’ Else ‘ ‘ END) as FSP,
MAX(CASE srs.rolename WHEN ‘SMS ServerName Locator Point’ THEN ‘Yes’ Else ‘ ‘ END) as SLP,
MAX(CASE srs.rolename WHEN ‘SMS PXE Service Point’ THEN ‘Yes’ Else ‘ ‘ END) as PXE,
MAX(CASE srs.rolename WHEN ‘AI Update Service Point’ THEN ‘Yes’ Else ‘ ‘ END) as AssI,
MAX(CASE srs.rolename WHEN ‘SMS State Migration Point’ THEN ‘Yes’ Else ‘ ‘ END) as SMP,
MAX(CASE srs.rolename WHEN ‘SMS System Health Validator’ THEN ‘Yes’ Else ‘ ‘ END) as SysVal,
MAX(CASE srs.rolename WHEN ‘SMS Notification Server’ THEN ‘Yes’ Else ‘ ‘ END) as NotiSer,
MAX(CASE srs.rolename WHEN ‘SMS Provider’ THEN ‘Yes’ Else ‘ ‘ END) as SMSPro,
MAX(CASE srs.rolename WHEN ‘SMS Application Web Service’ THEN ‘Yes’ Else ‘ ‘ END) as WebSer,
MAX(CASE srs.rolename WHEN ‘SMS Portal Web Site’ THEN ‘Yes’ Else ‘ ‘ END) as WebSite,
MAX(CASE srs.rolename WHEN ‘SMS Branch distribution point’ THEN ‘Yes’ Else ‘ ‘ END) as BranDP
FROM
v_SystemResourceList as srs
LEFT JOIN v_site vs on srs.ServerName = vs.ServerName
LEFT JOIN v_R_System_Valid vst on LEFT(srs.ServerName, CHARINDEX(‘.’, srs.ServerName) – 1) = vst.Netbios_Name0
GROUP BY
srs.ServerName,
srs.SiteCode,
vs.SiteName,
vs.ReportingSiteCode,
vst.AD_Site_Name0,
vs.InstallDir
ORDER BY srs.sitecode,srs.ServerName

The output of the SQL script will be like as below

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s