This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Query for UDT UnUsedPorts - HELP :(

greg.remer over 6 years ago

Hi All,

I have recently created a SQL Script to list all Nodes with UnUsedPorts in the last 30 days.

I currently have an issue where by I would like to try and add either a COUNT or SUM that rather than displaying all the individual ports it just summarizes to show;

NodeName > Total Ports Unused > Total Used

Again keeping the filter on 30 days here, so if it was not used for 5 days it would not be shown.

Below is what I have thus far...

SELECT UDT.NodeID AS [NodeID]

,UDT.PortID AS [PortID]

,UDT.Caption AS [Caption]

,UDT.IP_Address AS [IP_Address]

,UDT.Name AS [Name]

,UDT.PortDescription AS [Port_Description]

,UDT.DaysUnused AS [DaysUnused]

,UDP.PortID AS [PortID2]

,UDP.AdministrativeStatus AS [AdminStatus]

,UDP.OperationalStatus AS [OperStatus]

FROM UDT_UnUsedPorts AS UDT

INNER JOIN UDT_Port AS UDP on UDP.PortID = UDT.PortID

WHERE (

ISNUMERIC(UDT.DaysUnused) = 1 AND CAST(UDT.DaysUnused AS INT) >= 30 OR UDT.DaysUnused = 'Never'

)

AND UDP.AdministrativeStatus = 1

AND UDP.OperationalStatus = 2

AND UDT.Caption Like 'Switch0-%' OR UDT.Caption Like 'Switch11-%'

Order By UDT.Caption

If you can help that would be very much appreciated.alexslv

Many Thanks in advance

Greg

Top Replies

AlexSoul over 6 years ago in reply to AlexSoul +2 verified

How about this? SELECT n.Caption, 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…

0 AlexSoul over 6 years ago

Hi Greg,

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?

SELECT
T1.NodeID,
T1.Caption,
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"
FROM
(
   SELECT
ND.Caption,
ND.NodeID,
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%')
) T1
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

With Gratitude,

Alex

0 AlexSoul over 6 years ago in reply to AlexSoul

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...
Cancel
Vote Up 0 Vote Down

Cancel

0 AlexSoul over 6 years ago in reply to AlexSoul

How about this?

SELECT
n.Caption,
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

Output:

0 greg.remer over 6 years ago in reply to AlexSoul

Alex,
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.
Thanks
Greg
Cancel
Vote Up 0 Vote Down

Cancel