What exactly is the information you'd like to see? Can you use a custom report to get this?
I'd like to have the Top 10 Nodes by Percent Ports Used resource only display nodes that have less than 100 % Ports used.
Right now, the top 23 nodes in this resource on my system are all 100% used and I would like to filter them away, instead of removing them from the UDT tracker list.
There is not report that can do this for me.
Is there any specific filter query I can use to only show me nodes that have less than 100% ports used?
there is no report, but you can create one as a new SQL report. I extracted the SQL query from the resource and tweaked it to filter out the nodes with 100% Port usage.
SELECT TOP 10 * FROM
SELECT S.NodeID, N.Status, N.Caption, N.IP_Address, S.LastScan, S.LastSuccessfulScan, S.LastScanResult,
ISNULL(AllPortCount.NodeTotal,0) AS [PortCount],
ISNULL(AllActivePortCount.NodeTotal,0) AS [ActivePortCount],
(ISNULL(AllPortCount.NodeTotal,0) - ISNULL(AllActivePortCount.NodeTotal,0)) AS PortsAvailable, CASE WHEN (ISNULL(AllPortCount.NodeTotal,0) = 0 OR ISNULL(AllActivePortCount.NodeTotal,0) = 0) THEN 0
ELSE ((ISNULL(AllActivePortCount.NodeTotal,0) * 100 ) / ISNULL(AllPortCount.NodeTotal,0)) END AS [PortPercentUsed]
FROM Nodes N
INNER JOIN UDT_NodeCapability S ON N.NodeID=S.NodeID AND S.Capability = 2
RIGHT OUTER JOIN (SELECT NodeID, COUNT(PortID) AS NodeTotal FROM UDT_Port GROUP BY NodeID) AllPortCount ON AllPortCount.NodeID = S.NodeID
RIGHT OUTER JOIN (SELECT NodeID, COUNT(PortID) AS NodeTotal FROM UDT_Port GROUP BY NodeID, OperationalStatus HAVING OperationalStatus = 1) AllActivePortCount ON AllActivePortCount.NodeID = S.NodeID
WHERE ISNULL(AllPortCount.NodeTotal,0) > 0
) as data
Where PortPercentUsed < 100
ORDER BY PortPercentUsed DESC, ActivePortCount DESC, PortsAvailable ASC
User Report Writer to create a new "Advanced SQL" report, where you can use this query, and tweak the displayed columns.
Works out great! Now if we could get that as a native option in a resource with percent bar/graphics then it would totally rock!
Also, something that has been discussed extensively in previous discussions: the ability to group the Nodes in the Top XX resource by Custom Property.
We have close to 2000 nodes that we svan and there is no way to tell which country or region (Custom Property) has the least/most free ports available.
Just wanted to point that out once again.
Thanks for the rapid feedback and custom report!
The custom properties for ports are definitely on our roadmap.