Creating Filter based on IP Subnet

[Steve Drimones]

I am trying to create a filter based on the IP subnet, but am stuck on how to do it after starting a Query Builder.  I want to have the filter for all IPs in 10.0.32.0/24.

[Igor Perevozchikov]

Hello!

Starting from 8.5 RU2 release, there are default useful reports available regarding Sites, subnets, site servers and computers by subnet.

From "Subnets with Affiliated Sites" report you can drill-down via mouse double click on required subnet to "Computers in Specified Subnet" report where you will see all (managed/unmanaged) resources related to this subnets

"Computers in Specified Subnet" report works using this stored procedure with provided subnet GUID and Trustee GUID
                     EXEC [dbo].[Report_GetComputersFromSubnet] '%subnet%', '%TrusteeScope%'


Create a new "Filter" based on SQL Query and try to use part of 'Report_GetComputersFromSubnet' stored procedure using this SQL Query:

SELECT ip._ResourceGuid,
ip.[IP Address],
ip.Subnet,
ip.[Subnet Mask],
ISNULL(ste.Name, '')
FROM Inv_AeX_AC_TCPIP ip
JOIN vSubnet sub ON sub.Subnet = ip.Subnet AND sub.[Subnet Mask] = ip.[Subnet Mask]
JOIN vComputerResource vcr ON vcr.Guid = ip._ResourceGuid
JOIN vTcpIpAddress tcp ON tcp._ResourceGuid = ip._ResourceGuid AND tcp.[IP Address] = ip.[IP Address]
LEFT JOIN ResourceAssociation ra1 ON ra1.ChildResourceGuid = sub.Guid
AND ra1.ResourceAssociationTypeGuid = 'F71ABA8C-8823-44C8-A750-F03DE84DC5F6'
LEFT JOIN vSite ste ON ste.Guid = ra1.ParentResourceGuid
WHERE sub.Guid = '2d24d7a2-6a07-4349-93a4-aa4776840c80' ---/// Specify here your subnet GUID

To get required subnet GUID, open SMP Console -> Manage -> All Resources -> find there "Subnets" and via mouse right click on required subnet, open properties.

Thanks,
IP.

------------------------------
Software QA Engineer
Broadcom Inc.
------------------------------