Quick Summary

 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/*##=============================================*/

  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/*##=============================================*/

  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/*##=============================================*/

  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/*##=============================================*/