ConfigMgr (SCCM) – Servers with Site System-Server Roles in a Particular ConfigMgr Hierarchy
I am very glad to share an Awesome report created by my colleagues Robert Spinelli and XiaoDong Zhang.
Here is that pretty cool query/report that can help you in maintaining the inventory of SCCM hierarchy and also can be used as health check report.
This report will provide you all the servers and the site system roles they have installed. This reported is created in SSRS, but also works as a regular ASP web report.
Download MOF File – Here
Download RDL File – Here
Here is the report
SET NOCOUNT ON
select distinct sum.SiteCode,
SUBSTRING(SiteSystem, (CHARINDEX(‘\’,sum.SiteSystem)+2), (CHARINDEX(‘\,SiteSystem,(CHARINDEX(‘\’,sum.SiteSystem)+2)) – (CHARINDEX(‘\’,sum.SiteSystem)+2))) ‘Server’,
CASE Sum.Role
WHEN ‘AI Update Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘AI Point’,
CASE Sum.Role
WHEN ‘SMS Distribution Point’ THEN ‘X’ ELSE ‘ ‘
End ‘DP’,
CASE Sum.Role
WHEN ‘SMS Fallback Status Point’ THEN ‘X’ ELSE ‘ ‘
End ‘FSP’,
CASE Sum.Role
WHEN ‘SMS Management Point’ THEN ‘X’ ELSE ‘ ‘
End ‘MP’,
CASE Sum.Role
WHEN ‘SMS PXE Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘PXE’,
CASE Sum.Role
WHEN ‘SMS Server Locator Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SLP’,
CASE Sum.Role
WHEN ‘SMS Site Server’ THEN ‘X’ ELSE ‘ ‘
End ‘Site Server’,
CASE Sum.Role
WHEN ‘SMS Software Update Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SUP’,
CASE Sum.Role
WHEN ‘SMS SQL Server’ THEN ‘X’ ELSE ‘ ‘
End ‘SQL’,
CASE Sum.Role
WHEN ‘SMS SRS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SSRS’,
CASE Sum.Role
WHEN ‘SMS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘RP’,
Sum.Role
into ##temp
from v_SiteSystemSummarizer sum
where not sum.Role = ‘SMS Component Server’
SET NOCOUNT OFF
–select * from ##temp
select distinct t1.SiteCode,t1.[Server],
(select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [AI Point],
(select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [DP],
(select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [FSP],
(select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [MP],
(select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [PXE],
(select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SLP],
(select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [Site Server],
(select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SUP],
(select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SQL],
(select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SSRS],
(select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [RP],
CASE Site1.Type
WHEN 1 THEN ‘Secondary’
WHEN 2 Then ‘Primary’
ELSE
case when (select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘AIPoint ‘ else ” end+
case when (select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘DP ‘ else ” end+
case when (select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘FSP ‘ else ” end+
case when (select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘MP ‘ else ” end+
case when (select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘PXE ‘ else ” end+
case when (select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SLP ‘ else ” end+
case when (select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SiteServer ‘ else ” end+
case when (select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SUP ‘ else ” end+
case when (select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SQL ‘ else ” end+
case when (select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SSRS ‘ else ” end+
case when (select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘RP ‘ else ” end
End as [ServerType],
–site1.ReportingSiteCode
(select distinct site11.ReportingSiteCode from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.ReportingSiteCode is not null) as [ReportingSiteCode],
–site1.Version
(select distinct site11.Version from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.Version is not null) as [Version]
from ##temp t1
left join v_Site site1 on t1.SiteCode = site1.SiteCode and t1.Server = site1.ServerName
drop table ##temp
Glimpse of the OUTPUT of the query/report.
http://anoopmannur.files.wordpress.com/2011/08/report.jpg?w=600&h=113
