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 over 8 years ago

    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 over 8 years ago 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 over 8 years ago 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 over 8 years ago 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 over 8 years ago 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 over 8 years ago 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!