SCCM SQL Query : SQL Query for Locations and Boundaries

/ sanmansp
Download SQL Query
 
The Query :

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

Output :

Name
DefaultSiteCode
Site System
Boundary
ModifiedOn
ModifiedBy
Bahrain
EME
bahrain.contoso.com;
Bahrain – Wifi
26:20.0
contoso\admin1
India
APA
india.contoso.com;
India
06:35.0
contoso\admin2
London
EME
london.contoso.com;
London Extranet
52:51.0
contoso\admin3
New York
AME
newyork.contoso.com;
New York Wifi
55:54.0
contoso\admin4
Lima
SME
lima.contoso.com;
Lima Extranet
56:29.0
contoso\admin5
Moscow
EME
moscow.contoso.com;
Moscow
55:47.0
contoso\admin6

2 thoughts on “SCCM SQL Query : SQL Query for Locations and Boundaries”

  1. why am i getting the following error when try to run from SQL:
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘–’.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword ‘for’

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s