cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Anonymous
Not applicable

UDT report for used/unused ports per device

Jump to solution

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.

1 Solution

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

View solution in original post

33 Replies
Level 14

Hello,

I like this report a lot, as it seems it might satisfy our needs.  I would like to know if this reports show current used ports only.  The report I need shows which ports have been used or not in the last 90 days.  Thanks...

0 Kudos

@mr.e 

Hi , 

Have you got this report. I need similar report. If you have please share.

0 Kudos

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

0 Kudos

I'm new to all of this.  The SQL report listed above (LEFT JOIN) works great gives me 90% of what I'm looking for.  Thank you for sharing that.  I have some custom properties I would like to add per line also.  For Example the property "City" which will have a named city like New York or Detroit.  How would one incorporate that?

0 Kudos

Assuming you are referring to Nodes custom properties, just add them to the select statement...

SELECT
    N.Caption as Caption,
   N.City,
   N.<Other CPs>,
   ISNULL(Port......
.....


0 Kudos

Thank you for responding.  Unfortunately, I don't know SQL syntax.  Would you be so kind as to "momma bird" it for me.  Also what reference resources would you recommend to learn said syntax so I can learn this stuff myself?

0 Kudos

Playing with your code, I think I have it.  Thanks again.  Still would like to know your opinion on reference materials...

0 Kudos

I'll humbly defer to SQL or SWQL masters like njoylif​ and rgward​ or Bedrich.Michalek​ or jspanitz​.  If you ask them your detailed questions, you may find good help at hand.

0 Kudos
Level 7

Hi,

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

0 Kudos
Level 11

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

0 Kudos

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.

0 Kudos
Level 7

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

0 Kudos
Level 9

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...

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

0 Kudos
Anonymous
Not applicable

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.)

pastedImage_0.png

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

View solution in original post

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?

0 Kudos

Thanks for sharing the SQL script.

0 Kudos
Anonymous
Not applicable

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

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

0 Kudos

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

0 Kudos