Hi guys,
I'm looking for a way to filter the Top XX resource for % used ports. I need to filter out the nodes that have 100% ports used.
Is there any easy way to do so?
TIA,
Deltona
Deltona,
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.
HTH,
Michal
BUMP
What exactly is the information you'd like to see? Can you use a custom report to get this?
Hi Michal,
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?
