Dark mode

Dark mode

There are 0 results matching

article card image dark article card image light

Published by · Jun 25, 2024 tools · 2 mins read

Introducing: macOS JAMF Offboarding Tool

Offboarding macOS Devices from JAMF in Bulk using the JAMF API with a bash script ...

See More
article card image dark article card image light

Published by · Jun 25, 2024 tools · 2 mins read

Introducing: Microsoft Cloud License Automation Tool - Part 1

Automating Microsoft Cloud License Assignment and Reporting with PowerShell and Slack for Enterprise Mobility and Security E3 ...

See More
article card image dark article card image light

Published by · Jun 18, 2024 tools · 2 mins read

Introducing: Configuration Manager Set Implicit Uninstall Flag Tool

Setting Configuration Manager ConfigMgr Implicit Uninstall Flag with PowerShell for Required Application Deployments ...

See More
article card image dark article card image light

Published by · Jun 11, 2024 configmgr · 2 mins read

Configuration Manager Next Maintenance Window SQL Function

Get Next Configuration Manager Maintenance Window from a Schedule Token with Offset Days using an SQL Function. ...

See More
article card image dark article card image light

Published by · Jun 3, 2024 tools · 2 mins read

Introducing: Windows User Rights Assignment Tool - Part 3

Add, Remove, or Replace Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · May 28, 2024 tools · 2 mins read

Introducing: Windows User Rights Assignment Tool - Part 2

Get and Report Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · May 22, 2024 tools · 1 mins read

Introducing: Windows User Rights Assignment Tool - Part 1

Get Windows Rights Assignment with our PowerShell Tool. ...

See More
article card image dark article card image light

Published by · Apr 11, 2024 tools · 2 mins read

Introducing: Intune Linux Onboarding Tool

Onboard Ubuntu Linux devices to Microsoft Intune using a bash script. Installs prerequisites and starts the user-driven enrollment. ...

See More
article card image dark article card image light

Published by · Apr 11, 2024 tools · 2 mins read

Introducing: Intune macOS Onboarding Tool

Onboard macOS devices to Microsoft Intune using a bash script that initiates the process. Optionally, the script converts mobile accounts, resets the FileVault key, and removes ...

See More
article card image dark article card image light

Published by · Jan 23, 2024 tools · 3 mins read

Introducing: Intune Device Renaming Tool

Rename Intune Devices by setting a Prefix or using a User Attribute as Prefix. Supports Windows, macOS, and Linux ...

See More
article card image dark article card image light

Published by · Dec 8, 2023 intune · 5 mins read

Intune Logs: A Deep Dive into Locations, Interpretation, and Configuration

A Comprehensive Guide to Locations, Interpretation, and Configuration of Intune Logs ...

See More
article card image dark article card image light

Published by · Aug 14, 2023 configmgr · 2 mins read

Configuration Manager Console Extension to show Device Collection Membership with Console Builder

Use the Configuration Manager Console Builder, to add Collection Membership View to the Device Node ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 3 mins read

Introducing: Configuration Manager SSRS Dashboards

A Configuration Manager Dashboards solution with Reports for Software Updates, Bitlocker and more ...

See More
article card image dark article card image light

Published by · Aug 3, 2023 tools · 2 mins read

Introducing: PowerShell WMI Management Toolkit Module

Streamline your WMI Namespace, Class, and Instance Management with our PowerShell Module ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 1 mins read

Configuration Manager detailed, filterable Port Documentation

Configuration Manager detailed, filterable port documentation as an excel document ...

See More
article card image dark article card image light

Published by · Jul 14, 2023 configmgr · 3 mins read

Configuration Manager PXE TFTP Window Size Bug

Configuration Manager TFTP Block Size and TFTP Window Size Correct Configuration ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 4 mins read

Introducing: Configuration Manager Client Cache Cleanup Tool

Cleaning the Configuration Manager Client Cache the Right Way with PowerShell and Configuration Baselines ...

See More
article card image dark article card image light

Published by · Jun 18, 2023 tools · 2 mins read

Introducing: Windows Cache Cleanup Tool

Cleaning Windows and Configuration Manager Caches for Configuration Manager Build and Capture Task Sequence or Standalone Use ...

See More
article card image dark article card image light

Published by · Jun 17, 2023 tools · 1 mins read

