The IPAM information on the Top subnets and Top Scopes are ok....but I wanted to know the top level for the subnets. The subnets can be buried within: Group > Supernet > Subnet.
This SWQL script will traverse the tree on IPAM.SUBNET and let us know the root name of the subnet. I have set this to look at just thee level deep. Additional levels can be added.
Took a bit of effort to figure out how the table was structured. Once that was accomplished, it was a just a matter of transvering the tree in a subquery.
We arrive at the top level with the corresponding subnet/scope.
select
t1.parent as [Top Level]
,'/Orion/IPAM/subnets.aspx' as [_linkfor_Top Level]
,t1.friendlyname as [Subnet]
,concat('/Orion/IPAM/Subnets.aspx?opento=',t1.subnetid) as [_linkfor_Subnet]
,case when t1.percentused> 90 then
case when t1.GroupTypeText='subnet' then '\Orion\IPAM\res\images\sw\icon.Subnet.critical.gif'
when t1.GroupTypeText='supernet' then '\Orion\IPAM\res\images\sw\icon.Supernet.critical.gif'
when t1.GroupTypeText='dhcp scope' then '\Orion\IPAM\res\images\sw\icon.scope.critical.gif' END
when t1.percentused> 80 then
case when t1.GroupTypeText='subnet' then '\Orion\IPAM\res\images\sw\icon.Subnet.warning.gif'
when t1.GroupTypeText='supernet' then '\Orion\IPAM\res\images\sw\icon.Supernet.warning.gif'
when t1.GroupTypeText='dhcp scope' then '\Orion\IPAM\res\images\sw\icon.scope.warning.gif' END
ELSE
case when t1.GroupTypeText='subnet' then '\Orion\IPAM\res\images\sw\icon.Subnet.ok.gif'
when t1.GroupTypeText='supernet' then '\Orion\IPAM\res\images\sw\icon.Supernet.ok.gif'
when t1.GroupTypeText='dhcp scope' then '\Orion\IPAM\res\images\sw\icon.scope.ok.gif' END
end as [_iconfor_Subnet]
--,t1.comments
,t1.percentused as [% Used]
,t1.availablecount as [Available]
,t1.usedcount as [Used]
from
(SELECT DISTINCT
isnull((case when (select top 1 isss.parentid from ipam.subnet isss where isss.subnetid=iss.parentid and isss.distance=0)=0 THEN
(select top 1 isss.friendlyname from ipam.subnet isss where isss.subnetid=iss.parentid and isss.distance=0)
else
(select top 1 issss.FriendlyName from ipam.subnet issss where issss.subnetid=(select top 1 isss.parentid from ipam.subnet isss where isss.subnetid=iss.parentid and isss.distance=0) and issss.distance=0)
end),'IP Networks') as [Parent]
,iss.FriendlyName
,iss.GroupTypeText
,concat(iss.Address,'/',iss.CIDR) as [Address]
,round(iss.PercentUsed,0) as [Percentused]
,iss.subnetid
,iss.TotalCount, iss.UsedCount, iss.AvailableCount, iss.ReservedCount, iss.TransientCount, iss.comments
--SubnetId, ParentId, Address, AddressN, AddressMask, CIDR, AllocSize, AllocSizeN, FriendlyName, Comments, VLAN, Location, LastDiscovery, Status, ScanInterval, PercentUsed, TotalCount, UsedCount, AvailableCount, ReservedCount, TransientCount, HasLicenceOverflow, GroupIconPrefix, StatusName, StatusShortDescription, StatusRanking, StatusIconPostfix, GroupTypeText, AccountID, Role, GroupType, Distance, DisplayName, Description, InstanceType, Uri, InstanceSiteId
FROM IPAM.Subnet iss
where iss.Address is not null --and iss.Address like '172.31.246.0%'
) t1
where t1.parent not like 'IP Networks'
order by t1.percentused desc, t1.friendlyname
Thank you
Amit