SCCM - SQL Query Boundary / Boundary Group with Site System Details

SCCM - SQL Query Boundary / Boundary Group with Site System Details

SELECT BG.GROUPID,
       B.BOUNDARYID,
       B.BOUNDARYTYPE,
       BG.NAME [BOUNDARY GROUP NAME],
       B.DISPLAYNAME [BOUNDARY NAME],
       B.VALUE,
       CASE B.BOUNDARYTYPE
           WHEN '1' THEN 'AD Site'
           WHEN '0' THEN 'IP Subnet'
           WHEN '3' THEN 'IP Range'
       END AS 'Type',
       SUBSTRING(BGS.SERVERNALPATH, CHARINDEX('\\', BGS.SERVERNALPATH)+2,(CHARINDEX('"]', BGS.SERVERNALPATH) - CHARINDEX('\\', BGS.SERVERNALPATH))-3) AS 'Site System'
FROM VSMS_BOUNDARY B
INNER JOIN VSMS_BOUNDARYGROUPMEMBERS BGM ON B.BOUNDARYID = BGM.BOUNDARYID
INNER JOIN VSMS_BOUNDARYGROUP BG ON BG.GROUPID = BGM.GROUPID
INNER JOIN VSMS_BOUNDARYGROUPSITESYSTEMS BGS ON BGS.GROUPID = BGM.GROUPID

ORDER BY BG.GROUPID ASC


---------********--------------

SELECT DISTINCT BG.GROUPID,
BG.NAME [BOUNDARY GROUP NAME],
SUBSTRING(BGS.SERVERNALPATH, CHARINDEX('\\', BGS.SERVERNALPATH)+2,(CHARINDEX('"]', BGS.SERVERNALPATH) - CHARINDEX('\\', BGS.SERVERNALPATH))-3) AS 'Site System'
FROM VSMS_BOUNDARY B
INNER JOIN VSMS_BOUNDARYGROUPMEMBERS BGM ON B.BOUNDARYID = BGM.BOUNDARYID
INNER JOIN VSMS_BOUNDARYGROUP BG ON BG.GROUPID = BGM.GROUPID
INNER JOIN VSMS_BOUNDARYGROUPSITESYSTEMS BGS ON BGS.GROUPID = BGM.GROUPID
ORDER BY BG.GROUPID ASC