search

solarwinds / OrionSDK

SDK for the SolarWinds Orion platform, including tools, documentation, and samples in PowerShell, C#, Go, Perl, and Java.
https://thwack.com/OrionSDK
Apache License 2.0
401 stars 144 forks source link

Data not accurate #187

Closed junaid360 closed 5 years ago

junaid360 commented 5 years ago

Hello, I need to SUM 2 interfaces to see the total. but not able to. When I pull data from solarwinds in grafana, I see that the data is not pulled correctly.

mrxinu commented 5 years ago

Are you sure you meant to include the ObservationTimeStamp in your SELECT and GROUP BY clauses? That would produce a SUM for each timestamp instead of a SUM of the entire range.

junaid360 commented 5 years ago

Yes. As you can see the screen shot. I dont know if its a bug or what.

Regards, Mirza

On Jul 17, 2019, at 5:58 PM, Steven Klassen notifications@github.com wrote:

Are you sure you meant to include the ObservationTimeStamp in your SELECT and GROUP BY clauses? That would produce a SUM for each timestamp instead of a SUM of the entire range.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

mrxinu commented 5 years ago

Can you show me the results from the query?

junaid360 commented 5 years ago

Can you show me the results from the query?

what do you think?

mrxinu commented 5 years ago

How many data points do you expect to share an ObservationTime value?

junaid360 commented 5 years ago

Im trying to sum 2 interfaces max inbps.

On Jul 19, 2019, at 12:53 AM, Steven Klassen notifications@github.com wrote:

How many data points do you expect to share an ObservationTime value?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

mrxinu commented 5 years ago

I'm not sure why you're pulling InMaxBps and then calling it OutMaxBps.

image

But, that aside, if you want to use an aggregate like SUM on data you need to group it somehow and including the ObservationTimeStamp field prevents you from doing that unless you have multiple samples with the exact same timestamp. Since SolarWinds treats all your nodes & interfaces as separate entities there's no guarantee that an interface on device A and an interface on device B are going to be sampled at the same time.

This query combines the traffic from two different nodes/interfaces between two points in time and returns their sum. It's a single value and not very interesting for graphing so I'm not sure what you're shooting for with the SUM.

SELECT SUM(Traffic.OutMaxBps) AS SumOfOutMaxBps
FROM Orion.NPM.InterfaceTraffic AS Traffic
WHERE Traffic.ObservationTimeStamp BETWEEN $from AND $to
AND
(Traffic.NodeID = 1 AND Traffic.InterfaceID = 1238
OR Traffic.NodeID = 3 AND Traffic.InterfaceID = 1435)

How many rows/columns are you expecting to get from the query?

junaid360 commented 5 years ago

Hi Steven, The MAX Inmaxbps as outmaxbps is just things I was trying here and there.

Should be SUM inmaxbps as inmaxbps.

I need to graph inbps max from 2 interfaces
If interface A is 5gb and interface B is 5gb, I want to graph and show 10gb traffic.

Thanks.

On Jul 19, 2019, at 9:49 AM, Steven Klassen notifications@github.com wrote:

I'm not sure why you're pulling InMaxBps and then calling it OutMaxBps.

But, that aside, if you want to use an aggregate like SUM on data you need to group it somehow and including the ObservationTimeStamp field prevents you from doing that unless you have multiple samples with the exact same timestamp. Since SolarWinds treats all your nodes & interfaces as separate entities there's no guarantee that an interface on device A and an interface on device B are going to be sampled at the same time.

This query combines the traffic from two different nodes/interfaces between two points in time and returns their sum. It's a single value and not very interesting for graphing so I'm not sure what you're shooting for with the SUM.

SELECT SUM(Traffic.OutMaxBps) AS SumOfOutMaxBps FROM Orion.NPM.InterfaceTraffic AS Traffic WHERE Traffic.ObservationTimeStamp BETWEEN $from AND $to AND (Traffic.NodeID = 1 AND Traffic.InterfaceID = 1238 OR Traffic.NodeID = 3 AND Traffic.InterfaceID = 1435) How many rows/columns are you expecting to get from the query?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

nothrow commented 5 years ago

Steven, he is using DOWNSAMPLE() method on the ObservationTimestamp, (even in group by) which does neat magic - it downsamples, by default on 5 minutes, so you can see SUM of the InMaxBps of all samples in the 5min window.