Introducing: Windows Update Database Reinitialization Tool

Proactively repair corrupted Windows Update Database with Powershell and Configuration Manager ...

See More
article card image dark article card image light

Published by · Mar 31, 2023 tools · 3 mins read

Introducing: Configuration Manager SQL Products Reporting

A Complete SQL Products reporting solution using Configuration Manager ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 configmgr · 1 mins read

Application Detection Method using the Configuration Manager Application Version

Replace hardcoded application version in scripts, with the Configuration Manager Application Version ...

See More
article card image dark article card image light

Published by · Jan 28, 2023 tools · 3 mins read

Introducing: Certificate Management Toolkit

Managing Certificates with Configuration Manager and PowerShell by using just the Public Key ...

See More
article card image dark article card image light

Published by · Jan 7, 2019 reports · 2 mins read

Configuration Manager Device Boundary and Network Information Report

List Device Boundaries and Network Information with Configuration Manager ...

See More
article card image dark article card image light

Published by · Sep 9, 1980 help · 5 mins read

MEM.Zone Blog Publishing Documentation

Publishing Documentation for MEM.Zone ...

See More

We couldn’t find anything related to

“SCCM”

BLOG / reports zone

Configuration Manager Device Boundary and Network Information Report

Published by Popovici Ioan · Jan 7, 2019 · 2 mins read
article card image dark article card image light

Quick Summary

Correct boundaries are essential for Configuration Manager clients and this report offers a wealth of information that can be used for troubleshooting purposes.

It uses the IP address to determine the boundary groups the client is part of and displays them together with the client network information in a detailed format.

Prerequisites

Software

  • Configuration Manager with Windows Update Services integration.
  • SQL Server Reporting Services (SSRS) 2017 or above.
  • SQL Compatibility Level 130 or above.

SQL User Defined Functions

SQL Permissions

  • Additional SQL permissions need to be granted for the boundary group tables. See Grant SQL Permissions.

Report


Installation

Upload reports to SSRS, update the data source, grant the necessary permissions and install the SQL helper functions.

Upload Reports to SSRS

Create the ufn_CIDRFromIPMask SQL Function

Gets the CIDR (‘/’) from an IP subnet mask.

  • Paste the code below in SSMS.
  • Change the <SITE_CODE> to match your site code.
  • Click Execute to add the function to your database.
 1/*
 2.SYNOPSIS
 3    Gets the CIDR from a IP Subnet Mask.
 4.DESCRIPTION
 5    Gets the CIDR ('/') from a IP Subnet Mask.
 6.PARAMETER IPSubnetMask
 7    Specifies the IP subnet mask.
 8.EXAMPLE
 9    SELECT dbo.ufn_CIDRFromIPMask('255.255.255.0')
10.NOTES
11    Created by Ioan Popovici (2018-12-11)
12    Credit to Chris O'Connor
13    Replace the <SITE_CODE> with your CM site code.
14    Run the code in SQL Server Management Studio.
15.LINK
16    https://clouddeveloper.space/2015/07/14/sql-inet_aton-ip-address-cidr/ (Chris o'Connor)
17.LINK
18    https://MEM.Zone
19.LINK
20    https://MEM.Zone/ISSUES
21*/
22
23/*##=============================================*/
24/*## QUERY BODY                                  */
25/*##=============================================*/
26/* #region QueryBody */
27
28USE [CM_<SITE_CODE>]
29GO
30
31SET ANSI_NULLS ON
32GO
33SET QUOTED_IDENTIFIER OFF
34GO
35
36IF EXISTS (
37    SELECT [OBJECT_ID]
38    FROM   [SYS].[OBJECTS]
39    WHERE  NAME = 'ufn_CIDRFromIPMask'
40)
41    DROP FUNCTION [dbo].[ufn_CIDRFromIPMask];
42GO
43
44CREATE FUNCTION [dbo].[ufn_CIDRFromIPMask] (
45    @IPSubnetMask VARCHAR(15)
46)
47RETURNS VARCHAR(4)
48AS
49    BEGIN
50
51        /* Variable declaration */
52        DECLARE @IPSubnetMaskToInteger AS BIGINT;
53        DECLARE @IPDefaultSubnetMaskToInteger AS BIGINT;
54        DECLARE @MaskCalc AS BIGINT;
55        DECLARE @LogarithmCacl AS INT;
56        DECLARE @Result AS VARCHAR(4);
57
58        /* Convert IP subnet mask to integer */
59        SET @IPSubnetMaskToInteger        = (
60            CONVERT(BIGINT, PARSENAME(@IPSubnetMask,1)) +
61            CONVERT(BIGINT, PARSENAME(@IPSubnetMask,2)) * 256 +
62            CONVERT(BIGINT, PARSENAME(@IPSubnetMask,3)) * 65536 +
63            CONVERT(BIGINT, PARSENAME(@IPSubnetMask,4)) * 16777216
64        );
65
66        /* Convert default IP subnet mask (255.255.255.255) to integer */
67        SET @IPDefaultSubnetMaskToInteger = (
68            CONVERT(BIGINT, 255) +
69            CONVERT(BIGINT, 255) * 256 +
70            CONVERT(BIGINT, 255) * 65536 +
71            CONVERT(BIGINT, 255) * 16777216
72        );
73
74        /* Calculate mask */
75        SET @MaskCalc                     = (@IPDefaultSubnetMaskToInteger - @IPSubnetMaskToInteger + 1);
76        SET @LogarithmCacl                = (32 - LOG (@MaskCalc, 2));
77
78        /* Calculate result */
79        SET @Result = '/' + CAST(@LogarithmCacl AS VARCHAR(5));
80
81        /* Return result */
82        RETURN  @Result;
83    END;
84GO
85
86/* Grants select rights for this function to SCCM reporting users */
87GRANT SELECT ON OBJECT::dbo.ufn_CIDRFromIPMask
88    TO smsschm_users;
89
90/* #endregion */
91/*##=============================================*/
92/*## END QUERY BODY                              */
93/*##=============================================*/

