Identifying which Subnets, having Active Machines, are not assigned to a Site

Products

• IT Management Suite

Issue/Introduction

How do I determine which subnets are not in sites and from active computers?

Environment

ITMS 8.x

Resolution

These active computers, which are not part of a site, will not receive any Package Server codebases and need to be added to a site. Here is the SQL to get which subnets are not in any site:

SELECT * FROM vSubnet 
WHERE Guid not in (select subnetguid from vSiteSubnetMap)

To determine if they are active, the SQL looks to see if it has a config request in the last 7 days. Here is the SQL to get which subnets are not in a site and active computers are using:

SELECT distinct ip.[Subnet], ip.[subnet mask], [Count] = count(distinct cast(cr.resourceguid as nvarchar(38)))
FROM Evt_NS_Client_config_request cr
JOIN Inv_AeX_AC_TCPIP ip on cr.ResourceGUID = ip._resourceguid
GROUP BY ip.subnet, ip.[subnet mask]
HAVING max(cr._eventtime) > getdate() -7
and ip.subnet in (
                SELECT  Subnet FROM vSubnet 
                where Guid not in (select subnetguid from vSiteSubnetMap))
order by 3 DESC

Here is the sql to determine which Class A subnets are NOT in a Site:

SELECT distinct replace(left(Subnet, 3), '.', '') as 'Class A Subnet'
FROM vSubnet 
where Guid not in (select subnetguid from vSiteSubnetMap)

Remember, Subnets are populated by Agents sending in Basic Inventory or thru a Subnet AD Import.