SDK Extracting Netflow data in charts and tables

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,

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

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

Let me know if you need additional information.

Best Regards

Troels

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.

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?

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

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)

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)?

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:

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

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()

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

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

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.

Hi Eduard,

Thanks for the info.

Yes also validate with support :-).

Will try and rebuild index over the weekend.

Best Regards

Troels