A query to find unassigned subnets including ENCOMPASSED subnets logic.

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

A managed computer from a subnet, that is not assigned to any site, will download packages from Notification Server (NS) directly.

A subnet is a logical grouping of connected network devices. A supernet is a combination of subnets into one network with a single classless interdomain routing (CIDR) prefix. 

A subnet that belongs to a supernet is called an 'Encompassed' subnet in SMP Console. Computers from an encompassed subnet will download packages from Package Servers that are linked to a site the supernet is assigned to. There is no report that could clearly demonstrate subnet - supernet bindings in the Console. 

The query listed below lists subnets, sites and supernets. 

 

No errors. 

Environment

ITMS 8.1.

Cause

Working as designed. 

Resolution

1. Backup [dbo].[fnGetSubnetMaskAsTable] in Symantec_CMDB.
2. This step is required as otherwise the query in (3) may fail.
Run 
======
ALTER FUNCTION [dbo].[fnGetSubnetMaskAsTable]
(
    @SubNet nvarchar(20)
)
RETURNS @tbl TABLE ( IpPrefix int, IpMask int )
AS
BEGIN
    DECLARE @i int,
            @m int,
            @p int

    SET @p = CHARINDEX( N'/', @SubNet, 1 )
    IF (@p > 0)
    BEGIN
        SET @i = dbo.fnIpAddrToInt2( @SubNet, @p-1 )
        IF  @i IS NOT NULL
        BEGIN
            SET @p = CAST( SUBSTRING( @SubNet, @p+1, 3 ) AS int )
            IF (@p > 0 AND @p <= 32)
            BEGIN
                           IF (@p > 1)
                                  SET @m = ~( POWER(2, ([email protected])) - 1 )
                           ELSE
                       SET @m = 0x7FFFFFFF

                INSERT
                    INTO   @tbl
                    SELECT @i & @m, @m
            END
        END
    END
    ELSE 
    BEGIN
              -- not a subnet passed, so nothing to do
        INSERT
            INTO   @tbl
            SELECT 0, 0    
    END
    
    RETURN
END
======

3. Run 
===================================

CREATE TABLE #tmpSubnet
(
       [SubnetGuid]    uniqueidentifier NOT NULL,
       [Subnet]        nvarchar(20)     NOT NULL,
       [IpPrefix]      int              NOT NULL,
       [IpMask]        int              NOT NULL
)

INSERT INTO #tmpSubnet
SELECT [Guid] AS [SubnetGuid], Name AS [Subnet], aa.IpPrefix, aa.IpMask
       FROM [vRM_Subnet_Item]
       OUTER APPLY dbo.fnGetSubnetMaskAsTable(Name) aa
       WHERE ResourceItemDeleted = 0
       ORDER BY 1

CREATE TABLE #tmpFound
(
       [SubnetGuid]    uniqueidentifier NOT NULL,
       [Subnet]        nvarchar(20)     NOT NULL,
       [IpPrefix]      int              NOT NULL,
       [IpMask]        int              NOT NULL,
       [OwnerSubnet]   nvarchar(20),
       [_ResourceGuid] uniqueidentifier
)

DELETE     ts
       OUTPUT DELETED.*, DELETED.Subnet, sm._ResourceGuid
    INTO   #tmpFound
    FROM   #tmpSubnet     ts
    JOIN   vSiteSubnetMap sm ON sm.SubnetGuid = ts.SubnetGuid

DELETE     ts
       OUTPUT DELETED.*, tf.Subnet, tf._ResourceGuid
    INTO   #tmpFound
    FROM   #tmpSubnet   ts
       JOIN   #tmpFound    tf ON tf.IpPrefix = ts.IpPrefix & tf.IpMask

SELECT   tf.Subnet, i.Name AS [Site], CASE WHEN tf.OwnerSubnet = tf.Subnet THEN
'-' ELSE tf.OwnerSubnet END AS [Supernet]
    FROM #tmpFound tf
       LEFT JOIN vSite i ON i.Guid = tf._ResourceGuid
UNION    ALL
SELECT   ts.Subnet, '', ''
    FROM #tmpSubnet ts
       ORDER BY 2 DESC, 3, 1

DROP TABLE #tmpFound
DROP TABLE #tmpSubnet
===================================

The query returns 3 columns:
a. Subnet;
b. Site;
c. Supernet. 

-If 'Site' is empty -> the subnet is unassigned.
-If 'Supernet' is emply but 'Site' is not->  the subnet is directly assigned to the site. 
-If 'Site' AND 'Supernet' are NOT empty -> the subnet is assigned to the site via the supernet. 

The aforementioned steps will be converted into a default report and added to ITMS 8.1 HF1.