Saturday, March 2, 2013

Report to list Boundary Group with Site System Details



Hi Everyone,

Recently i got in to a trouble that one of the boundary was targeted to wrong site system and i really had a tough time opening all the boundary to see the site systems.

So i have created a report to ease this work and i hope some one out there might require this.

This report will provide the Boundary Group Name, Assignment Site Code, Site System, Boundary Target details, Modified date and admin name.


Create a report using below query and comment me if you have trouble running the report.

------

select sys1.Name, sys1.DefaultSiteCode,
(select SUBSTRING(sys2.ServerNALPath, CHARINDEX('\\', sys2.ServerNALPath) + 2,
CHARINDEX('"]', sys2.ServerNALPath) - CHARINDEX('\\', sys2.ServerNALPath) - 3 ) +
CASE sys2.Flags WHEN '1' Then ' (Slow)' WHEN '0' THEN '' END + '; ' as 'data()'
from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID
for XML path('')) as 'Site System',
(select sys4.Value + '; ' as 'data()' from vSMS_BoundaryGroupMembers as sys3
left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID
for XML path('')) as 'Boundary', sys1.ModifiedOn, sys1.ModifiedBy
from vSMS_BoundaryGroup as sys1

------

-
------------------------------------------
Ur's -> SithaYuvaraj | SCCM Administrator
------------------------------------------
-
This posting is provided AS-IS with no warranties/guarantees and confers no rights.

Use at your own risk
-

8 comments:

Unknown said...

Amazing Query
Thanks a ton

Unknown said...

All Awesomeness to you. :-)

Anonymous said...

Superb query. Hats off to you

Unknown said...

This works perfectly,thanks!

Matt K. said...

This is so great! Thanks a lot! :)

Caryn said...

You are the best!

nanb said...

Great report! Is there a way to add number of systems in each boundary group if you are using subnet boundaries? Ive been trying to figure out how to do that.Thank you!

Unknown said...

Thank you Sir