Create the ufn_IsIPInRange SQL Function

Checks if the IP is in the specified IP range.

  • Paste the code below in SSMS.
  • Change the <SITE_CODE> to match your site code.
  • Click Execute to add the function to your database.
  1/*
  2.SYNOPSIS
  3    Checks if the IP is in the specified IP range.
  4.DESCRIPTION
  5    Checks if the IP is in the specified IP range and returns 1 or 0 (True/False).
  6.PARAMETER IP
  7    Specifies the IP.
  8.PARAMETER IPRange
  9    Specifies the IP range.
 10.EXAMPLE
 11    SELECT dbo.ufn_IsIPInRange('10.10.10.22', '10.10.10.0-10.10.10.254')
 12.NOTES
 13    Created by Ioan Popovici (2019-01-14)
 14    Replace the <SITE_CODE> with your CM site code.
 15    Run the code in SQL Server Management Studio.
 16.LINK
 17    https://MEM.Zone
 18.LINK
 19    https://MEM.Zone/ISSUES
 20*/
 21
 22/*##=============================================*/
 23/*## QUERY BODY                                  */
 24/*##=============================================*/
 25/* #region QueryBody */
 26
 27USE [CM_<SITE_CODE>]
 28GO
 29
 30SET ANSI_NULLS ON
 31GO
 32SET QUOTED_IDENTIFIER OFF
 33GO
 34
 35IF EXISTS (
 36    SELECT [OBJECT_ID]
 37    FROM   [SYS].[OBJECTS]
 38    WHERE  NAME = 'ufn_IsIPInRange'
 39)
 40    DROP FUNCTION [dbo].[ufn_IsIPInRange];
 41GO
 42
 43CREATE FUNCTION [dbo].[ufn_IsIPInRange] (
 44    @IP        NVARCHAR(15)
 45    , @IPRange NVARCHAR(31)
 46)
 47RETURNS BIT
 48AS
 49    BEGIN
 50
 51        /* Variable declaration */
 52        DECLARE @IPRangeStart           AS NVARCHAR(15);
 53        DECLARE @IPRangeEnd             AS NVARCHAR(15);
 54        DECLARE @IPToInteger            AS BIGINT;
 55        DECLARE @IPRangeStartToInteger  AS BIGINT;
 56        DECLARE @IPRangeEndToInteger    AS BIGINT;
 57        DECLARE @Result                 AS BIT;
 58
 59        /* Set IP range start and range end */
 60        SET @IPRangeStart = (SELECT SUBSTRING(@IPRange, 1, PATINDEX('%-%', @IPRange) -1));
 61        SET @IPRangeEnd   = (SELECT SUBSTRING(@IPRange, PATINDEX('%-%', @IPRange) +1, LEN(@IPRange)));
 62
 63        /* Convert IP to integer */
 64        SET @IPToInteger = (
 65            CONVERT(BIGINT, PARSENAME(@IP,1)) +
 66            CONVERT(BIGINT, PARSENAME(@IP,2)) * 256 +
 67            CONVERT(BIGINT, PARSENAME(@IP,3)) * 65536 +
 68            CONVERT(BIGINT, PARSENAME(@IP,4)) * 16777216
 69        );
 70
 71        /* Convert IP range start to integer */
 72        SET @IPRangeStartToInteger = (
 73            CONVERT(BIGINT, PARSENAME(@IPRangeStart,1)) +
 74            CONVERT(BIGINT, PARSENAME(@IPRangeStart,2)) * 256 +
 75            CONVERT(BIGINT, PARSENAME(@IPRangeStart,3)) * 65536 +
 76            CONVERT(BIGINT, PARSENAME(@IPRangeStart,4)) * 16777216
 77        );
 78
 79        /* Convert IP range end to integer */
 80        SET @IPRangeEndToInteger = (
 81            CONVERT(BIGINT, PARSENAME(@IPRangeEnd,1)) +
 82            CONVERT(BIGINT, PARSENAME(@IPRangeEnd,2)) * 256 +
 83            CONVERT(BIGINT, PARSENAME(@IPRangeEnd,3)) * 65536 +
 84            CONVERT(BIGINT, PARSENAME(@IPRangeEnd,4)) * 16777216
 85        );
 86
 87        /* Calculate result */
 88        SET @Result = (
 89            CASE
 90                WHEN @IPToInteger BETWEEN @IPRangeStartToInteger AND @IPRangeEndToInteger
 91                THEN 1
 92                ELSE 0
 93            END
 94        )
 95
 96        /* Return result */
 97        RETURN  @Result;
 98    END;
 99GO
