Download Report to Get the Documentation of SCCM ConfigMgr Site System Server Roles

5

I am very glad to share one 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.

Glimpse of the OUTPUT of the query/report.

Download Report to Get the Documentation of SCCM ConfigMgr Site System Server Roles 1

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

5 COMMENTS

  1. Thanks Anoop for sharing such valuable information which will be very useful and helpful at the same time in a very large and complex SCCM environment.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.