List devices by boundary group, boundary and network information. 0 /ConfigMgr_HUB/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602} None 11111111-1111-1111-1111-111111111111 CMSQLDatabase =Parameters!UserTokenSIDs.Value /* Get AdminID Dataset */ SELECT dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) AS UserSIDs UserSIDs System.String CMSQLDatabase =Parameters!UserSIDs.Value SELECT CollectionID, Name FROM dbo.fn_rbac_Collection(@UserSIDs) WHERE CollectionType = 2 ORDER BY Name; true CollectionID System.String Name System.String CMSQLDatabase =Parameters!Locale.Value =Parameters!UserSIDs.Value =Parameters!CollectionID.Value /* .SYNOPSIS List devices by boundary and network information. .DESCRIPTION List devices by boundary group, boundary and network information. .NOTES Created by Ioan Popovici Part of a report should not be run separately. Requires CM_Tools.dbo.ufn_IsIPInSubnet CM_Tools.dbo.ufn_IsIPInRange CM_Tools.dbo.ufn_CIDRFromIPMask .LINK https://SCCM.Zone/SIT-Devices-by-Boundary-and-Network .LINK https://SCCM.Zone/SIT-Devices-by-Boundary-and-Network-CHANGELOG .LINK https://SCCM.Zone/SIT-Devices-by-Boundary-and-Network-GIT .LINK https://SCCM.Zone/Issues */ /*##=============================================*/ /*## QUERY BODY */ /*##=============================================*/ /* Testing variables !! Need to be commented for Production !! */ --DECLARE @UserSIDs AS NVARCHAR(10) = 'Disabled'; --DECLARE @CollectionID AS NVARCHAR(10) = 'HUB0074A'; --DECLARE @Locale AS INTEGER = '2'; /* Variable declaration */ DECLARE @LCID AS INTEGER = dbo.fn_LShortNameToLCID (@Locale); WITH BoundaryData_CTE (Occurrences, Device, Managed, OperatingSystem, DomainOrWorkgroup, ADSite, SCCMSite, SCCMSiteCode, BoundaryGroup, Boundary, IPAddress, IPSubnet, IPSubnetMask) AS ( /* Get boundary data */ SELECT Occurrences = Count(*) OVER (PARTITION BY Systems.ResourceID) -- Count ResourceID occurrences , Device = ISNULL(NULLIF(Systems.NetBios_Name0, '-'), 'N/A') , Managed = ( CASE Systems.Client0 WHEN 1 THEN 'Yes' ELSE 'No' END ) , OperatingSystem = ( /* Get OS caption by version */ CASE WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 5.%' THEN 'Windows XP' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.0%' THEN 'Windows Vista' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.1%' THEN 'Windows 7' WHEN Systems.Operating_System_Name_And0 LIKE 'Windows_7 Entreprise 6.1' THEN 'Windows 7' WHEN Systems.Operating_System_Name_And0 = 'Windows Embedded Standard 6.1' THEN 'Windows 7' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.2%' THEN 'Windows 8' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.3%' THEN 'Windows 8.1' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 10%' THEN 'Windows 10' WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 10%' THEN 'Windows 10' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 5.%' THEN 'Windows Server 2003' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.0%' THEN 'Windows Server 2008' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.1%' THEN 'Windows Server 2008 R2' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.2%' THEN 'Windows Server 2012' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.3%' THEN 'Windows Server 2012 R2' WHEN Systems.Operating_System_Name_And0 LIKE '%Server 10%' THEN ( CASE WHEN CAST(REPLACE(Build01, '.', '') AS INTEGER) > 10017763 THEN 'Windows Server 2019' ELSE 'Windows Server 2016' END ) ELSE Systems.Operating_System_Name_And0 END ) , DomainOrWorkgroup = ISNULL(Full_Domain_Name0, Systems.Resource_Domain_Or_Workgr0) , ADSite = CombinedResources.ADSiteName , SCCMSite = Sites.SiteName , SCCMSiteCode = CombinedResources.SiteCode , BoundaryGroup = ISNULL(BoundaryGroup.Name, 'N/A') , Boundary = ISNULL(Boundary.DisplayName, 'N/A') , IPAddress = Network.IPAddress0 , IPSubnet = ( CASE /* Support function */ WHEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Subnets.IP_Subnets0, Network.IPSubnet0) = 1 THEN Subnets.IP_Subnets0 ELSE NULL END ) , IPSubnetMask = ( CASE /* Support function */ WHEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Subnets.IP_Subnets0, Network.IPSubnet0) = 1 /* Support function */ THEN Network.IPSubnet0 + CM_Tools.dbo.ufn_CIDRFromIPMask(Network.IPSubnet0) -- Add CIDR to the IP subnet ELSE NULL END ) FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers LEFT JOIN v_R_System AS Systems ON Systems.ResourceID = CollectionMembers.ResourceID LEFT JOIN v_CombinedDeviceResources AS CombinedResources ON CombinedResources.MachineID = CollectionMembers.ResourceID LEFT JOIN v_Site AS Sites ON Sites.SiteCode = CombinedResources.SiteCode LEFT JOIN v_Network_DATA_Serialized AS Network ON Network.ResourceID = CollectionMembers.ResourceID AND IPEnabled0 = 1 -- Exclude non-enabled adapters AND Network.IPAddress0 NOT LIKE '%:%' -- Exclude IPv6 LEFT JOIN v_RA_System_IPSubnets AS Subnets ON Subnets.ResourceID = CollectionMembers.ResourceID INNER JOIN vSMS_Boundary AS Boundary ON ( CASE WHEN Boundary.BoundaryType = 0 /* Support function */ THEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Boundary.Value, Network.IPSubnet0) WHEN Boundary.BoundaryType = 1 AND Boundary.Value = CombinedResources.ADSiteName THEN 1 WHEN Boundary.BoundaryType = 3 /* Support function */ THEN CM_Tools.dbo.ufn_IsIPInRange(Network.IPAddress0, Boundary.Value) END ) = 1 -- Join only if the Boundary value matches ADSiteName or is in the computer subnet or subnet range. INNER JOIN vSMS_BoundaryGroupMembers AS BoundaryRelation ON BoundaryRelation.BoundaryID = Boundary.BoundaryID INNER JOIN vSMS_BoundaryGroup AS BoundaryGroup ON BoundaryGroup.GroupID = BoundaryRelation.GroupID WHERE CollectionMembers.CollectionID = @CollectionID ) /* Remove rows that have no subnet only when a ResourceID is present more than once in the result */ SELECT Device , Managed , OperatingSystem , DomainOrWorkgroup , ADSite , SCCMSite , SCCMSiteCode , BoundaryGroup , Boundary , IPAddress , IPSubnet , IPSubnetMask FROM BoundaryData_CTE AS BoundaryData WHERE ( (BoundaryData.Occurrences > 1 AND BoundaryData.IPSubnet IS NOT NULL) -- Remove all rows that have no subnet OR (BoundaryData.Occurrences = 1) -- Keep at least one occurrence, even if the subnet is NULL ) /*##=============================================*/ /*## END QUERY BODY */ /*##=============================================*/ true Device System.String Managed System.String OperatingSystem System.String DomainOrWorkgroup System.String ADSite System.String SCCMSite System.String SCCMSiteCode System.String BoundaryGroup System.String Boundary System.String IPAddress System.String IPSubnet System.String IPSubnetMask System.String 1.47018in 8.35556in 0.4in true true =SrsResources.Localization.GetString("Description", User!Language) 2pt 2pt 2pt 2pt true true List devices by boundary group, boundary and network information. 5pt 2pt 2pt 2pt DeviceAndBoundaryData 0.03194in 0.127cm 0.4in 9.82574in 0.44815in 0.04887in 0in 19.27333in 1 1.5pt 1.5pt 1.5pt 1.5pt 1.5pt 3.15274cm 0.6cm true =Fields!IPSubnetMask.Value Device true =SrsResources.Localization.GetString("Subnet Mask", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 0.6cm true true Textbox293 Middle 2pt 2pt 2pt 2pt 0.6cm true Device true =Fields!IPSubnetMask.Value WhiteSmoke Middle 2pt 2pt 2pt 2pt 0.6cm true true =CountDistinct(Fields!Device.Value) LightGrey Middle 2pt 2pt 2pt 2pt 3.34026cm true =Fields!Device.Value Device true =SrsResources.Localization.GetString("Device", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 2.34125cm true =Fields!Managed.Value Device true =SrsResources.Localization.GetString("Managed", User!Language) Black 1pt Black 1pt LightGrey Middle 2pt 2pt 2pt 2pt 4.34988cm true =Fields!OperatingSystem.Value Device true =SrsResources.Localization.GetString("OperatingSystem", User!Language) Black 1pt Black 1pt LightGrey Middle 2pt 2pt 2pt 2pt 4.91003cm true =Fields!DomainOrWorkgroup.Value Device true =SrsResources.Localization.GetString("Domain or Workgroup", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 4.01045cm true =Fields!ADSite.Value Device true =SrsResources.Localization.GetString("AD Site", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 6.00838cm true =Fields!SCCMSite.Value Device true =SrsResources.Localization.GetString("SCCM Site", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true =Fields!SCCMSiteCode.Value Device true =SrsResources.Localization.GetString("SCCM Site Code", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 7.13154cm true =Fields!BoundaryGroup.Value Device true =SrsResources.Localization.GetString("Boundary Group", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt 6.20973cm true =Fields!Boundary.Value Device true =SrsResources.Localization.GetString("Boundary", User!Language) Black 1pt Black 1pt LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true =Fields!IPAddress.Value Device true =SrsResources.Localization.GetString("IP Address", User!Language) Black 1pt Black 1pt LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true =Fields!IPSubnet.Value Device true =SrsResources.Localization.GetString("IP Subnet", User!Language) LightGrey Middle 2pt 2pt 2pt 2pt =Fields!Device.Value =Fields!Device.Value =Fields!Managed.Value =Fields!OperatingSystem.Value =Fields!DomainOrWorkgroup.Value =Fields!ADSite.Value =Fields!SCCMSite.Value =Fields!SCCMSiteCode.Value =Fields!BoundaryGroup.Value =Fields!Boundary.Value 3.34026cm true true =Fields!Device.Value Middle 2pt 2pt 2pt 2pt =Fields!Managed.Value =Fields!Managed.Value 2.34125cm true true =Fields!Managed.Value Middle 2pt 2pt 2pt 2pt =Fields!OperatingSystem.Value =Fields!OperatingSystem.Value 4.34988cm true true =Fields!OperatingSystem.Value Middle 2pt 2pt 2pt 2pt =Fields!DomainOrWorkgroup.Value =Fields!DomainOrWorkgroup.Value 4.91003cm true true =Fields!DomainOrWorkgroup.Value Middle 2pt 2pt 2pt 2pt =Fields!ADSite.Value =Fields!ADSite.Value 4.01045cm true true =Fields!ADSite.Value Middle 2pt 2pt 2pt 2pt =Fields!SCCMSite.Value =Fields!SCCMSite.Value 6.00838cm true true =Fields!SCCMSite.Value Middle 2pt 2pt 2pt 2pt =Fields!SCCMSiteCode.Value =Fields!SCCMSiteCode.Value 2.5cm true true =Fields!SCCMSiteCode.Value Middle 2pt 2pt 2pt 2pt 7.13154cm true true Middle 2pt 2pt 2pt 2pt 6.20973cm true true Middle 2pt 2pt 2pt 2pt 2.5cm true true Middle 2pt 2pt 2pt 2pt 2.5cm true true Middle 2pt 2pt 2pt 2pt After =Fields!BoundaryGroup.Value =Fields!SCCMSite.Value =Fields!ADSite.Value =Fields!DomainOrWorkgroup.Value =Fields!OperatingSystem.Value =Fields!IPAddress.Value =Fields!IPSubnet.Value =Fields!IPSubnetMask.Value 7.13154cm true Device true =Fields!BoundaryGroup.Value WhiteSmoke Middle 2pt 2pt 2pt 2pt 6.20973cm true Device true =Fields!Boundary.Value WhiteSmoke Middle 2pt 2pt 2pt 2pt 2.5cm true Device true =Fields!IPAddress.Value WhiteSmoke Middle 2pt 2pt 2pt 2pt 2.5cm true Device true =Fields!IPSubnet.Value WhiteSmoke Middle 2pt 2pt 2pt 2pt 3.34026cm true true Total LightGrey Middle 2pt 2pt 2pt 2pt 2.34125cm true true LightGrey Middle 2pt 2pt 2pt 2pt 4.34988cm true true LightGrey Middle 2pt 2pt 2pt 2pt 4.91003cm true true LightGrey Middle 2pt 2pt 2pt 2pt 4.01045cm true true LightGrey Middle 2pt 2pt 2pt 2pt 6.00838cm true true LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true true LightGrey Middle 2pt 2pt 2pt 2pt 7.13154cm true true LightGrey Middle 2pt 2pt 2pt 2pt 6.20973cm true true LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true true LightGrey Middle 2pt 2pt 2pt 2pt 2.5cm true true LightGrey Middle 2pt 2pt 2pt 2pt DeviceAndBoundaryData 1.2997cm 0.12413cm 2.4cm 48.95425cm 2 3.6997cm 49.36913cm 2.49515cm true true true true =SrsResources.Localization.GetString(Globals!ReportName, User!Language) 1.59698cm 0.127cm 0.89817cm 24.95739cm Middle 2pt 2pt 2pt 2pt Embedded Report_Logo FitProportional 0.0617cm 0.12413cm 1.35889cm 1.5cm 1 0.60444in 0.05in 0in 19.2722in 2 0.75pt 0.75pt 0.75pt 0.75pt 0.75pt 1.25148cm true true true true ="Page: " + Globals!PageNumber.ToString() + "of " + Globals!TotalPages.ToString() 0.38806cm 0.12413cm 0.63492cm 2.79292cm 0.06944in 0.04887in 0in 19.27333in 1 1.5pt 1.5pt 1.5pt 1.5pt 1.5pt 21cm 29.7cm 2.54cm 2.54cm 2.54cm 2.54cm 1.27cm