Help with how Network Volume Is Calculated

straderw

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.

straderw

Well I figured out the SQL Query to make the numbers match and I can adjust for the start/end of a week.  Apparently I wasn't taking into account that the timestamps in the InterfaceTraffic table are in UTC.  Then when SW reports, it semi adjusts to the Time Off Set...   So this query works:

SET DATEFIRST 7; -- 1=Monday, 7=Sunday -- First Day of the Week
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @APE VARCHAR(255)
DECLARE @CustomerName VARCHAR(255)
DECLARE @DeviceName VARCHAR(255)

--SET @StartTime = DATEADD(DAY, -7, GETDATE())
--SET @StartTime = '2022-09-18 00:00:00'
SET @StartTime = ${FromTime}

--SET @EndTime = GETDATE() -- Now
--SET @EndTime = '2022-09-25 00:00:00'
SET @EndTime = ${ToTime}

SET @CustomerName = 'Customer'
SET @DeviceName = 'Switch-Name'
SET @APE = 'MY-APE'

SELECT [N].[Caption]
      ,[I].[Caption] AS [Interface]
      ,DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [IT].[DateTime])), 0) AS [Date]
	  ,SUM(ISNULL([IT].[In_TotalBytes], 0)) AS [Volume In]
	  ,SUM(ISNULL([IT].[Out_TotalBytes], 0)) AS [Volume Out]
	  ,SUM(ISNULL([IT].[In_TotalBytes], 0) + ISNULL([IT].[Out_TotalBytes], 0)) AS [Total Volume]
FROM [SolarWindsOrion].[dbo].[Nodes] AS [N] WITH (NOLOCK)
	LEFT JOIN [SolarWindsOrion].[dbo].[Interfaces] AS [I] WITH (NOLOCK)
		ON [I].[NodeID] = [N].[NodeID]
		   AND
		   [I].[InBandwidth] > 0
		   AND
		   [I].[OutBandwidth] > 0
	LEFT JOIN [SolarWindsOrion].[dbo].[InterfaceTraffic] AS [IT] WITH (NOLOCK)
		ON [IT].[InterfaceID] = [I].[InterfaceID]
		   AND
		   [IT].[NodeID] = [N].[NodeID]
		   AND
		   [IT].[DateTime] >= CAST(@StartTime AS datetime2)
		   AND
		   [IT].[DateTime] < CAST(@EndTime AS datetime2)
WHERE
[N].[CUSTOMER] = @CustomerName
AND
[N].[Caption] = @DeviceName
AND
[N].[APE] = @APE
GROUP BY [N].[Caption]
        ,[I].[Caption]
        ,DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [IT].[DateTime])), 0)
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [IT].[DateTime])), 0) ASC
        ,[N].[Caption] DESC
		,[I].[Caption] DESC

As you can see in this screen shot.  Left is what SW was reporting and Right is what my SQL Query returns.

So how I figured this out?  Well we did open a ticket, but they were not able to give me any good answers.  What I did was as a SQL admin I turned on SQL Profile and captured what the actual SQL query was when the report ran.  I captured it:

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT [T1].[Caption] AS C1
      ,[T2].[Caption] AS C2
	  ,DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [T3].[C10])), 0) AS C3
	  ,SUM([T3].[C11]) AS C4
	  ,SUM([T3].[C12]) AS C5
	  ,SUM([T3].[C13]) AS C6
	  ,0 AS C7
FROM dbo.NodesData AS T1
	LEFT JOIN dbo.Interfaces AS T2
		ON [T2].[NodeID] = [T1].[NodeID] AND 0 = 0
	LEFT JOIN (SELECT [T4].[InterfaceID] AS C8
	                 ,0 AS C9
					 ,[T4].[DateTime] AS C10
					 ,[T4].[In_TotalBytes] AS C11
					 ,[T4].[Out_TotalBytes] AS C12
					 ,[T4].[In_TotalBytes] + [T4].[Out_TotalBytes] AS C13
			   FROM (SELECT it.*
			               ,CASE WHEN ISNULL(i.InBandwidth,0) = 0 THEN
									0
								ELSE
									ISNULL(it.In_Averagebps,0) / i.InBandwidth * 100
								END InPercentUtil
						   ,CASE WHEN ISNULL(i.OutBandwidth,0) = 0 THEN
									0
								ELSE
									ISNULL(it.Out_Averagebps,0) / i.OutBandwidth * 100
								END OutPercentUtil
					 FROM [dbo].[InterfaceTraffic] it
						LEFT JOIN [dbo].[Interfaces] i
							ON it.InterfaceID = i.InterfaceID) AS T4
					 WHERE
					 [T4].[DateTime] >= CAST('2022-09-18 00:00:00' AS datetime2)
					 AND
					 [T4].[DateTime] < CAST('2022-09-25 00:00:00' AS datetime2)
					 AND
					 [T4].[DateTime] >= CAST('2022-09-18 00:00:00' AS datetime2)
					 AND
					 [T4].[DateTime] < CAST('2022-09-25 00:00:00' AS datetime2)) AS T3
		ON [T3].[C8] = [T2].[InterfaceID]
		   AND
		   [T3].[C9] = 0
	INNER JOIN dbo.LimitationSnapshots AS LS999_T1_0
		ON [LS999_T1_0].[LimitationID] = 999
		   AND
		   [LS999_T1_0].[EntityType] = 'Orion.Nodes'
		   AND
		   [LS999_T1_0].[EntityID] = [T1].[NodeID]
WHERE
[T1].[Caption] = 'Switch-Name'
AND
[T3].[C10] >= '2022-09-18 00:00:00'
AND
[T3].[C10] < '2022-09-25 00:00:00'
GROUP BY [T1].[Caption]
        ,[T2].[Caption]
		,DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [T3].[C10])), 0)
ORDER BY [C3] ASC
        ,[C1] DESC
		,[C2] DESC

And in testing out all the joins, what fixed me was just doing this for the date:

,DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), [IT].[DateTime])), 0) AS [Date]

and now all the data lined up and looked correct.  So yeah.  Thank you for all your advice, I truly appreciate it.