OK so we have a requirement for "Last Week" instead of being Sunday to Saturday, the customers "Week" is Monday to Sunday. So I am doing the query for data via a a SQL Query.
Here is my query (This is a MSSQL Query)...
DECLARE @StartTime DATETIME DECLARE @EndTime DATETIME DECLARE @PMStartTime DATETIME DECLARE @PMEndTime DATETIME DECLARE @DeviceName VARCHAR(255) DECLARE @CustomerName VARCHAR(255) SET @StartTime = DATEADD(DAY, 0, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, -1, GETDATE())) - 1, 0)) --Start Last Week on Monday --SET @StartTime = DATEADD(DAY, 1, ${FromTime}) -- Week Start on Monday SET @EndTime = DATEADD(SECOND, -1, DATEADD(DAY, 7, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, -1, GETDATE())) - 1, 0))) -- End Last Week on Sunday --SET @EndTime = DATEADD(DAY, 1, DATEADD(SECOND, -1, ${ToTime})) -- Week End on Sunday SET @CustomerName = 'CustomerName' SET @DeviceName = 'NetWork-Device' ;WITH amonth(day) AS ( SELECT CAST(@StartTime AS DATE) AS day UNION ALL SELECT DATEADD(DAY, 1 , day) FROM amonth WHERE CAST(day AS DATE) < CAST(@EndTime AS DATE) ) SELECT [N].[Caption] ,[I].[Caption] AS [Interface] ,ISNULL(AVG(([IT].[In_Averagebps]/[I].[InBandwidth])) * 100, 0) AS [Avg Util In %] ,ISNULL(AVG(([IT].[Out_Averagebps]/[I].[OutBandwidth])) * 100, 0) AS [Avg Util Out %] ,ISNULL(AVG((([IT].[In_Averagebps] + [IT].[Out_Averagebps])/[I].[InterfaceSpeed])) * 100, 0) AS [Avg Util %] ,AVG(CASE WHEN ([IE].[In_Errors] + [IE].[In_Discards] + ISNULL([IT].[In_TotalPkts], 0)) > 0 THEN ([IE].[In_Errors] / ([IE].[In_Errors] + [IE].[In_Discards] + ISNULL([IT].[In_TotalPkts], 0))) ELSE 0 END) [% Errors In] ,AVG(CASE WHEN ([IE].[Out_Errors] + [IE].[Out_Discards] + ISNULL([IT].[Out_TotalPkts], 0)) > 0 THEN ([IE].[Out_Errors] / ([IE].[Out_Errors] + [IE].[Out_Discards] + ISNULL([IT].[Out_TotalPkts], 0))) ELSE 0 END) [% Errors Outs] ,AVG(CASE WHEN ([IE].[In_Errors] + [IE].[In_Discards] + ISNULL([IT].[In_TotalPkts], 0)) > 0 THEN ([IE].[Out_Discards] / ([IE].[In_Errors] + [IE].[In_Discards] + ISNULL([IT].[In_TotalPkts], 0))) ELSE 0 END) [% Discards In] ,AVG(CASE WHEN ([IE].[Out_Errors] + [IE].[Out_Discards] + ISNULL([IT].[Out_TotalPkts], 0)) > 0 THEN ([IE].[Out_Discards] / ([IE].[Out_Errors] + [IE].[Out_Discards] + ISNULL([IT].[Out_TotalPkts], 0))) ELSE 0 END) [% Discards Outs] ,SUM(ISNULL([IT].[In_TotalBytes], 0)) AS [Volume In Bytes] ,SUM(ISNULL([IT].[Out_TotalBytes], 0)) AS [Volume Out Bytes] FROM amonth LEFT JOIN [SolarWindsOrion].[dbo].[Nodes] AS [N] WITH (NOLOCK) ON [N].[CUSTOMER] = @CustomerName AND [N].[IsServer] = '0' AND [N].[Caption] = @DeviceName LEFT JOIN [SolarWindsOrion].[dbo].[Interfaces] AS [I] WITH (NOLOCK) ON [I].[NodeID] = [N].[NodeID] LEFT JOIN [SolarWindsOrion].[dbo].[InterfaceTraffic] AS [IT] WITH (NOLOCK) ON [IT].[InterfaceID] = [I].[InterfaceID] AND [IT].[NodeID] = [N].[NodeID] AND CAST([IT].[DateTime] AS DATE) = CAST(amonth.day AS DATE) LEFT JOIN [SolarWindsOrion].[dbo].[InterfaceErrors] AS [IE] WITH (NOLOCK) ON [N].[NodeID] = [IE].[NodeID] AND [I].[InterfaceID] = [IE].[InterfaceID] AND CAST([IE].[DateTime] AS DATE) = CAST(amonth.day AS DATE) GROUP BY [N].[Caption] ,[I].[Caption] ORDER BY 4, 3 OPTION (MaxRecursion 32767)
So the query works... The issue is my data returned does not match what a SW using the "Dynamic Query Builder" would return.
So when I run my SQL query for the "Volume In" and "Volume Out" I am seeing numbers like this:
Now if I take say the 4446243662209 and well convert it to Gigabytes I am getting "4446.243662209". So I am showing just in they are doing about 4.5 Terabytes of data in a week. But when I use the SW Dynamic method it is saying:
As you can see, none of the lines are showing Terabytes worth of data. So my question is... How the heck is Orion calculating the Volume/Total Traffic? Is the data in another table somewhere or what? The columns I used in the built in method are:
Interface Traffic History/Total Bytes Received
Interface Traffic History/Total Bytes Transmitted
Interface Traffic History/Total Bytes (Transmitted + Received)
So yeah... can anyone help me understand how they are doing calculations?
Regards,
Billy S.