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

Hi All,

I am going to share you the report i created for SCCM 2012 to generate details about all site server and site system and there hostname and the roles they are running and there parent site code.

and the short form for the roles used in this report are given below…

SQL Server        -    SQL DB
Distribution Point        -    DP
Management Point        -    MP
Device Management Point    -    DMP
System Health Validator    -    SHV
State Migration Point    -    SMP
Provider            -    SMS Prov
Software Update Point    -    SUP
Fallback Status Point    -    FSP
Multicast Service Point    -    MSP
AMT Service Point        -    AMT
AI Update Service Point    -    AI
SRS Reporting Point        -    SRS
Application Web Service    -    APP WS
Portal Web Site        -    Por WS
Enrollment Server        -    ES
Enrollment Web Site    -    EWS
Endpoint Protection Point    -    EPP

—————–

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 SQL Server' THEN 'SQL DB'
WHEN 'SMS Component Server' THEN NULL
WHEN 'SMS Distribution Point' THEN 'DP'
WHEN 'SMS Site Server' THEN NULL
WHEN 'SMS Site System' THEN NULL
WHEN 'SMS Management Point' THEN 'MP'
WHEN 'SMS Device Management Point' THEN 'DMP'
WHEN 'SMS System Health Validator' THEN 'SHV'
WHEN 'SMS State Migration Point' THEN 'SMP'
WHEN 'SMS Provider' THEN 'SMS Prov'
WHEN 'SMS Software Update Point' THEN 'SUP'
WHEN 'SMS Fallback Status Point' THEN 'FSP'
WHEN 'SMS Multicast Service Point' THEN 'MSP'
WHEN 'SMS AMT Service Point' THEN 'AMT'
WHEN 'AI Update Service Point' THEN 'AI'
WHEN 'SMS SRS Reporting Point' THEN 'SRS'
WHEN 'SMS Application Web Service' THEN 'APP WS'
WHEN 'SMS Portal Web Site' THEN 'Por WS'
WHEN 'SMS Enrollment Server' THEN 'ES'
WHEN 'SMS Enrollment Web Site' THEN 'EWS'
WHEN 'SMS Endpoint Protection Point' THEN 'EPP'
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 + '.' + sys4.Domain0=sys1.ServerName
left join v_gs_operating_system as sys5 on sys5.resourceid=sys4.resourceid
order by sys1.ServerName





—————————————————————————
 Ur’s -> SithaYuvaraj | SCCM Administrator
—————————————————————————

This posting is provided AS-IS with no warranties/guarantees and confers no rights.