100
101/* Grants select rights for this function to SCCM reporting users */
102GRANT SELECT ON OBJECT::ufn_IsIPInRange
103    TO smsschm_users;
104
105/* #endregion */
106/*##=============================================*/
107/*## END QUERY BODY                              */
108/*##=============================================*/

Create the ufn_IsIPInSubnet SQL Function

Checks if the IP is in the specified subnet using the subnet mask.

  • Paste the code below in SSMS.
  • Change the <SITE_CODE> to match your site code.
  • Click Execute to add the function to your database.
  1/*
  2.SYNOPSIS
  3    Checks if the IP is in the specified subnet.
  4.DESCRIPTION
  5    Checks if the IP is in the specified subnet using the subnet mask.
  6    Returns 1 (True) or 0 (False).
  7.PARAMETER IP
  8    Specifies the IP to test.
  9.PARAMETER Subnet
 10    Specifies the subnet IP.
 11.PARAMETER SubnetMask
 12    Specifies the subnet mask IP in CIDR format.
 13    If you don't use CIDR the ufn_CIDRFromIPMask custom function is required.
 14.EXAMPLE
 15    SELECT dbo.ufn_IsIPInSubnet('10.10.10.22', '10.10.10.0', '/24')
 16.EXAMPLE
 17    SELECT dbo.ufn_IsIPInSubnet('10.10.10.22', '10.10.10.0', '24')
 18.EXAMPLE
 19    SELECT dbo.ufn_IsIPInSubnet('10.10.10.22', '10.10.10.0', '255.255.255.0') -- Requires the ufn_CIDRFromIPMask custom function.
 20.NOTES
 21    Created by Ioan Popovici (2018-12-12)
 22    Credit to Anthony Mattas. This is just a slightly modified version.
 23    Replace the <SITE_CODE> with your CM site code.
 24    Run the code in SQL Server Management Studio.
 25.LINK
 26    https://www.anthonymattas.com
 27.LINK
 28    https://MEM.Zone
 29.LINK
 30    https://MEM.Zone/ISSUES
 31*/
 32
 33/*##=============================================*/
 34/*## QUERY BODY                                  */
 35/*##=============================================*/
 36/* #region QueryBody */
 37
 38USE [CM_<SITE_CODE>]
 39GO
 40
 41SET ANSI_NULLS ON
 42GO
 43SET QUOTED_IDENTIFIER OFF
 44GO
 45
 46IF EXISTS (
 47    SELECT [OBJECT_ID]
 48    FROM   [SYS].[OBJECTS]
 49    WHERE  NAME = 'ufn_IsIPInSubnet'
 50)
 51    DROP FUNCTION [dbo].[ufn_IsIPInSubnet];
 52GO
 53
 54CREATE FUNCTION [dbo].[ufn_IsIPInSubnet] (
 55    @IP           AS VARCHAR(15)
 56    , @Subnet     AS VARCHAR(15)
 57    , @SubnetMask AS VARCHAR(15)
 58)
 59RETURNS BIT
 60AS
 61BEGIN
 62
 63    /* Variable declaration */
 64    DECLARE @IPBinary     AS BINARY(4);
 65    DECLARE @SubnetBinary AS BINARY(4);
 66    DECLARE @SubnetCIDR   AS TINYINT;
 67    DECLARE @Result       AS BIT;
 68
 69    /* Convert IP to Binary */
 70    SET @IPBinary    = (
 71        CAST(CAST(PARSENAME(@IP, 4) AS INTEGER) AS BINARY(1)) +
 72        CAST(CAST(PARSENAME(@IP, 3) AS INTEGER) AS BINARY(1)) +
 73        CAST(CAST(PARSENAME(@IP, 2) AS INTEGER) AS BINARY(1)) +
 74        CAST(CAST(PARSENAME(@IP, 1) AS INTEGER) AS BINARY(1))
 75    )
 76
 77    /* Convert IPSubnet to Binary */
 78    SET @SubnetBinary = (
 79        CAST(CAST(PARSENAME(@Subnet, 4) AS INTEGER) AS BINARY(1)) +
 80        CAST(CAST(PARSENAME(@Subnet, 3) AS INTEGER) AS BINARY(1)) +
 81        CAST(CAST(PARSENAME(@Subnet, 2) AS INTEGER) AS BINARY(1)) +
 82        CAST(CAST(PARSENAME(@Subnet, 1) AS INTEGER) AS BINARY(1))
 83    )
 84
 85    /* Convert IPSubnet to CIDR and remove '/' if needed */
 86    IF LEN(@SubnetMask) > 4
 87        /* Support function */
 88        SET @SubnetCIDR = REPLACE(CM_Tools.dbo.ufn_CIDRFromIPMask(@SubnetMask), '/','')
 89    ELSE
 90        SET @SubnetCIDR = REPLACE(@SubnetMask, '/', '')
 91
 92    /* Calculate result */
 93    SET @Result       = (
 94        CASE
 95            WHEN (
 96                SELECT (CAST(@IPBinary AS INTEGER) ^ CAST(@SubnetBinary AS INTEGER))
 97                & ~ -- Bitwise (AND NOT)
 98                (POWER(2, 32 - @SubnetCIDR) - 1)
 99            ) = 0
100            THEN 1
101            ELSE 0
102        END
103    )
104
105    /* Return result */
106    RETURN @Result;
107END;
108GO
109
110/* Grants select rights for this function to SCCM reporting users */
111GRANT SELECT ON OBJECT::dbo.ufn_IsIPInSubnet
112    TO smsschm_users;
113
114/* #endregion */
115/*##=============================================*/
116/*## END QUERY BODY                              */
117/*##=============================================*/

