SQL vs SWQL Database

I wrote a query in SQL that provides bandwidth usage of interfaces during a specific time of day. We have multiple people manage different devices so we use account limitations for ease of use but the SQL report did not take into consideration these account limits. Because of this, I rewrote the query in SWQL, which did allow users to only see this report for the devices that they managed. What I found however makes no sense to me so I thought I would post it to the forum, the data is different. Does anyone know why it would be different?

Here is a sample of the output from the same InterfaceID in SQL and then SWQL,

SQL

ID	DateTime	In Averagebps	Out Averagebps
4469	10/6/2015 4:00 AM	373210.469	325500.031
4469	10/6/2015 4:01 AM	310744.3	421875.438
4469	10/6/2015 4:02 AM	293931.3	316834.438
4469	10/6/2015 4:03 AM	1246795.38	295516.7
4469	10/6/2015 4:04 AM	316399.4	383283.8
4469	10/6/2015 4:05 AM	337346.563	413797.344
4469	10/6/2015 4:06 AM	207001.875	254501.656
4469	10/6/2015 4:07 AM	254659.672	285642.938
4469	10/6/2015 4:08 AM	332758.344	379863.469
4469	10/6/2015 4:09 AM	412690	397863.625
4469	10/6/2015 4:10 AM	301817.125	372958.031

SWQL

ID	DateTime	InAveragebps	OutAveragebps
4469	2015-10-06T04:00:18.8200000	1670156	658171.9
4469	2015-10-06T04:01:18.8200000	2436784	656226.1
4469	2015-10-06T04:02:18.8200000	2134289	511995.4
4469	2015-10-06T04:03:18.8030000	3701352	457958.6
4469	2015-10-06T04:04:18.8200000	1703175	405209.4
4469	2015-10-06T04:05:18.8200000	1614091	442200.6
4469	2015-10-06T04:06:18.8200000	1348071	393996.2
4469	2015-10-06T04:07:18.8200000	239271.5	244385
4469	2015-10-06T04:08:18.8200000	253873.7	327525.9
4469	2015-10-06T04:09:18.8200000	437078.1	444289.2
4469	2015-10-06T04:10:18.8200000	248555.1	294039.2

Find more posts tagged with

solarwinds database

sql_to_swql

SWQL

sql_dba

sql query

Accepted answers

zackm

You nailed it.

SWQL with both Times

SELECTInterfaceID,DateTime AS [DateTime_UTC],ToLocal(DateTime) AS [DateTime_Local],InAveragebps,OutAveragebpsFROM Orion.NPM.InterfaceTrafficWHERE InterfaceID = 4469ORDER BY DateTime DESC

SQL with both Times

SELECTInterfaceID,DateTime 'DateTime_Local',DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), DateTime) 'DateTime_UTC',In_Averagebps,Out_AveragebpsFROM [dbo].[InterfaceTraffic]WHERE Cast(DateTime AS Date) = '10/6/2015'AND InterfaceID  = 4469ORDER BY DateTime DESC

The SWQL table stores the times in UTC.

Good catch!

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

All comments

zackm

can you post both of your queries please?

bmont

Here are the two queries I used to get the data above.

SQL:

SELECT	InterfaceID
	,Datetime
	,In_Averagebps
	,Out_Averagebps

FROM [dbo].[InterfaceTraffic]

Where InterfaceID = 4469

AND Cast(DateTime AS Date) = '10/6/2015'

SWQL:

SELECT InterfaceID, DateTime, InAveragebps, OutAveragebps

FROM Orion.NPM.InterfaceTraffic

WHERE InterfaceID = 4469

I think I just figured out the problem though. If you possibly confirm, that would be great. I think the issue is a time zone problem. I just realized that my data from the SWQL was starting at 4:00 AM(I'm EST) and when I looked at the same data at 8:00 AM it looked the same as the SQL at 4:00 AM

zackm

You nailed it.

SWQL with both Times

SELECTInterfaceID,DateTime AS [DateTime_UTC],ToLocal(DateTime) AS [DateTime_Local],InAveragebps,OutAveragebpsFROM Orion.NPM.InterfaceTrafficWHERE InterfaceID = 4469ORDER BY DateTime DESC

SQL with both Times

SELECTInterfaceID,DateTime 'DateTime_Local',DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), DateTime) 'DateTime_UTC',In_Averagebps,Out_AveragebpsFROM [dbo].[InterfaceTraffic]WHERE Cast(DateTime AS Date) = '10/6/2015'AND InterfaceID  = 4469ORDER BY DateTime DESC

The SWQL table stores the times in UTC.

Good catch!

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

bmont

Perfect, thanks for the help. Figuring out how to covert them was my next step. Really appreciate it.