4 Replies Latest reply on Oct 7, 2015 7:11 AM by bmont

    SQL vs SWQL Database

    bmont

      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

      IDDateTimeIn AveragebpsOut Averagebps
      446910/6/2015 4:00 AM373210.469325500.031
      446910/6/2015 4:01 AM310744.3421875.438
      446910/6/2015 4:02 AM293931.3316834.438
      446910/6/2015 4:03 AM1246795.38295516.7
      446910/6/2015 4:04 AM316399.4383283.8
      446910/6/2015 4:05 AM337346.563413797.344
      446910/6/2015 4:06 AM207001.875254501.656
      446910/6/2015 4:07 AM254659.672285642.938
      446910/6/2015 4:08 AM332758.344379863.469
      446910/6/2015 4:09 AM412690397863.625
      446910/6/2015 4:10 AM301817.125372958.031


      SWQL   

      IDDateTimeInAveragebpsOutAveragebps
      44692015-10-06T04:00:18.82000001670156658171.9
      44692015-10-06T04:01:18.82000002436784656226.1
      44692015-10-06T04:02:18.82000002134289511995.4
      44692015-10-06T04:03:18.80300003701352457958.6
      44692015-10-06T04:04:18.82000001703175405209.4
      44692015-10-06T04:05:18.82000001614091442200.6
      44692015-10-06T04:06:18.82000001348071393996.2
      44692015-10-06T04:07:18.8200000239271.5244385
      44692015-10-06T04:08:18.8200000253873.7327525.9
      44692015-10-06T04:09:18.8200000437078.1444289.2
      44692015-10-06T04:10:18.8200000248555.1294039.2
        • Re: SQL vs SWQL Database
          zackm

          can you post both of your queries please?

            • Re: SQL vs SWQL Database
              bmont

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

              SQL:

              SELECTInterfaceID
              ,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