5 Replies Latest reply on May 17, 2013 2:46 AM by michalB

    SQL Port usage report per device

    jawells

      Hi

      I created this basic SQL and published it via report writer for UDT

       

      select n.Caption, u.PortCount, u.ActivePortCount, u.PortCount - u.ActivePortCount as [UnusedPorts], ROUND((cast(u.ActivePortCount AS float)/u.PortCount) * 100, 1) as [% FreePorts] from dbo.UDT_Switch u, dbo.Nodes n
      where u.NodeID = n.NodeID

       

      Kind Regards

      james

        • Re: SQL Port usage report per device
          dclick

          Thanks for sharing.  Quesiton - I dont have a table "UDT_Switch". Could that be something else?

            • Re: SQL Port usage report per device
              Bedrich.Michalek

              Database schema changed between UDT 1.0 (1.0.1) and UDT 2.0. UDT_Switch and UDT_Router tables were replaced by UDT_NodeCapability table.

               

              Following query should give you the same result for UDT 2.0 though I didn't test it too much so it's possible you'll have to do some changes. Remove the [IsMonitored] condition to include all ports (both monitored and unmonitored), and remove the [Flag] condition to include deleted (1), missing (2) or deleted and missing (3) ports - or change it as you need.

               

              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

                • Re: SQL Port usage report per device
                  mr.e

                  Hello,

                   

                  First of all, many thanks for posting the SQL script. I ran the SQL report that you posted and it seemed to work.  However, when I attempted to save the report, I received the following error:

                   

                  Detected usage of a macro, which links to a non-existent field in the report. Therefore WebURL, which uses this macro, will not be generated correctly.

                   

                  Missing fields:  NodeID

                   

                  After this, I checked and confirmed that the report does show up in the Reports page. Unfortunately, I am not very skilled in SQL scripting so I did not know how to correct the script in order to avert the problem.

                   

                  Any ideas????

                  • Re: SQL Port usage report per device
                    dclick

                    AS newkidd2 stated, looks great in report writer when I test it, but I also get the Missing field notice when saving.  Im not a SQL guy, so I dont know how to rebuild your query to add the missing NodeID

                      • Re: SQL Port usage report per device
                        michalB

                        Thanks for reporting the issue. It is caused by the fact that the field Caption in Field Formatting tab has a Web URL set and the Web URL contains a macro ${NodeID}. The Web URL converts the text to a hyperlink with the NodeID, but the NodeID is not in the SQL query, and therefore the value cannot be provided. You can either delete the Web URL for the Caption column, or you can add the NodeID column in the SQL query like this:

                         

                        SELECT

                            [N].[NodeID]

                            , [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

                         

                        If you do not want the NodeID column to be visible in the report, you can hide it in the Field Formatting tab by checking the "Hidden Field" checkbox. I tested the report with the query above and it works on my machine