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

Hi All,

This is my First Post in my WordPress Blog…

I am going to share you the report i created and amazed my self with the outcome and i am sharing this report so that all SCCM Adminsitrators out there can make use of it.

This report will give 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…

AI Update Service Point    – AI
AMT Service Point        – AMT
Distribution Point        – DP
Fallback Status Point    – FSP
Management Point        – MP
PXE Service Point        – PSP
Reporting Point        – RP
Server Locator Point    – SLP
Software Update Point    – SUP
SQL Server        – SQL DB
SRS Reporting Point        – SRP
State Migration Point    – SMP
System Health Validator    – SHV

Let me know if you have any query regarding this report.

—————–

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 Site Server’ THEN NULL
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 SQL Server’ THEN ‘SQL DB’
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

—————–

  • —————————————–

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

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

Advertisements