Wednesday, January 30, 2013

Slightly Modified Report

Here is a modified Site system roles and servers report that can show all servers:
if @variable != ''
BEGIN
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 RoleName end as 'RoleName'
FROM v_SystemResourceList as sys
left join v_DistributionPointInfo as dp
on sys.NALPath = dp.NALPath
WHERE sys.SiteCode  LIKE @variable
Order by sys.SiteCode, sys.ServerName, RoleName
END
ELSE
BEGIN
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 RoleName end as 'RoleName'
FROM v_SystemResourceList as sys
left join v_DistributionPointInfo as dp
on sys.NALPath = dp.NALPath
--WHERE sys.SiteCode  LIKE @variable
Order by sys.SiteCode, sys.ServerName, RoleName END
To create a modified report, it always helps to build the report in SQL Management Studio. Most of the time, you just need to add 2 lines at the top of the query to accomidate for the SCCM prompt for some reports like the one shown above.

DECLARE @variable varchar(30)
SET @variable = ''
Then you can add whatever information to your variable (in this case called @variable)