31 Replies Latest reply on Jan 10, 2018 1:38 PM by extonjse

    UDT report for used/unused ports per device

    rgward

      Unless I'm missing it, there isn't a OOTB UDT report (v2.5) that provides Used Ports/percentage, Free Ports/percentage, Total Ports per device like the UDT Ports in Use Overview graph below. Has anyone developed such a report they would be willing to share?  I'm a UDT newbie and not strong in creating Advanced SQL reports either.

       

        • Re: UDT report for used/unused ports per device
          rgward

          Here is the image for the prior post.    (Moderator, why can't I paste a graphic when creating initial post?  You can paste it but it is not saved with post.)

           

          1 of 1 people found this helpful
            • Re: UDT report for used/unused ports per device
              njoylif

              I have no idea where I got this from - I didn't write it.  I'm sure someone on thwack wrote it, but here's the SQL that I think you want.

              SELECT

                  [N].[Caption] AS Caption

                  , ISNULL([PortsTotal].[PortCount], 0) AS PortCount

                  , ISNULL([PortsActive].[PortCount], 0) AS ActivePortCount

                  , (ISNULL([PortsTotal].[PortCount], 0) - ISNULL([PortsActive].[PortCount],0)) AS UnusedPorts

                  , CASE

                      WHEN ((ISNULL([PortsTotal].[PortCount], 0) = 0) OR (ISNULL([PortsActive].[PortCount], 0) = 0)) THEN 0

                      ELSE ((ISNULL([PortsActive].[PortCount], 0) * 100) / ISNULL([PortsTotal].[PortCount], 0))

                  END AS [% PortsUsed]

              FROM dbo.Nodes AS N

              INNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))

              RIGHT JOIN (

                  SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount

                  FROM dbo.UDT_Port AS P

                  WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)

                  GROUP BY [P].[NodeID]

              ) AS PortsTotal ON ([PortsTotal].[NodeID] = [NC].[NodeID])

              RIGHT JOIN (

                  SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount

                  FROM dbo.UDT_Port AS P

                  WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1)

                  GROUP BY [P].[NodeID], [P].[OperationalStatus]

                  HAVING ([P].[OperationalStatus] = 1)

              ) AS PortsActive ON ([PortsActive].[NodeID] = [NC].[NodeID])

              WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0)

              ORDER BY [% PortsUsed] DESC, [ActivePortCount] DESC, [UnusedPorts] ASC

               

              the report looks like this (caption is the first column - cropped out)

              port usage.png

              1 of 1 people found this helpful
                • Re: UDT report for used/unused ports per device
                  rgward

                  Awesome! Thank you so much njoylif!  Just what I needed and works great! 

                   

                  P.S., watch out!  Another UDT report request post forthcoming. 

                    • Re: UDT report for used/unused ports per device
                      njoylif

                        I'll do what I can - when I can - but bring it on; just may need to be patient.

                        • Re: UDT report for used/unused ports per device
                          rgward

                          ☺  Thanks njoylif.  I really appreciate your offer to help.  I was thinking rather than start another post for the additional report, what I’m looking for is a slight variation of report you have already provided.  I’m looking for a report that provides the same used/unused port stats but broken down into used/unused ports parsed by Ethernet port name (i.e., Port Name beginning with Gig, Fa, Ten) per device.

                           

                          Caption  TotalPortCount Gig-ActivePorts Gig-UnusedPorts Fa-ActivePorts Fa-UnusedPorts Ten-ActivePorts Ten-UnusedPorts %TotalPortsUsed

                          • Re: UDT report for used/unused ports per device
                            rgward

                            njoylif,

                            I took a stab using your report as the model and created a used/unused ports by (Cisco) ethernet port name.  It works to a certain extent...it runs.  I call that a success.   Unfortunately, it only returns results if all 3 type ethernet ports (FastEthernet, Gigibit, TenGigibit) are present for a given switch.  Since we have no single switch that has all 3 port name types, no results are returned.  I came to this conclusion through process of eliminating a port name type.  For instance, when I remove the TE statements and run report, all the switches with FE&GE ports are reported but switches with all GE ports are not reported in results.  We have a mix of switches that have only FE only ports, FE&GE, GE only and some GE/10GE.  I can't quite put my finger on it but I suspect the primary WHERE clause may be the issue although it seems correct to me.  Any suggestions/thoughts?

                             

                            SELECT
                               UPPER ([N].[Caption]) AS Caption
                                , ISNULL([FEPortsTotal].[PortCount], 0) AS FEPortCount
                                , ISNULL([FEPortsActive].[PortCount], 0) AS FEActivePortCount
                                , (ISNULL([FEPortsTotal].[PortCount], 0) - ISNULL([FEPortsActive].[PortCount],0)) AS FEUnusedPorts
                                , CASE
                                    WHEN ((ISNULL([FEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([FEPortsActive].[PortCount], 0) = 0)) THEN 0
                                    ELSE ((ISNULL([FEPortsActive].[PortCount], 0) * 100) / ISNULL([FEPortsTotal].[PortCount], 0))
                                END AS [% FEPortsUsed]

                                , ISNULL([GEPortsTotal].[PortCount], 0) AS GEPortCount
                                , ISNULL([GEPortsActive].[PortCount], 0) AS GEActivePortCount
                                , (ISNULL([GEPortsTotal].[PortCount], 0) - ISNULL([GEPortsActive].[PortCount],0)) AS GEUnusedPorts
                                , CASE
                                    WHEN ((ISNULL([GEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([GEPortsActive].[PortCount], 0) = 0)) THEN 0
                                    ELSE ((ISNULL([GEPortsActive].[PortCount], 0) * 100) / ISNULL([GEPortsTotal].[PortCount], 0))
                                END AS [% GEPortsUsed]

                                , ISNULL([TEPortsTotal].[PortCount], 0) AS TenPortCount
                                , ISNULL([TEPortsActive].[PortCount], 0) AS TenActivePortCount
                                , (ISNULL([TEPortsTotal].[PortCount], 0) - ISNULL([TEPortsActive].[PortCount],0)) AS TenUnusedPorts
                                , CASE
                                    WHEN ((ISNULL([TEPortsTotal].[PortCount], 0) = 0) OR (ISNULL([TEPortsActive].[PortCount], 0) = 0)) THEN 0
                                    ELSE ((ISNULL([TEPortsActive].[PortCount], 0) * 100) / ISNULL([TEPortsTotal].[PortCount], 0))
                                END AS [% TenPortsUsed]

                             

                            FROM dbo.Nodes AS N

                             

                            INNER JOIN dbo.UDT_NodeCapability AS NC ON (([N].[NodeID] = [NC].[NodeID]) AND ([NC].[Capability] = 2))

                             

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Fa%')
                                GROUP BY [P].[NodeID]
                            ) AS FEPortsTotal ON ([FEPortsTotal].[NodeID] = [NC].[NodeID])

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Fa%')
                                GROUP BY [P].[NodeID], [P].[OperationalStatus]
                                HAVING ([P].[OperationalStatus] = 1)
                            ) AS FEPortsActive ON ([FEPortsActive].[NodeID] = [NC].[NodeID])

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Gi%')
                                GROUP BY [P].[NodeID]
                            ) AS GEPortsTotal ON ([GEPortsTotal].[NodeID] = [NC].[NodeID])

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Gi%')
                                GROUP BY [P].[NodeID], [P].[OperationalStatus]
                                HAVING ([P].[OperationalStatus] = 1)
                            ) AS GEPortsActive ON ([GEPortsActive].[NodeID] = [NC].[NodeID])

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Te%')
                                GROUP BY [P].[NodeID]
                            ) AS TEPortsTotal ON ([TEPortsTotal].[NodeID] = [NC].[NodeID])

                            RIGHT JOIN (
                                SELECT [P].[NodeID], COUNT([P].[PortID]) AS PortCount
                                FROM dbo.UDT_Port AS P
                                WHERE ([P].[Flag] = 0) AND ([P].[IsMonitored] = 1) AND ([P].[Name] LIKE 'Te%')
                                GROUP BY [P].[NodeID], [P].[OperationalStatus]
                                HAVING ([P].[OperationalStatus] = 1)
                            ) AS TEPortsActive ON ([TEPortsActive].[NodeID] = [NC].[NodeID])

                             

                            WHERE (ISNULL([FEPortsTotal].[PortCount], 0) > 0)
                              OR (ISNULL([GEPortsTotal].[PortCount], 0) > 0)
                              OR (ISNULL([TEPortsTotal].[PortCount], 0) > 0)

                            ORDER BY Caption

                              • Re: UDT report for used/unused ports per device
                                njoylif

                                I haven't had time to run it down, but off top of my head, maybe try using "HAVING" clause.

                                I'll play with it as soon as I can.

                                • Re: UDT report for used/unused ports per device
                                  rgward

                                  Checking in to see if anyone can help resolve my issue with the above report for used/unused ports by (Cisco) ethernet port name?

                                   

                                  njoylif, I couldn't get HAVING clause to work. I couldn't insert the clause correctly in the Select and kept throwing errors so I put it aside for now.

                                    • Re: UDT report for used/unused ports per device
                                      Bedrich.Michalek

                                      Hi,

                                      Replace all occurrences of "right join" with "left join", the report will work then.

                                      Or you can try this modified query (for UDT 2.5+), it will give what you need though in a slightly different way.


                                      Let me know if you'll have any troubles with that.


                                      with PortCTE as

                                      (

                                          select

                                              [PC].[NodeID]

                                              , [PC].[PortName]

                                              , isnull([PC].[PortCount], 0) as [PortCount]

                                              , isnull([APC].[PortCount], 0) as [ActivePortCount]

                                          from (

                                              select [P].[NodeID], left([P].[Name], 2) as [PortName], count([P].[PortID]) AS [PortCount]

                                              from [dbo].[UDT_Port] [P]

                                              where [P].[IsExcluded] = 0 and [P].[IsMissing] = 0 AND [P].[IsMonitored] = 1

                                              group by [P].[NodeID], left([P].[Name], 2)

                                              having left([P].[Name], 2) in ('Fa', 'Gi', 'Te')

                                          ) as PC

                                          left outer join (

                                              select [P].[NodeID], left([P].[Name], 2) as [PortName], count([P].[PortID]) AS [PortCount]

                                              from [dbo].[UDT_Port] [P]

                                              where [P].[IsExcluded] = 0 and [P].[IsMissing] = 0 AND [P].[IsMonitored] = 1

                                              group by [P].[NodeID], left([P].[Name], 2), [P].[OperationalStatus]

                                              having left([P].[Name], 2) in ('Fa', 'Gi', 'Te') and [P].[OperationalStatus] = 1

                                          ) as APC on APC.[NodeID] = PC.[NodeID] and APC.[PortName] = PC.[PortName]

                                      )

                                      select

                                          [N].[Caption]

                                          , [P].[PortName]

                                          , [P].[PortCount]

                                          , [P].[ActivePortCount]

                                          , ([P].[PortCount] - [P].[ActivePortCount]) as [UnusedPorts]

                                          , ([P].[ActivePortCount] * 100 / [P].[PortCount]) as [%PortsUsed]

                                      from [PortCTE] [P]

                                      inner join [dbo].[Nodes] [N] on [N].[NodeID] = [P].[NodeID]

                                      order by [N].[Caption], [P].[PortName]

                                      1 of 1 people found this helpful
                                        • Re: UDT report for used/unused ports per device
                                          rgward

                                          Nice work Bedrich!   My original query works great with changing the joins.  However, I love your revised query.  It's actually what I preferred the report to be but couldn't figure out how to write the query.  My query ends up taking up way too much report landscape real estate.  You da'man!  I owe you a beer.    Thank you!

                                            • Re: UDT report for used/unused ports per device
                                              jspanitz

                                              Modified this to accommodate Juniper JUNOS Ethernet Interfaces.

                                               

                                              with PortCTE as
                                              (
                                                  select
                                                      [PC].[NodeID]
                                                      , [PC].[PortName]
                                                      , isnull([PC].[PortCount], 0) as [PortCount]
                                                      , isnull([APC].[PortCount], 0) as [ActivePortCount]
                                                  from (
                                                      select [P].[NodeID], left([P].[Name], 2) as [PortName], count([P].[PortID]) AS [PortCount]
                                                      from [dbo].[UDT_Port] [P]
                                                      where [P].[IsExcluded] = 0 and [P].[IsMissing] = 0 AND [P].[IsMonitored] = 1
                                                      group by [P].[NodeID], left([P].[Name], 2)
                                                      having left([P].[Name], 2) in ('Fa', 'Fe', 'Gi', 'Ge', 'Te', 'Xe')
                                                  ) as PC
                                                  left outer join (
                                                      select [P].[NodeID], left([P].[Name], 2) as [PortName], count([P].[PortID]) AS [PortCount]
                                                      from [dbo].[UDT_Port] [P]
                                                      where [P].[IsExcluded] = 0 and [P].[IsMissing] = 0 AND [P].[IsMonitored] = 1
                                                      group by [P].[NodeID], left([P].[Name], 2), [P].[OperationalStatus]
                                                      having left([P].[Name], 2) in ('Fa', 'Fe', 'Gi', 'Ge', 'Te', 'Xe') and [P].[OperationalStatus] = 1
                                                  ) as APC on APC.[NodeID] = PC.[NodeID] and APC.[PortName] = PC.[PortName]
                                              )

                                              select
                                                  [N].[Caption]
                                                  , [P].[PortName]
                                                  , [P].[PortCount]
                                                  , [P].[ActivePortCount]
                                                  , ([P].[PortCount] - [P].[ActivePortCount]) as [UnusedPorts]
                                                  , ([P].[ActivePortCount] * 100 / [P].[PortCount]) as [%PortsUsed]

                                              from [PortCTE] [P]
                                              inner join [dbo].[Nodes] [N] on [N].[NodeID] = [P].[NodeID]
                                              order by [N].[Caption], [P].[PortName]

                                  • Re: UDT report for used/unused ports per device
                                    rickrocks

                                    Thanks for sharing the SQL script.

                                    • Re: UDT report for used/unused ports per device
                                      dwarf

                                      I keep getting an error when trying to run this. I am using UDT 3.2.0 (I'm not sure if that's the issue or not).

                                       

                                      I'm getting this:

                                       

                                       

                                      The multi-part identifier "PortsActive.PortCount" could not be bound.

                                       

                                      Any ideas?

                                       

                                  • Re: UDT report for used/unused ports per device
                                    pzjones

                                    I'm new to both Orion and the reporting side of things. I saw a report in one of the quizzes that not only gives you the unused ports, but also how many days it has been since they were used. Is this a custom report or something that exists and I just can't find it? If it is a custom report, where can I get instructions on how to create it - where would I execute the SQL code? Thanks for your patience with a newbie...

                                      • Re: UDT report for used/unused ports per device
                                        knudsenm

                                        The one that jspanitz posted above and attached a file for works well for me, but I can't get it to save for some reason, comes up with an error for NodeID.  So as far as I can tell, I can only run it on the server itself and not save the report so it can be accessed form the web.  I know I'm missing something but I'm not sure what.

                                         

                                        I tried just copying/pasting in the text but that only returns NodeIDs and not the names of the nodes.  But if I open up the report file he attached in the Orion Report Editor it looks great.

                                         

                                        Untitled.png

                                        Edit: Also, the counts do not look right for stacked switches.  See below, with names blurred out, these are all 48 port switches Cisco 3750x, so this doesn't look right.

                                         

                                        cut2.png

                                      • Re: UDT report for used/unused ports per device
                                        netteamstras

                                        I've seen one post regarding this question but I haven't seen an answer unless I'm missing something.  I just want to know either what do I need to do or something isn't right regarding the problem I have with that unused report in that the accuracy is low.  We are in the process or locking down the network due to security assessments and suggestions.  When I run the report it works great but when I correlate the NEVER used ports to active ports and I've got a ton reporting active while within the report they show as never having been used?  Am I reading this information wrong?  Is there a fix for this???

                                        • Re: UDT report for used/unused ports per device
                                          hjarriell

                                          This is great.  Anyone found a way to do something similar that lists the ports that aren't used?

                                            • Re: UDT report for used/unused ports per device
                                              rschroeder

                                              If you don't mind going old-school and creating it through the old Report-Writer, message me Monday; I've built a report to show exactly that info, which I needed after NCM 7.4 stopped displaying port-status with unused ports that also showed how long they've been down.  I used that for determining which ports might safely be unpatched and reused, rather than buying more switches.

                                               

                                              Rick S.

                                            • Re: UDT report for used/unused ports per device
                                              mandyv

                                              Hi,

                                              How to make this to grab only sudden time frame only ? For example on January 2017.

                                              • Re: UDT report for used/unused ports per device
                                                rschroeder

                                                How long do ports have to be inactive/without link before UDT says they are "unused"?

                                                 

                                                I'm confused about the accuracy and validity of the used/unused ports report.  I've discovered multiple unused ports in a switch, but UDT reports the switch as 100% PortsUsed.

                                                 

                                                See this thread:

                                                 

                                                 

                                                How long does a port have to be down before UDT reports it as "available" or "unavailable"?