19 Replies Latest reply on Feb 14, 2017 10:14 AM by whitwa

    Top 5 Applications

    whitwa

      Hi,

       

      We would like to obtain the Top 5 Applications data via the SolarWinds API.

       

      Note we want the actual data and not to embed the SolarWinds widget in our user interface.

       

      Please can you assist.

       

       

      Regards,

      Andrew

        • Re: Top 5 Applications
          derhally

          What version of NTA are you running?

          • Re: Top 5 Applications
            derhally

            The following query gives you the TOP 5 applications for the last hour last hour based on total bytes.

             

            SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets
            FROM Orion.Netflow.Flows
            WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > AddHour(-1, GETUTCDATE())
            GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName
            ORDER BY TotalBytes DESC
            
            1 of 1 people found this helpful
              • Re: Top 5 Applications
                whitwa

                Thanks for that.

                 

                Unfortunately when I try to run that query in SWQL Studio I get a timeout:

                 

                 

                This seems to happen even when running the most basic of queries against the Orion.Netflow.Flows table.

                 

                Any ideas what could be causing this issue?

                  • Re: Top 5 Applications
                    tdanner

                    The problem may be the use of the "ADDHOUR" function. See the note at the bottom of this page: NTA 4.0 Entity Model · solarwinds/OrionSDK Wiki · GitHub

                     

                    Try doing the date math using + and - fractional days instead of the functions. Like this:

                     

                    WHERE Flows.ObservationTimestamp > GETUTCDATE() - 0.417

                      • Re: Top 5 Applications
                        whitwa

                        Unfortunately that hasn't made any difference. The following query incorporating your suggestion still times out after 2 minutes:

                         

                        SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

                        FROM Orion.Netflow.Flows

                        WHERE Flows.ProtocolID IN (6, 17) AND (Flows.ObservationTimestamp > GETUTCDATE() - 0.417)

                        GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

                        ORDER BY TotalBytes DESC

                          • Re: Top 5 Applications
                            derhally

                            It may be a pain but can you try putting the date as a string literal instead of GETUTCDATE()

                             

                            For example,

                             

                            SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets  
                            FROM Orion.Netflow.Flows  
                            WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > '2017-01-30 00:00:00'
                            GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName  
                            ORDER BY TotalBytes DESC  
                            WITH QUERYPLAN
                            

                             

                            Note that the should be in UTC.

                              • Re: Top 5 Applications
                                whitwa

                                This latest query doesn't time out but still takes approx. 1 min 16 seconds to execute in SWQL Studio.

                                 

                                I should point out that I don't see any such delay on the dashboard where the screenshot in the original post was taken.

                                 

                                This sounds like some kind of performance issue perhaps? Maybe the dashboard is getting the data directly from the SQL database as opposed to via the Orion API/SWQL?

                                  • Re: Top 5 Applications
                                    jeffrey.kohn

                                    HI,

                                     

                                    I spoke with one of the developers and they mentioned that there is a performance issue with the Fastbit database when Timestamp is not the first condition in the where clause. Can you try changing the order of the WHERE clause to see if that makes a difference with the query performance?

                                      • Re: Top 5 Applications
                                        whitwa

                                        Hi Jeffrey,

                                         

                                        I tried altering the order of the WHERE clause as suggested but it's not resolved the issue.

                                         

                                        The following 2 queries (before and after altering the WHERE clause) are taking over 20 seconds:

                                         

                                        SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

                                        FROM Orion.Netflow.Flows

                                        WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > '2017-01-30'

                                        GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

                                        ORDER BY TotalBytes DESC

                                         

                                        SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

                                        FROM Orion.Netflow.Flows

                                        WHERE Flows.ObservationTimeStamp > '2017-01-30' AND Flows.ProtocolID IN (6, 17)

                                        GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

                                        ORDER BY TotalBytes DESC

                                         

                                        This seems quite inconsistent as when I ran the top query yesterday it took 1 min 16 seconds instead of 20 seconds today?

                                         

                                        If I alter the date (I need the last 7 days ultimately) then I encounter the timeout in SWQL Studio.

                                          • Re: Top 5 Applications
                                            jeffrey.kohn

                                            Give this query a try and see how it performs compared to the resource. The resource is actually using a legacy SWIS entity that uses a cryptic syntax, but this should be similar to the query that gets generated under the hood by the resource.

                                             

                                            SELECT

                                                AppID,

                                                AA.Name AS ApplicationName,

                                                TotalBytes,

                                                TotalPackets,

                                                TotalBytesIngress, TotalBytesEgress, TotalPacketsIngress, TotalPacketsEgress

                                              FROM

                                              (

                                                  SELECT TOP 5

                                                    ApplicationID AS AppID,

                                                    SUM(IngressBytes) AS TotalBytesIngress, SUM(IngressPackets) AS TotalPacketsIngress,

                                                    SUM(EgressBytes) AS TotalBytesEgress, SUM(EgressPackets) AS TotalPacketsEgress,

                                                    SUM(TotalBytes) AS TotalBytes,

                                                    SUM(TotalPackets) AS TotalPackets

                                                  FROM Orion.NetFlow.FlowsByInterface AS Flows

                                                  WHERE TimeStamp >= '2015-02-01'

                                                  GROUP BY GROUPING SETS ((AppID))

                                                  ORDER BY TotalBytes DESC

                                              )

                                              LEFT JOIN Orion.NetFlow.Applications AA ON AA.ApplicationID = AppId

                                                ORDER BY TotalBytes DESC, AppID ASC

                                              • Re: Top 5 Applications
                                                whitwa

                                                Thanks - but I don't see a Orion.NetFlow.FlowsByInterface table and the query won't run.

                                                 

                                                  • Re: Top 5 Applications
                                                    jeffrey.kohn

                                                    Sorry, FlowsByInterface was introduced in 4.2.x, I missed seeing up above that you're running an earlier version. You can just change it to Orion.Netflow.Flows. You may also need to replace the GROUPING SETS operator with regular GROUP BY AppID.

                                                      • Re: Top 5 Applications
                                                        whitwa

                                                        Hi Jeffrey,

                                                         

                                                        Thanks but I'm still seeing the timeout error, even when running just the inner query and reducing the timestamp as follows:

                                                         

                                                        SELECT TOP 5 ApplicationID AS AppID, SUM(TotalBytes) AS TotalBytes

                                                        FROM Orion.NetFlow.Flows AS Flows

                                                        WHERE TimeStamp >= '2017-01-27'

                                                        GROUP BY AppID

                                                        ORDER BY TotalBytes DESC

                                                          • Re: Top 5 Applications
                                                            jeffrey.kohn

                                                            Sorry to hear it's still not working, I thought that query should have been pretty similar to what gets generated under the hood by the legacy query syntax that the resource uses. Here's the actual query the resource uses with the legacy syntax. I didn't actually test this against a 4.1.x installation but you can give it a try on your deployment

                                                             

                                                            SELECT [AppID], [ApplicationName], [MapTo], [ProtocolName], [NodeID], [InterfaceID], [TotalBytes], [TotalPackets], [TotalBytesIngress], [TotalBytesEgress], [TotalPacketsIngress], [TotalPacketsEgress]

                                                            FROM

                                                            [Orion.NetFlow].[ApplicationsTop](Filter='NSF:TD:2017-01-31T14:32:00~2017-01-31T15:32:00,1,True,False;FD:Both', Limit=5, Rx=True, Tx=True, TopKey='')

                                                              • Re: Top 5 Applications
                                                                whitwa

                                                                Hi Jeffrey,

                                                                 

                                                                Thanks but I don't seem to have the Orion.NetFlow.ApplicationsTop table/object?

                                                                 

                                                                 

                                                                These are all of the Orion.NetFlow entities I can see on our system:

                                                                • Re: Top 5 Applications
                                                                  whitwa

                                                                  Hi Jeffrey,

                                                                   

                                                                  Do you have any further suggestions on this as we are struggling on this issue.

                                                                   

                                                                  Regards,

                                                                  Andrew

                                                                    • Re: Top 5 Applications
                                                                      jeffrey.kohn

                                                                      The last provided query should work, that's what the resource is using. If that entity is not showing as available you may need to connect to the v2 Information service (Server type "Orion (v2)" in the 'Connect to Information Service' dialog) . On my 4.2.x install ApplicationsTop is listed in the v3 SWIS schema, but I think in 4.1 it may not have been converted to v3 yet. In that case you'd have to connect to the v2 Information Service to use the query.

                                                                        • Re: Top 5 Applications
                                                                          whitwa

                                                                          Thanks Jeffrey. I can confirm that I can see the Orion.NetFlow.ApplicationsTop entity when connecting via the V2 information service.

                                                                           

                                                                          Unfortunately the query is still performing extremely poorly or just timing out.

                                                                           

                                                                          Running the exact query you provided takes around 56 seconds. Altering the dates to be the last 7 days (as per our requirements) results in the query timing out after 2 minutes in SWQL Studio.

                                                                           

                                                                          How long does that query take for you or what is a realistic expectation here?

                                                                           

                                                                          Do you have any other ideas? Could it be some sort of environmental issue? Maybe the fact we are running NTA 4.1?