Grant SQL Permissions

By default, some of the views used by this report are restricted for reporting purposes and additional permissions need to be granted.

  • Paste the code below in SSMS.
  • Change the <SITE_CODE> to match your site code.
  • Click Execute to grant the permissions.
USE [CM_<SITE_CODE>]
GRANT SELECT ON vSMS_Boundary TO smsschm_users;
GRANT SELECT ON vSMS_BoundaryGroup TO smsschm_users;
GRANT SELECT ON vSMS_BoundaryGroupMembers TO smsschm_users;
article card image ssms-helper-functions.png
Added Functions

Preview

article card image report-site-device-by-boundary-and-network.png
Report screenshot

Code

For reference only, the report includes this query.

  1/*
  2.SYNOPSIS
  3    List devices by boundary and network information.
  4.DESCRIPTION
  5    List devices by boundary group, boundary and network information.
  6.NOTES
  7    Created by Ioan Popovici
  8    Part of a report should not be run separately.
  9    Requires
 10        CM_Tools.dbo.ufn_IsIPInSubnet
 11        CM_Tools.dbo.ufn_IsIPInRange
 12        CM_Tools.dbo.ufn_CIDRFromIPMask
 13.LINK
 14    https://MEMZ.one/SIT-Devices-by-Boundary-and-Network
 15.LINK
 16    https://MEMZ.one/SIT-Devices-by-Boundary-and-Network-CHANGELOG
 17.LINK
 18    https://MEMZ.one/SIT-Devices-by-Boundary-and-Network-GIT
 19.LINK
 20    https://MEM.Zone/ISSUES
 21*/
 22
 23/*##=============================================*/
 24/*## QUERY BODY                                  */
 25/*##=============================================*/
 26
 27/* Testing variables !! Need to be commented for Production !! */
 28--DECLARE @UserSIDs       AS NVARCHAR(10) = 'Disabled';
 29--DECLARE @CollectionID   AS NVARCHAR(10) = 'HUB0074A';
 30--DECLARE @Locale         AS INTEGER      = '2';
 31
 32/* Variable declaration */
 33DECLARE @LCID AS INTEGER = dbo.fn_LShortNameToLCID (@Locale);
 34
 35WITH BoundaryData_CTE (Occurrences, Device, Managed, OperatingSystem, DomainOrWorkgroup, ADSite, SCCMSite, SCCMSiteCode, BoundaryGroup, Boundary, IPAddress, IPSubnet, IPSubnetMask)
 36AS (
 37
 38    /* Get boundary data */
 39    SELECT
 40        Occurrences         = Count(*) OVER (PARTITION BY Systems.ResourceID)   -- Count ResourceID occurrences
 41        , Device            = ISNULL(NULLIF(Systems.NetBios_Name0, '-'), 'N/A')
 42        , Managed           = (
 43            CASE Systems.Client0
 44                WHEN 1 THEN 'Yes'
 45                ELSE 'No'
 46            END
 47        )
 48        , OperatingSystem   = (
 49
 50            /* Get OS caption by version */
 51            CASE
 52                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 5.%'              THEN 'Windows XP'
 53                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.0%'             THEN 'Windows Vista'
 54                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.1%'             THEN 'Windows 7'
 55                WHEN Systems.Operating_System_Name_And0 LIKE 'Windows_7 Entreprise 6.1'      THEN 'Windows 7'
 56                WHEN Systems.Operating_System_Name_And0 =    'Windows Embedded Standard 6.1' THEN 'Windows 7'
 57                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.2%'             THEN 'Windows 8'
 58                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 6.3%'             THEN 'Windows 8.1'
 59                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 10%'              THEN 'Windows 10'
 60                WHEN Systems.Operating_System_Name_And0 LIKE '%Workstation 10%'              THEN 'Windows 10'
 61                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 5.%'                   THEN 'Windows Server 2003'
 62                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.0%'                  THEN 'Windows Server 2008'
 63                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.1%'                  THEN 'Windows Server 2008 R2'
 64                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.2%'                  THEN 'Windows Server 2012'
 65                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 6.3%'                  THEN 'Windows Server 2012 R2'
 66                WHEN Systems.Operating_System_Name_And0 LIKE '%Server 10%'                   THEN (
 67                    CASE
 68                        WHEN CAST(REPLACE(Build01, '.', '') AS INTEGER) > 10017763 THEN 'Windows Server 2019'
 69                        ELSE 'Windows Server 2016'
 70                    END
 71                )
 72                ELSE Systems.Operating_System_Name_And0
 73            END
 74        )
 75        , DomainOrWorkgroup = ISNULL(Systems.Full_Domain_Name0, Systems.Resource_Domain_Or_Workgr0)
 76        , ADSite            = CombinedResources.ADSiteName
 77        , SCCMSite          = Sites.SiteName
 78        , SCCMSiteCode      = CombinedResources.SiteCode
 79        , BoundaryGroup     = ISNULL(BoundaryGroup.Name, 'N/A')
 80        , Boundary          = ISNULL(Boundary.DisplayName, 'N/A')
 81        , IPAddress         = Network.IPAddress0
 82        , IPSubnet          = (
 83            CASE
 84                /* Support function */
 85                WHEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Subnets.IP_Subnets0, Network.IPSubnet0) = 1
 86                THEN Subnets.IP_Subnets0
 87                ELSE NULL
 88            END
 89        )
 90        , IPSubnetMask      = (
 91            CASE
 92                /* Support function */
 93                WHEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Subnets.IP_Subnets0, Network.IPSubnet0) = 1
 94                /* Support function */
 95                THEN Network.IPSubnet0 + CM_Tools.dbo.ufn_CIDRFromIPMask(Network.IPSubnet0) -- Add CIDR to the IP subnet
 96                ELSE NULL
 97            END
 98        )
 99    FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers
