SCCM Report to get All Site Server & System with there Roles

select distinct sys1.ServerName, sys5.Caption0 as ‘Operating System’,
CASE sys4.SystemType0
WHEN ‘x64-based PC’ THEN ‘x64’
WHEN ‘x86-based PC’ THEN ‘x86’
END as ‘Type’,
sys2.SiteCode as ‘Site Code’, sys2.SiteName as ‘Site Name’, sys2.ReportingSiteCode as ‘Parent Site Code’,

(select CASE sys3.RoleName
WHEN ‘SMS Component Server’ THEN NULL
WHEN ‘AI Update Service Point’ THEN ‘AI’
WHEN ‘SMS AMT Service Point’ THEN ‘AMT’
WHEN ‘SMS Distribution Point’ THEN ‘DP’
WHEN ‘SMS Fallback Status Point’ THEN ‘FSP’
WHEN ‘SMS Management Point’ THEN ‘MP’
WHEN ‘SMS PXE Service Point’ THEN ‘PSP’
WHEN ‘SMS Reporting Point’ THEN ‘RP’
WHEN ‘SMS Server Locator Point’ THEN ‘SLP’
WHEN ‘SMS Software Update Point’ THEN ‘SUP’
WHEN ‘SMS SRS Reporting Point’ THEN ‘SRP’
WHEN ‘SMS State Migration Point’ THEN ‘SMP’
WHEN ‘SMS System Health Validator’ THEN ‘SHV’
END + ‘, ‘ as ‘data()’
from v_systemresourcelist as sys3
where (sys1.ServerName = sys3.ServerName) and ( NALPath not like ‘%SMSPXEIMAGES$%’)
order by sys3.RoleName
for xml path(”)) as ‘Site Roles’ from v_systemresourcelist as sys1
left join v_site as sys2 on sys2.ServerName=sys1.ServerName
left join v_gs_computer_system as sys4 on sys4.Name0=sys1.ServerName
left join v_gs_operating_system as sys5 on sys5.resourceid=sys4.resourceid
order by sys1.ServerName

Source :


Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at

Up ↑

%d bloggers like this: