6 Replies Latest reply on Jun 24, 2013 8:14 AM by michalB

    Filter on Top XX resource ?? PercentUsed < 100 ??

    Deltona

      Hi guys,

       

      I'm looking for a way to filter the Top XX resource for % used ports. I need to filter out the nodes that have 100% ports used.

      Is there any easy way to do so?

       

      TIA,

      Deltona

          • Re: Filter on Top XX resource ?? PercentUsed < 100 ??
            michalB

            What exactly is the information you'd like to see? Can you use a custom report to get this?

              • Re: Filter on Top XX resource ?? PercentUsed < 100 ??
                Deltona

                Hi Michal,

                 

                I'd like to have the Top 10 Nodes by Percent Ports Used resource only display nodes that have less than 100 % Ports used.

                Right now, the top 23 nodes in this resource on my system are all 100% used and I would like to filter them away, instead of removing them from the UDT tracker list.

                 

                There is not report that can do this for me.

                 

                Is there any specific filter query I can use to only show me nodes that have less than 100% ports used?


                  • Re: Filter on Top XX resource ?? PercentUsed < 100 ??
                    michalB

                    Deltona,

                    there is no report, but you can create one as a new SQL report. I extracted the SQL query from the resource and tweaked it to filter out the nodes with 100% Port usage.

                     

                    SELECT TOP 10 * FROM

                    (

                    SELECT S.NodeID, N.Status, N.Caption, N.IP_Address, S.LastScan, S.LastSuccessfulScan, S.LastScanResult,

                                    ISNULL(AllPortCount.NodeTotal,0) AS [PortCount],

                                    ISNULL(AllActivePortCount.NodeTotal,0) AS [ActivePortCount],

                     

                     

                                    (ISNULL(AllPortCount.NodeTotal,0) - ISNULL(AllActivePortCount.NodeTotal,0)) AS PortsAvailable, CASE WHEN (ISNULL(AllPortCount.NodeTotal,0) = 0 OR ISNULL(AllActivePortCount.NodeTotal,0) = 0) THEN 0

                                                    ELSE ((ISNULL(AllActivePortCount.NodeTotal,0) * 100 ) / ISNULL(AllPortCount.NodeTotal,0)) END AS [PortPercentUsed]

                     

                     

                                                    FROM Nodes N

                                    INNER JOIN UDT_NodeCapability S ON N.NodeID=S.NodeID AND S.Capability = 2

                                    RIGHT OUTER JOIN (SELECT NodeID, COUNT(PortID) AS NodeTotal FROM UDT_Port  GROUP BY NodeID) AllPortCount ON AllPortCount.NodeID = S.NodeID

                                    RIGHT OUTER JOIN (SELECT NodeID, COUNT(PortID) AS NodeTotal FROM UDT_Port  GROUP BY NodeID, OperationalStatus HAVING OperationalStatus = 1) AllActivePortCount ON AllActivePortCount.NodeID = S.NodeID

                                    WHERE ISNULL(AllPortCount.NodeTotal,0) > 0

                            

                             ) as data

                            

                             Where PortPercentUsed < 100

                             ORDER BY PortPercentUsed DESC, ActivePortCount DESC, PortsAvailable ASC

                     

                     

                    User Report Writer to create a new "Advanced SQL" report, where you can use this query, and tweak the displayed columns.

                     

                    HTH,

                     

                    Michal