How do I determine which Subnets, having Active Machines, are not assigned to a Site?

book

Article ID: 181559

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

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

Answer
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 cr.resourceguid)
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

Use this for SQL 2000:


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)


Subnets are populated by Agents sending in Basic Inventory. You can delete stale subnets from Resources tab > Resources > Defaults > Subnets. You can select more than one, so you can delete multiple subnets at one time. When the agents check back in the subnet will be repopulated.