4 Replies Latest reply on Apr 28, 2017 9:12 AM by Greg Remer

    SQL Query for UDT UnUsedPorts - HELP :(

    Greg Remer

      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

        • Re: SQL Query for UDT UnUsedPorts - HELP :(
          alexslv

          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