13 Replies Latest reply on May 23, 2016 6:39 AM by eduard khudaiberdin

    SDK Extracting Netflow data in charts and tables

    tjensen@mcg-systems.dk

      Hi,

       

      I am trying to build an overall dashboard for 5 specific applications which are defined under applications in netflow. The idea would be to see rates, class of service, CBQoS.

       

      The idea would be to have one page with overall traffic and then tabs for individual sites using the same applications. I am trying to use SWQL in a table and chart to see the trend and development in each.

       

      I am using latest version of SDK, however I keep seeing strange results.

       

      I am doing below:

       

      SELECT

      [flow].[TimeStamp],

      [flow].[ApplicationID],

      [apps].[Name],

      (SUM([flow].[EgressBytes])/60) AS eBytes,

      (SUM([flow].[IngressBytes])/60) AS iBytes,

      (SUM([flow].[TotalBytes])/60) AS TotalBytes

      FROM Orion.NetFlow.Flows AS flow

      INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

      WHERE [apps].[ApplicationID] = xxxxxxx AND ([flow].[TimeStamp] >= (GetUTCDate() - 0.5))

      GROUP BY [flow].[TimeStamp] ,[flow].[ApplicationID]

      ORDER BY [flow].[TimeStamp]  asc

       

      Where xxxxxxx is the application ID.

       

      If I try to limit using more than one appID like WHERE [apps].[ApplicationID] = xxxxxxx OR [apps].[ApplicationID] = xxxxxxx. It suddenly reports all application ids, not just the once I filter.

      At a later stage I would like to only include certain nodes or interfaces.

       

      Am I doing something wrong here

       

      Best Regards

      Troels

        • Re: SDK Extracting Netflow data in charts and tables
          eduard khudaiberdin

          Hello,

           

          I'm not sure why exactly you get strange results. If you could provide result query with several appId filters, then I could tell you more.

           

          However, there are couple of things, that can be improved:

          First of all, it's important to put TimeStamp condition as the first condition in WHERE statement. This will dramatically affect performance of the query.

          Second, if you what to add condition on nodeId, then it should be in second position after TimeStamp condition. This will increase performance as well.

          Third, don't use condition on [apps].[ApplicationID]. Instead you should add condition on [flow].[ApplicaitonId].

          Last, if you are going to put condition on several applications, then it will be much simpler to use IN operator.

           

          So, I would suggest to use following query:

          SELECT

             [flow].[TimeStamp],

             [flow].[ApplicationID],

             [apps].[Name],

             (SUM([flow].[EgressBytes])/60) AS eBytes,

             (SUM([flow].[IngressBytes])/60) AS iBytes,

             (SUM([flow].[TotalBytes])/60) AS TotalBytes

          FROM Orion.NetFlow.Flows AS flow

          INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

          WHERE

             [flow].[TimeStamp] >= (GetUTCDate() - 0.5)

          AND

             [flow].[ApplicationID] IN (xxxx1, xxx2, xxx3)

          GROUP BY [flow].[TimeStamp], [flow].[ApplicationID]

          ORDER BY [flow].[TimeStamp]  asc

           

          I hope it will help you. Can you try it?

           

          Best Regards,

            • Re: SDK Extracting Netflow data in charts and tables
              tjensen@mcg-systems.dk

              Hi Eduard,

               

              Thanks for the info.

               

              I did initially try with IN however as it did not work, I tried the other way around.

               

              I tried with this as per suggestion:

               

              SELECT

                 [flow].[TimeStamp],

                 [flow].[ApplicationID],

                 [apps].[Name],

                 (SUM([flow].[EgressBytes])/60) AS eBytes,

                 (SUM([flow].[IngressBytes])/60) AS iBytes,

                 (SUM([flow].[TotalBytes])/60) AS TotalBytes

              FROM Orion.NetFlow.Flows AS flow

              INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

              WHERE

                 [flow].[TimeStamp] >= (GetUTCDate() - 0.5)

              AND

                 [flow].[ApplicationID] IN (162623,162624,162625,162626,162627)

              GROUP BY [flow].[TimeStamp], [flow].[ApplicationID]

              ORDER BY [flow].[TimeStamp]  asc

               

              Wrong_AppIds.png

               

              Suddenly it starts to display applications that are not part of the search.

               

              Let me know if you need additional information.

               

              Best Regards

              Troels

                • Re: SDK Extracting Netflow data in charts and tables
                  eduard khudaiberdin

                  Are you sure that the screenshot and query are matching? There should be the Name column in 3rd position, but it's not shown on screenshot.

                   

                  I tried the query on my environment, and it works perfectly fine.

                  SWQL Studio - Thwack Question.png

                   

                  But that may depend on the version of NTA as well. I tried it on NTA 4.1.1 RTM and NTA 4.2 RC1 and it works correctly. What version of NTA do you use?

                    • Re: SDK Extracting Netflow data in charts and tables
                      tjensen@mcg-systems.dk

                      Hi Eduard,

                       

                      Yes sorry for that. I removed the name column when I ran it a second time, so yes screenshot is accurate just removed the apps.name.

                       

                      I am using

                      License Production

                      Module Name NetFlow Traffic Analyzer

                      Version 4.1.1

                      Service Pack None

                       

                      The applications I am running against was recently added to the NTA as application mappings. Could time zone or time settings some how affect it? Did you try to scroll down in your result set. Mine did match in the beginning and then suddenly in the middle it starts to show values for other application ids.

                       

                      Best Regards

                      Troels

                        • Re: SDK Extracting Netflow data in charts and tables
                          eduard khudaiberdin

                          Yeah, I tried to scroll down, and it works fine. But I don't have a lot of data in first place.

                           

                          Can you try to do following steps:

                          1. Run application on your FastBit server c:\Program Files (x86)\SolarWinds\Orion\NetFlowTrafficAnalysis\SolarWinds.NetFlow.FastBit.Studio.exe (it will warn you, but you should not execute any commands, that going to change DB, so it should be fine) more time.

                          2. Execute SWIS query one more time

                          3. Type "EXEC GetRecentQueries()" and press button Execute (F5) (if there will be an error, you probably need to restart this application under admin rights)

                          Thwack Question - FSDB Studio.png

                          Here, in result table, you will see recent queries, that were executed on FastBit.

                          You should have something like this:

                          SELECT TimeStamp AS C7, ApplicationID AS C8, SUM(CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END) AS C13, SUM(CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END) AS C14, SUM((CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END+CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END)) AS C15 FROM Flows WHERE (((((TimeStamp>=1463443471) AND (ApplicationID IN (100039,100065,101203))) AND ((Flags&16)=0)) AND ((Flags&3)<>0)))

                          You query probably will be a bit different, but you can distinguish it by ApplicationID condition.

                          Can you post it here with exact SWIS query that you are trying to execute and it's results (so the problem will be easily seen)?

                          1 of 1 people found this helpful
                            • Re: SDK Extracting Netflow data in charts and tables
                              tjensen@mcg-systems.dk

                              Hi Eduard,

                               

                              He is the query and the result. It seems it failed for some reason.

                               

                              SELECT

                              [flow].[TimeStamp],

                              [flow].[ApplicationID],

                              (SUM([flow].[EgressBytes])/60) AS eBytes,

                              (SUM([flow].[IngressBytes])/60) AS iBytes,

                              (SUM([flow].[TotalBytes])/60) AS TotalBytes

                              FROM

                              Orion.NetFlow.Flows AS flow

                              INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

                              WHERE [flow].[TimeStamp] >= (GetUTCDate() - 0.5) AND [flow].[ApplicationID] IN (162623,162624,162625,162626,162627)

                              GROUP BY [flow].[TimeStamp], [flow].[ApplicationID]

                              ORDER BY [flow].[TimeStamp]  asc

                               

                              RESULT:

                               

                              Q@30772016-05-17T15:02:01

                              SELECT

                              [flow].[TimeStamp],

                              [flow].[ApplicationID],

                              (SUM([flow].[EgressBytes])/60) AS eBytes,

                              (SUM([flow].[IngressBytes])/60) AS iBytes,

                              (SUM([flow].[TotalBytes])/60) AS TotalBytes

                              FROM

                              Orion.NetFlow.Flows AS flow

                              INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

                              WHERE [flow].[TimeStamp] >= (GetUTCDate() - 0.5) AND [flow].[ApplicationID] IN (162623,162624,162625,162626,162627)

                              GROUP BY [flow].[TimeStamp], [flow].[ApplicationID]

                              ORDER BY [flow].[TimeStamp]  asc

                               

                               

                              00:00.0 Failed

                              System.ArgumentException: SELECT cannot be parsed or is empty.

                              Parameter name: selectClause 

                              at FastbitCLR.FastbitQuery.VerifySelect(selectClause* select) 

                              at FastbitCLR.FastbitQuery..ctor(IDictionary`2 tables, String selectClause,String fromClause, String whereClause, String orderbyClause, Int32 top, Boolean withRollup, IDictionary`2 columnsTypeMapping, FastbitDatabase database, IEnumerable`1 limitMapValues) 

                              at FastbitCLR.FastbitDatabase.BuildQuery(String selectClause, String fromClause, String whereClause, String orderbyClause, Int32 limit, Boolean withRollup, IDictionary`2 columnsTypeMapping, IEnumerable`1 limitMapValues) 

                              at SolarWinds.Netflow.FastBit.Server.Core.FastbitQueryBuilder.BuildQuery(FastBitSerializableQuery fastBitQuery, FastBitEmbeddedConnection connection) 

                              at SolarWinds.Netflow.FastBit.Server.Core.FastBitQueryTask.Run() 

                              at FastbitCLR.Util.ExceptionHandler<FastbitCLR::Util::NativePolicyConvertToManaged\,FastbitCLR::Util::ManagedPolicyRethrow\,FastbitCLR::Util::LoggingPolicyError>.Try(String jobName, GuardedJob job) 

                              at FastbitCLR.FastbitTaskManager.Run(FastbitRunnableTask task)

                               

                              Best Regards

                              Troels

                                • Re: SDK Extracting Netflow data in charts and tables
                                  eduard khudaiberdin

                                  I think you misunderstood me.

                                   

                                  You need to execute query in SWIS Studio

                                  SELECT

                                  [flow].[TimeStamp],

                                  [flow].[ApplicationID],

                                  (SUM([flow].[EgressBytes])/60) AS eBytes,

                                  (SUM([flow].[IngressBytes])/60) AS iBytes,

                                  (SUM([flow].[TotalBytes])/60) AS TotalBytes

                                  FROM

                                  Orion.NetFlow.Flows AS flow

                                  INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID]

                                  WHERE [flow].[TimeStamp] >= (GetUTCDate() - 0.5) AND [flow].[ApplicationID] IN (162623,162624,162625,162626,162627)

                                  GROUP BY [flow].[TimeStamp], [flow].[ApplicationID]

                                  ORDER BY [flow].[TimeStamp]  asc

                                   

                                   

                                  And then open FSDB Studio and copy query similar to

                                  SELECT TimeStamp AS C6, ApplicationID AS C7, SUM(CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END) AS C12, SUM(CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END) AS C13, SUM((CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END+CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END)) AS C14

                                  FROM Flows

                                  WHERE (((((TimeStamp>=1463452892) AND (ApplicationID IN (162623,162624,162625,162626,162627))) AND ((Flags&16)=0)) AND ((Flags&3)<>0)))

                                   

                                  from FastBit Studio after you execute EXEC GetRecentQueries()

                                  Thwack Question - FSDB Studio.png

                                   

                                  What you just sent me is execution of SWIS query in FSDB Studio.

                                    • Re: SDK Extracting Netflow data in charts and tables
                                      tjensen@mcg-systems.dk

                                      Hi Eduard,

                                       

                                      Ok, hope this is correct.

                                      Here is what I did.

                                       

                                      Ran this query from Studio.

                                       

                                      SELECT

                                      [flow].[TimeStamp],

                                      [flow].[ApplicationID],

                                      (SUM([flow].[EgressBytes])/60) AS eBytes,

                                      (SUM([flow].[IngressBytes])/60) AS iBytes,

                                      (SUM([flow].[TotalBytes])/60) AS TotalBytes

                                      FROM

                                      Orion.NetFlow.Flows AS flow

                                      INNER JOIN Orion.Netflow.Applications AS apps ON [flow].[ApplicationID] = [apps].[ApplicationID] 

                                      WHERE [flow].[TimeStamp] >= (GetUTCDate() - 0.5) AND [flow].[ApplicationID] IN (162623,162624,162625,162626,162627)

                                      GROUP BY [flow].[TimeStamp], [flow].[ApplicationID] 

                                      ORDER BY [flow].[TimeStamp]  asc

                                       

                                      Then ran this query from Netflow server studio.

                                       

                                      SELECT TimeStamp AS C6, ApplicationID AS C7, SUM(CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END) AS C12, SUM(CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END) AS C13, SUM((CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END+CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END)) AS C14 FROM Flows WHERE (((((TimeStamp>=1463452892) AND (ApplicationID IN (162623,162624,162625,162626,162627))) AND ((Flags&16)=0)) AND ((Flags&3)<>0)))

                                       

                                      Then ran this query from Netflow server studio.

                                      EXEC GetRecentQueries()

                                       

                                      This is the result from that query after EXEC GetRecentQueries()

                                       

                                      "Q@4232",

                                      2016-05-18T08:52:13,

                                      "SELECT TimeStamp AS C6,

                                      ApplicationID AS C7,

                                      SUM(CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END) AS C12,

                                      SUM(CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END) AS C13,

                                      SUM((CASE WHEN ((Flags&1)<>0) THEN Bytes ELSE 0 END+CASE WHEN ((Flags&2)<>0) THEN Bytes ELSE 0 END)) AS C14

                                      FROM Flows 

                                      WHERE (((((TimeStamp>=1463452892) AND (ApplicationID IN (162623,162624,162625,162626,162627)))

                                      AND ((Flags&16)=0)) AND ((Flags&3)<>0)))

                                      ",00:00:08.2840328,"Finished",

                                      "Total: 00:00:08.2970000

                                      Loading partitions: 0,00% 00:00:00

                                      Select: 100,00% 00:00:08.2970000

                                      Partition processing: 99,61% 00:00:08.2650000

                                      Select verification : 0,00% 00:00:00

                                      Where: 25,13% 00:00:02.0770000

                                      Retrieving records: 66,17% 00:00:05.4690000

                                      Data access: 50,27% 00:00:02.7490000

                                      Filling: 0,00% 00:00:00

                                      Data processing: 0,00% 00:00:00

                                      Pivot: 0,00% 00:00:00

                                      Pivot evaluate : 0,00% 00:00:00

                                      GroupByA: 6,59% 00:00:00.5450000

                                      Partial merge: 1,15% 00:00:00.0950000

                                      Others: 0,96% 00:00:00.0790000

                                      Final merge: 0,19% 00:00:00.0160000

                                      Others: 0,19% 00:00:00.0160000

                                      Type mapping: 0,00% 00:00:00

                                      Reader: 0,00% 00:00:00

                                      Others: 0,00% 00:00:00  

                                      Release lock: 0,00% 00:00:00 

                                      Shared: 0,55% 00:00:00.0460000

                                      Private: 0,00% 00:00:00

                                      Native: 0,58% 00:00:00.0480000",

                                      "Returned 22080 rows."

                                       

                                      Is this what you are looking for?

                                       

                                      Best Regards

                                      Troels

                                        • Re: SDK Extracting Netflow data in charts and tables
                                          eduard khudaiberdin

                                          Hello,

                                           

                                          Yeah, that's what I needed.

                                           

                                          It looks like the query propagated from SWIS to FastBit is correct. I think the problem can be because of corrupted indexes on FastBit. I can suggested to contact SW support or try to rebuild indexes yourself. To do so, you need to open FastBit Studio again and execute next query

                                          EXEC BuildIndexes('Flows', '', '')

                                          This will rebuild all indexes on Flows table. It can take some time depending on amount of data you have. I believe this should be sufficient enough to fix it.

                                            • Re: SDK Extracting Netflow data in charts and tables
                                              tjensen@mcg-systems.dk

                                              Hi Eduard,

                                               

                                              Thanks for the info.

                                               

                                              Yes also validate with support :-).

                                               

                                              Will try and rebuild index over the weekend.

                                               

                                              Best Regards

                                              Troels

                                                • Re: SDK Extracting Netflow data in charts and tables
                                                  eduard khudaiberdin

                                                  Hello,

                                                   

                                                  Sure, hope it helps.

                                                  Please update the thread after verification, I would like to know the results. Thank you.

                                                    • Re: SDK Extracting Netflow data in charts and tables
                                                      tjensen@mcg-systems.dk

                                                      Hi Eduard,

                                                       

                                                      I got this from the log today:

                                                       

                                                      2016-05-20 14:04:21,604 - Main form has been created.

                                                      2016-05-20 14:05:18,610 - Database: 'net.pipe://localhost/orion/nta/FlowStorageService' connected

                                                      2016-05-20 14:05:21,560 - Executing command: "EXEC BuildIndexes('Flows', '', '')"

                                                      2016-05-20 15:05:23,571 - Error while performing async action: The request channel timed out while waiting for a reply after 00:59:59.9990023. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.

                                                      2016-05-20 15:05:23,576 - Error while Quering: The request channel timed out while waiting for a reply after 00:59:59.9990023. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.

                                                       

                                                      It seemed to timeout after an hour. Do I need to increase it or will it run in the background?

                                                       

                                                      It did seem to solve the issue if I only select from the past couple of days like (GetUTCDate() - 2.5) however still shows incorrect values if using (GetUTCDate() - 7)

                                                       

                                                      Best Regards

                                                      Troels

                                                        • Re: SDK Extracting Netflow data in charts and tables
                                                          eduard khudaiberdin

                                                          Hello,

                                                          It seems that Indexes were partially fixed. The rebuild process starts from the latest day and process days in sequence. It seems several of your latest days were already fixed, that's why you have correct results when you use (GetUTCDate() - 2.5). I think all new flows will be in correct indexes from no one. So, the problem will be fixed eventually (because of retention period).

                                                           

                                                          If you still want to fix your historical data you should ask SW support to help you to increase the timeout.