Posted on by mteegarden

Using SQL to list the Site Servers, Roles and Boundaries of the SCCM Hierarchy

Today I was trying to gather a list of all of our site servers and roles.  I then wanted to find out what site servers were protected.  The built in reports the SCCM provides did not have all this information.  That’s OK….as long as we know SQL!

-- Create Table #One

-- (

-- SiteCode varchar(256)

-- , ServerName varchar (256)

-- , RoleName varchar (256)

-- )

--

-- Insert into #One (Sitecode,servername,rolename)

--

create view v_SCCM_ServerInfrastructure as -- decided to create view instead of temp table

SELECT sys.SiteCode, sys.ServerName,

    case when sys.RoleName='SMS Distribution Point' 

        then case when dp.IsPeerDP=1 then 'SMS Branch Distribution Point'

            else 'SMS Standard Distribution Point' end

    else sys.RoleName end as 'RoleName'

FROM v_SystemResourceList as sys

left join v_DistributionPointInfo as dp

on sys.NALPath = dp.NALPath

-- Order by sys.SiteCode, sys.ServerName, RoleName  -- need to comment out for the Create View statement

--

-- Create Table #Two

-- (

-- ServerName varchar (256)

-- , SiteCode varchar(256)

-- , value varchar (256)

-- , displayname varchar (256)

-- , rolename varchar (256)

-- )

--

-- Insert into #Two (servername,sitecode,value,displayname, rolename)

--

create view v_SCCM_DistributionPoints as -- decided to create view instead of temp table

select dp.ServerName

,bi.SiteCode

,bi.value

,bi.displayname

,case when dp.IsPeerDP=1 then 'SMS Branch Distribution Point'

            else 'SMS Standard Distribution Point' end as 'RoleName'

from v_DistributionPointInfo as dp

left join ProtectedSiteSystem_ARR ps

on ps.servername = dp.servername

left join v_BoundaryInfo bi

on ps.boundaryID = bi.boundaryID

-- order by ps.servername -- need to comment out for the Create View statement

--

-- select * from #one

-- select * from #two

--

-- select a.SiteCode, a.Servername, a.RoleName, b.value as 'Boundary', b.displayname as 'Boundary_Displayname'

-- from #one a

-- left join #two b

-- on a.rolename = b.rolename and a.servername = b.servername

-- order by a.SiteCode, a.Rolename

--

create view v_SCCM_Server_Infrastructure_Boundaries as

select a.SiteCode, a.Servername, a.RoleName, b.value as 'Boundary', b.displayname as 'Boundary_Displayname'

from v_SCCM_ServerInfrastructure a

left join v_SCCM_DistributionPoints b

on a.rolename = b.rolename and a.servername = b.servername

--

select * from v_SCCM_Server_Infrastructure_Boundaries

order by SiteCode, Rolename