6 Replies Latest reply on Feb 2, 2017 6:34 AM by Deltona

    Custom UDT Summary and Details View Report

    Deltona

      Hi guys,

       

      I'm trying to build two new reports for UDT, but I'm getting stuck with the more advanced SQL queries that lookup date.

      I hope one of you can help

       

      1. A Summary View report, much like the Top 10 Nodes by Percent Ports Used resource, that groups the Nodes by <custom_Property> AND only lists historical "Archive" information for ports that have never been seen.

      What I wish to end up having is an overview of % Ports Used based on Active Ports Vs Never Used ports. Alternatively, Active Ports Vs Ports Last Seen (haven't been used in at least 90 days).

       

      Top 10 Nodes by # of Never Seen Ports

      <custom_property>NodePorts CountPorts UsedPorts Never Used% Ports Used (ASC)
      Datacenter ARTR0171614
      FW01112918
      SW0193633
      SW0227151255
      Datacenter BSW0157342359
      SW02251510

      60

       

      2. The same type of query for use in a Node Details view, where the query is ${NodeID} dependant.

       

      What I have so far is losely based off njoylif's work found here: Re: UDT report for used/unused ports per device

      I can't figure out how to call the never seen/last seen -90 bit

       

      I'm using UDT v3.2.4

      Is this doable?

       

      Regards,

      Deltona

        • Re: Custom UDT Summary and Details View Report
          ctlswadmin

          Hi Deltona, I haven't played around with the UDT tables in a while now. Can you confirm if "Last Scan" is considered last seen ? That's the only date reference I found on njoylif's query. Are you using the same tables as they are ?

           

          If you're using the same query, or similar to add this to the end.

           

          LastScan > dateadd(DAY,-90,getdate())

           

          So the query would look like this.

           

          WHERE (ISNULL([PortsTotal].[PortCount], 0) > 0)

          AND LastScan > dateadd(DAY,-90,getdate())

          ORDER BY [% PortsUsed] DESC, [ActivePortCount] DESC, [UnusedPorts] ASC

            • Re: Custom UDT Summary and Details View Report
              Deltona

              Hi,

              Thanks for the reply.

               

              I am not sure whether to base it on last scan or last seen because I am not certain about the difference or whether a Last Seen exists. Logically it would have to be last seen but if this isn't logged in the database then how would this be done? Are entries overwritten?

              This is the query that I'm using now. Bold and italic is what I've added/modified.

               

              SELECT

                  [N].[Caption] AS Caption, Country

                  , 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)
              AND LastScan > dateadd(DAY,-90,getdate())
              ORDER BY [% PortsUsed] ASC

               

              It's not displaying what I expected it would display, probably because it is based on LastScan.

            • Re: Custom UDT Summary and Details View Report
              njoylif

              Deltona, did that work for you?  if not PM or LMK and I'll take a look...been a while so have to refresh