100        LEFT JOIN v_R_System AS Systems ON Systems.ResourceID = CollectionMembers.ResourceID
101        LEFT JOIN v_CombinedDeviceResources AS CombinedResources ON CombinedResources.MachineID = CollectionMembers.ResourceID
102        LEFT JOIN v_Site AS Sites ON Sites.SiteCode = CombinedResources.SiteCode
103        LEFT JOIN v_Network_DATA_Serialized AS Network ON Network.ResourceID = CollectionMembers.ResourceID
104            AND IPEnabled0 = 1                               -- Exclude non-enabled adapters
105            AND Network.IPAddress0 NOT LIKE '%:%'            -- Exclude IPv6
106        LEFT JOIN v_RA_System_IPSubnets AS Subnets ON Subnets.ResourceID = CollectionMembers.ResourceID
107        INNER JOIN vSMS_Boundary AS Boundary ON
108            (
109                CASE
110                    WHEN Boundary.BoundaryType = 0
111                    /* Support function */
112                    THEN CM_Tools.dbo.ufn_IsIPInSubnet(Network.IPAddress0, Boundary.Value, Network.IPSubnet0)
113                    WHEN Boundary.BoundaryType = 1 AND Boundary.Value = CombinedResources.ADSiteName
114                    THEN 1
115                    WHEN Boundary.BoundaryType = 3
116                    /* Support function */
117                    THEN CM_Tools.dbo.ufn_IsIPInRange(Network.IPAddress0, Boundary.Value)
118                END
119            ) = 1 -- Join only if the Boundary value matches ADSiteName or is in the computer subnet or subnet range.
120        INNER JOIN vSMS_BoundaryGroupMembers AS BoundaryRelation ON BoundaryRelation.BoundaryID = Boundary.BoundaryID
121        INNER JOIN vSMS_BoundaryGroup AS BoundaryGroup ON BoundaryGroup.GroupID = BoundaryRelation.GroupID
122    WHERE CollectionMembers.CollectionID = @CollectionID
123)
124
125/* Remove rows that have no subnet only when a ResourceID is present more than once in the result */
126SELECT
127    Device
128    , Managed
129    , OperatingSystem
130    , DomainOrWorkgroup
131    , ADSite
132    , SCCMSite
133    , SCCMSiteCode
134    , BoundaryGroup
135    , Boundary
136    , IPAddress
137    , IPSubnet
138    , IPSubnetMask
139FROM BoundaryData_CTE AS BoundaryData
140WHERE (
141    (BoundaryData.Occurrences > 1 AND BoundaryData.IPSubnet IS NOT NULL) -- Remove all rows that have no subnet
142    OR
143    (BoundaryData.Occurrences = 1)                                       -- Keep at least one occurrence, even if the subnet is NULL
144)
145
146/*##=============================================*/
147/*## END QUERY BODY                              */
148/*##=============================================*/

SHARE

article card image dark article card image light

Published by · Jun 25, 2024 tools · 2 mins read

Introducing: macOS JAMF Offboarding Tool

Offboarding macOS Devices from JAMF in Bulk using the JAMF API with a bash script ...

See More
article card image dark article card image light

Published by · Jun 25, 2024 tools · 2 mins read

Introducing: Microsoft Cloud License Automation Tool - Part 1

Automating Microsoft Cloud License Assignment and Reporting with PowerShell and Slack for Enterprise Mobility and Security E3 ...

See More