30 Replies Latest reply on Dec 13, 2017 9:55 AM 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.)

           

            • 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

                • 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
                                                  jspanitz

                                                  One thing I just realized, this is just a point in time snapshot of ports in use.  This is ok when looking at Core / ToR switches, but when looking at access switches, for us it would be really beneficial to see the maximum number of ports used over a period of time.  It paints a more realistic picture for use based on endpoints coming and going.


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

                                                    Hate to bring up a "dead" topic, but just came across a need for this report.

                                                     

                                                    While the report is exactly what I am looking for, its only reporting on 20 of my 200 nodes.  Any thoughts as to why?

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

                                                        Depending on which iteration you used, check your "having" and any "where" clauses.

                                                        find a system that is NOT showing up as you would expect and verify these clauses are not excluding those systems.

                                                        or perhaps comment those out and get everything back to see what is causing the exclusions.

                                                         

                                                        Also, (always check the obvious) verify the systems are checking ports via UDT

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

                                                            thanks for the SQL info - it probably is my UDT settings.  We have a lot of 24/48 port switches, and with the way NPM /UDT is configured, i think we are only "monitoring" ethernet ports that are trunks/uplinks or specific servers.  UDT is not showing/seeing ALL the ports on a switch, since I dont want to monitor (in NPM) an used port. Ill figure it out somehow.

                                          • 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"?