Hi, we only have traffic in CS1 and CS3. We need a graph or data with the percentage of CS1 and CS3 respect the total bandwith of every WAN link. Today we obtain this information from Cisco Works Qos Performance Monitor, but this tool going to be no longer available. Instead of QPM, CEO going to buy NetFlow Traffic Analyzer Module. So we need Display the Average Transmit bps per Type of Service and percentage used by every ToS for each WAN Interface over the last month. QoS - Last MonthNode NameInterface Name Transmit BandwithAverage Transmit bpsAverage Transmit bps CS1Average Transmit bps CS3Average Bandwith usage CS1Average Bandwith usage CS3AustinSerial0/0/0:0 - WAN Link1.98 Mbps547 Kbps176188.7370811.332.21 %67.79 %DallasSerial0/0/1:0 - WAN Link1.98 Mbps329 Kbps83072.5245927.525.25 %74.75 %NYSerial0/0/0:0 - WAN Link1.98 Mbps451 Kbps198349.8252650.243.98 %56.02 % Any Idea?, I need give a demo report to my CEO in order to approve the purchase of NetFlow Traffic Analyzer

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Net Flow and ToS

Hi, we only have traffic in CS1 and CS3. We need a graph or data with the percentage of CS1 and CS3 respect the total bandwith of every WAN link.

Today we obtain this information from Cisco Works Qos Performance Monitor, but this tool going to be no longer available. Instead of QPM, CEO going to buy NetFlow Traffic Analyzer Module.

So we need Display the Average Transmit bps per Type of Service and percentage used by every ToS for each WAN Interface over the last month.

QoS - Last Month
Node Name	Interface Name	Transmit Bandwith	Average Transmit bps	Average Transmit bps CS1	Average Transmit bps CS3	Average Bandwith usage CS1	Average Bandwith usage CS3
Austin	Serial0/0/0:0 - WAN Link	1.98 Mbps	547 Kbps	176188.7	370811.3	32.21 %	67.79 %
Dallas	Serial0/0/1:0 - WAN Link	1.98 Mbps	329 Kbps	83072.5	245927.5	25.25 %	74.75 %
NY	Serial0/0/0:0 - WAN Link	1.98 Mbps	451 Kbps	198349.8	252650.2	43.98 %	56.02 %

Any Idea?, I need give a demo report to my CEO in order to approve the purchase of NetFlow Traffic Analyzer

Find more posts tagged with

Accepted answers

All comments

mcbridea

Sent you a PM on this.

davidmaltby

To create this type of report in Report Writer, you need to create a new "Advanced SQL" report. Then in the SQL tab cut-n-paste the following query..

SELECT Nodes.Caption AS [Node Name], Interfaces.InterfaceName AS [Interface Name],

--Add up the total transmit bytes for a month and divide by the number of seconds in a month

-- Times 8 for bits in a byte

-- Divide by 1024 to convert bits to kilobits.

CONVERT(varchar, CONVERT(DECIMAL(10,0),((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) / 1024 )) + ' Kbps' AS [Average Transmit bps],

--[Average Transmit bps CS1]

CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) AS [Average Transmit bps CS1],

--[Average Transmit bps CS3]

CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) AS [Average Transmit bps CS3],

--[Average Transmit bps CS1]/ ([Average Transmit bps] * 1024) The 1024 cancels out because [Average Transmit bps] is in Kbps

CONVERT (nvarchar, CONVERT(DECIMAL(10,0),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))/ ((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))))) + ' %' AS [Average Bandwith usage CS1],

--[Average Transmit bps CS3]/ ([Average Transmit bps] * 1024) The 1024 cancels out because [Average Transmit bps] is in Kbps

CONVERT (nvarchar, CONVERT(DECIMAL(10,0),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))/ ((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))))) + ' %' AS [Average Bandwith usage CS3]

FROM Nodes JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID = NetFlowSummary1.InterfaceIDTx

JOIN TypesOfService ON NetFlowSummary1.ToS = TypesOfService.ToSID

WHERE NetFlowSummary1.StartTime > DATEADD(month, -1, GETDATE())

GROUP BY Nodes.Caption, Interfaces.InterfaceName, Nodes.NodeID

I tried to add comments so that you can verify that my calculations are correct. Hopefully this is what you were looking for. It is a lot of T-SQL, so I may have missed something.

Thanks,

davidmaltby

Do note that I didn't spend any time trying to optimize this query.

charly_DF

Hi, I was testing the SQL query, and looks fine, I'm going to compare the results with CW-QoS, and the table of InterfaceTraffic. I will post my results soon

SELECT

Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0) AS SummaryDateTime,

Nodes.NodeID AS NodeID,

Interfaces.InterfaceID AS InterfaceID,

Nodes.Caption AS NodeName,

Interfaces.Caption AS Interface_Caption,

Interfaces.OutBandwidth,

AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps

FROM

(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))

INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID =
NetFlowSummary1.InterfaceIDTx

WHERE

( DateTime BETWEEN 39770 AND 39771 )

AND

( NOT (Interfaces.PhysicalAddress > '') )

GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0),

Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.Caption, Interfaces.OutBandwidth

charly_DF

Documento sin título

I change a little bit the query, but I still have problems to select a range of dates, for example:

Where NetFlowSummary1.StartTime BETWEEN '3/01/2009' AND '3/30/2009'

And the table NetFlowSummary1 only have records of the las two days, We need querys respect past months

Experimental Query

SELECT top 205

Nodes.Caption AS [Node Name],
Interfaces.InterfaceName AS [Interface Name],

CONVERT(varchar, SUM(TotalBytes)/1024) AS [T kbyte],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'Unknown' THEN TotalBytes ELSE 0 END)) / 1024) AS [T kbyte Unknown],
CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS0' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS0],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS1],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS2' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS2],
CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS3],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS4' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS4],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS5' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS5],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS6' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS6],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS7' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS7],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF11' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF11],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF12' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF12],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF13' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF13],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF21' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF21],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF22' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF22],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF23' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF23],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF31' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF31],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF32' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF32],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF33' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF33],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF41' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF41],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF42' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF42],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF43' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF43],
--CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'EF' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte EF]

FROM Nodes
JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID = NetFlowSummary1.InterfaceIDTx
JOIN TypesOfService ON NetFlowSummary1.ToS = TypesOfService.ToSID

WHERE
NetFlowSummary1.StartTime > DATEADD(month, -1, GETDATE()) AND
Nodes.TIPO = 'R' AND
Interfaces.InterfaceTypeName = 'ppp'

GROUP BY
Nodes.Caption,
Interfaces.InterfaceName,
Nodes.NodeID