@junaid360, Can you please elaborate on what do you expect to see?

InMaxBps is maximum BPS seen during the window. SUM of those values doesn't make much sense.

What I believe would make sense is something along (I haven't tested it, I don't have working SWIS at this moment)

SELECT SUM(s.Traffic) AS SumOfTraffic FROM 
 (
   SELECT MAX(i.InMaxBps) AS Traffic, i.InterfaceID
   FROM Orion.NPM.InterfaceTraffic i
   WHERE i.ObservationTimestamp BETWEEN $from AND $to
   GROUP BY i.InterfaceID
   WHERE i.InterfaceID IN (1238, 1435)
 ) s

Where the inner query selects largest InMaxBps per each interface, and then the SUM computes the overall sum.

junaid360 commented 5 years ago

What I want is to show total values of 2 interfaces as one. Sum or stack. Im not able to.

I need to graph inbps max from 2 interfaces
If interface A is 5gb and interface B is 5gb, I want to graph and show 10gb traffic.

With my current statement im not able to. Maybe my statement is wrong.

On Jul 19, 2019, at 7:04 PM, Víťa Tauer notifications@github.com wrote:

Steven, he is using DOWNSAMPLE() method on the ObservationTimestamp, (even in group by) which does neat magic - it downsamples, by default on 5 minutes, so you can see SUM of the InMaxBps of all samples in the 5min window.

junaid360, Can you please elaborate on what do you expect to see?

InMaxBps is maximum BPS seen during the window. SUM of those values doesn't make much sense.

What I believe would make sense is something along (I haven't tested it, I don't have working SWIS at this moment)

SELECT SUM(s.Traffic) AS SumOfTraffic FROM ( SELECT MAX(i.InMaxBps) AS Traffic, i.InterfaceID FROM Orion.NPM.InterfaceTraffic i WHERE i.ObservationTimestamp BETWEEN $from AND $to GROUP BY i.InterfaceID WHERE i.InterfaceID IN (1238, 1435) ) s Where the inner query selects largest InMaxBps per each interface, and then the SUM computes the overall sum.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

nothrow commented 5 years ago

As I've said, SUM over BPS does not make sense at all - the more samples you'd have, the higher the BPS would be. SUM makes sense on number of transferred bytes, for example.

Anyway, I sent the query just as an inspiration, I did not check if it actually is syntactically correct.

Here you are with fixed version.

SELECT SUM(s.Traffic) AS SumOfTraffic FROM 
 (
   SELECT MAX(i.InMaxBps) AS Traffic, i.InterfaceID
   FROM Orion.NPM.InterfaceTraffic i
   WHERE i.ObservationTimestamp BETWEEN $from AND $to AND i.InterfaceID IN (1238, 1435)
   GROUP BY i.InterfaceID
 ) s

Output of this is just one number, the sum of highest incoming traffic for the two interfaces. It can be used in pie chart.

If you need to have something stacked, let's say per hour, I'd go with query like

SELECT SUM(s.Traffic) AS SumOfTraffic, s.Time FROM 
 (
   SELECT MAX(i.InMaxBps) AS Traffic, DOWNSAMPLE(i.ObservationTimestamp) AS Time, i.InterfaceID
   FROM Orion.NPM.InterfaceTraffic i
   WHERE i.ObservationTimestamp BETWEEN $from AND $to AND i.InterfaceID IN (1238, 1435)
   GROUP BY DOWNSAMPLE(i.ObservationTimestamp), i.InterfaceID
 ) s
GROUP BY s.Time
ORDER BY s.Time ASC
WITH GRANULARITY 1h
nothrow commented 5 years ago

I operate under assumption that what you need is to show sum of highest incoming traffic rates for two interfaces.

junaid360 commented 5 years ago

I operate under assumption that what you need is to show sum of highest incoming traffic rates for two interfaces.

Yes, I'm trying to show it as Guage, over 24 hours highest inbps for 2 interfaces.

nothrow commented 5 years ago

@junaid360 , I see you closed this issue. Can I please ask you for confirming that the issue was solved?

junaid360 commented 5 years ago

Hi, Didn’t get much help. Hence I rest my case.

Regards, Mirza

On Jul 21, 2019, at 11:56 PM, Víťa Tauer notifications@github.com wrote:

@junaid360 , I see you closed this issue. Can I please ask you for confirming that the issue was solved?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.