SCCM - SQL Query Machine without boundaries

SCCM - SQL Query Machine without boundaries 

SELECT DISTINCT

v_R_System.Name0,

v_R_System.Client0,

v_RA_System_IPAddresses.IP_Addresses0,

v_RA_System_IPSubnets.IP_Subnets0,

v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0

FROM v_R_System LEFT OUTER JOIN

v_RA_System_IPSubnets ON v_R_System.ResourceID = v_RA_System_IPSubnets.ResourceID LEFT OUTER JOIN

v_RA_System_IPAddresses ON v_R_System.ResourceID = v_RA_System_IPAddresses.ResourceID LEFT OUTER JOIN

v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID

WHERE (v_RA_System_SMSAssignedSites.SMS_Assigned_Sites0 IS NULL)

AND (NOT (v_RA_System_IPAddresses.IP_Addresses0 IS NULL))

AND (v_R_System.Client0 IS NULL)

AND (NOT (v_RA_System_IPSubnets.IP_Subnets0 IS NULL))


order by v_RA_System_IPSubnets.IP_Subnets0

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