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.

UDT report for used/unused ports per device

FormerMember
FormerMember

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.

  • FormerMember
    0 FormerMember

    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

  • FormerMember
    0 FormerMember in reply to njoylif

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

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

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

  • FormerMember
    0 FormerMember in reply to njoylif

    Relaxed 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

  • FormerMember
    0 FormerMember in reply to njoylif

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

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

  • FormerMember
    0 FormerMember in reply to FormerMember

    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.

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

  • FormerMember
    0 FormerMember in reply to Bedrich.Michalek

    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. emoticons_happy.png   Thank you!