I am not sure about those 30 days though, but this is the report that I use to report the same - would that be suitable?
COUNT (*) AS "Total Ports",
SUM (IsUp) AS "Used Ports",
SUM (IsDown) AS "Available Ports",
CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
WHERE (InterfaceTypeName like '%ethernet%')
JOIN Nodes n ON n.NodeID = T1.NodeID
GROUP BY T1.NodeID, T1.Caption, n.GroupStatus, n.VendorIcon, n.NodeID, n.Caption
ORDER BY "Available Ports" DESC
Ou, sorry buddy, just realised that my one is for NCM and you do need for UDT ... let me see what I can do with yours... 1 moment...
1 of 1 people found this helpful
How about this?
COUNT(*) AS 'Total',
SUM(CASE WHEN ((ISNUMERIC(u.DaysUnused) = 1 AND CAST(u.DaysUnused AS INT) >= 30) OR u.DaysUnused = 'Never') THEN 1 ELSE 0 END) AS 'Unused'
FROM UDT_Port p
JOIN Nodes n ON n.NodeID = p.NodeID
LEFT JOIN UDT_UnUsedPorts u ON u.PortID = p.PortID
GROUP BY n.Caption
Thank you for your help with this, thats perfect.
I think going forward would be interesting to see if this could be formatted in SWQL, as the Custom Query would then allow you to search by hostname which would be much more useful, rather than displaying all nodes..
Just a thought.