Search 85,948 posts and 653 resources contributed by 43,573 members or post a topic.

Already Joined? Sign in
Tos Types of Service Report

Page 1 of 1 (9 items) | RSS

rated by 0 users
Answered (Verified) This post has 1 verified answer | 8 Replies | 0 Followers | 716 Views


75 Posts
Points 208
SolarWinds Certified Professional
charly_DF posted on Thu, Apr 30 2009 3:45 PM
rated by 0 users
tos2.jpg

 Hi, I´m trying to generate a report of all Types of Service from all my routers by SQL query, something like this table:

Node

Interface

T kbyte

T kbyte CS0

T kbyte CS1

T kbyte CS2

T kbyte CS3

...

Router 1

Serial0/0/0:0

8.43E+06

8305134.9

122488.8

0

0.4

0

Router 2

Serial0/0/0:0

2.36E+07

22994710.4

619592

0

4.4

0

Router 3

Serial0/0/0:0

1.73E+06

1704274

29146.3

0

0.2

0

Router 4

Serial0/0/0:0

0.503906

0

0

0

0

0

Router 5

Serial0/0/0:0

17.4688

0

0

0

0

0

But I still have troubles to:

  • define the date time range,
  • define the souce (NetFlowSummary1 contains only las two days, NetFlowSummary2 contains only the actual month minus NetFlowSummary1, and NetFlowSummary3 contains the rest of recors minus NetFlowSummary2 and NetFlowSummary1 )
  • show records of all routers (I have 200 and my query only shows 40 records)

 Any idea??

Experimental Query:

 

 

SELECT

 

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 BETWEEN 39902 AND 39932

AND Nodes.TIPO = 'R'

AND Interfaces.InterfaceTypeName = 'ppp'

 

GROUP BY

Nodes.Caption,

Interfaces.InterfaceName,

Nodes.NodeID

 

 

 


  • | Post Points: 8

Answered (Verified) Verified Answer


75 Posts
Points 208
SolarWinds Certified Professional
Answered (Verified) charly_DF replied on Wed, May 6 2009 9:07 PM
rated by 0 users
Verified by Elisabeth Zakes

I Have the final version XD

 

VIEW QoS_Carlos

SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary1
UNION ALL
SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary2
UNION ALL
SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary3

 

SQL Query

SELECT 
--top 5
CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth
,Nodes.FOLIO
,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 QoS_Carlos
INNER JOIN Nodes ON Nodes.NodeID = QoS_Carlos.NodeID
INNER JOIN TypesOfService ON TypesOfService.ToSID = QoS_Carlos.ToS 
--INNER JOIN Interfaces ON Interfaces.InterfaceID = Nodes.NodeID
INNER JOIN Interfaces ON Interfaces.InterfaceID = QoS_Carlos.InterfaceIDRx

WHERE 
 ((DateDiff (m, DateTime, getdate())) = 1)
 AND Interfaces.InterfaceTypeName = 'ppp'

GROUP BY 
  CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101)
  ,Nodes.FOLIO  
  ,Nodes.Caption
  ,Interfaces.InterfaceName

 

 

 

  • | Post Points: 25

All Replies


1,000 Posts
Points 5,477
SolarWinds Certified Professional
Yann replied on Fri, May 1 2009 5:58 AM
rated by 0 users

Hi,

charly_DF:
define the date time range,


You can edit the NetFlowSummary1.StartTime statement in the WHERE Condition to something as below:

DatePart(day, StartTime) between 1 and 10

Will show results from the 1st to the 10th of Month. 

DatePart(dw, StartTime) between 2 and 6

Will show results from the monday to the friday. Numbers can change for the weekday depending of the db collation http://msdn.microsoft.com/en-us/library/ms174420.aspx 

DateDiff (m, StartTime, getdate()) = 1

Will show results from the previous month.

TIPs time: The number in your query "39902 AND 39932" are static dates, you can use a spreadsheet application to convert them into something readable.

charly_DF:
define the souce (NetFlowSummary1 contains only las two days, NetFlowSummary2 contains only the actual month minus NetFlowSummary1, and NetFlowSummary3 contains the rest of recors minus NetFlowSummary2 and NetFlowSummary1 )

There are some views you could use:

NetFlowApplicationSummary, NetFlowDetail_XX, NetflowSummary

Open your database using SQL Server Management Studio to review them.

charly_DF:
show records of all routers (I have 200 and my query only shows 40 records)

The following statement in the query:

AND Nodes.TIPO = 'R'

AND Interfaces.InterfaceTypeName = 'ppp'

is probably filtering too much the results.

HTH,

Yann

  • | Post Points: 3

75 Posts
Points 208
SolarWinds Certified Professional
Answered (Verified) charly_DF replied on Wed, May 6 2009 9:07 PM
rated by 0 users
Verified by Elisabeth Zakes

I Have the final version XD

 

VIEW QoS_Carlos

SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary1
UNION ALL
SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary2
UNION ALL
SELECT Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
FROM dbo.NetFlowSummary3

 

SQL Query

SELECT 
--top 5
CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth
,Nodes.FOLIO
,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 QoS_Carlos
INNER JOIN Nodes ON Nodes.NodeID = QoS_Carlos.NodeID
INNER JOIN TypesOfService ON TypesOfService.ToSID = QoS_Carlos.ToS 
--INNER JOIN Interfaces ON Interfaces.InterfaceID = Nodes.NodeID
INNER JOIN Interfaces ON Interfaces.InterfaceID = QoS_Carlos.InterfaceIDRx

WHERE 
 ((DateDiff (m, DateTime, getdate())) = 1)
 AND Interfaces.InterfaceTypeName = 'ppp'

GROUP BY 
  CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101)
  ,Nodes.FOLIO  
  ,Nodes.Caption
  ,Interfaces.InterfaceName

 

 

 

  • | Post Points: 25

1,000 Posts
Points 5,477
SolarWinds Certified Professional
Yann replied on Thu, May 7 2009 4:49 AM
rated by 0 users

It looks great :). Just to add my 2c, you were right to create a view because the default NetFlowSummary view does not contain the ToS column which makes it useless for the report you built...in case someone ask why it requires to create an additional view.

  • | Post Points: 3

135 Posts
Points 347
Chandru replied on Tue, Jun 23 2009 1:58 AM
rated by 0 users

Hi Charly,

 

Thanks for the SQL query

 

I have created a new view QoS_Carlos

 

Is the Nodes.FOLIO you have created a custom property for the router

Can you clarify?

Chandru

  • | Post Points: 3

75 Posts
Points 208
SolarWinds Certified Professional
charly_DF replied on Tue, Jun 23 2009 1:01 PM
rated by 0 users

Nodes.FOLIO is a custom property, I have a lot of tools and a lot

of equipment, then I assign a common ID in all the tools to track

devices and correlation reports, for example:

FOLIO= B001R1

B Sector ID

001 Building ID

R Type of device (R=router S=switch F=Firewall)

1 ID device

  • | Post Points: 3

135 Posts
Points 347
Chandru replied on Tue, Jun 23 2009 8:13 PM
rated by 0 users

I tried to run the SQL query but it gives me output only for one day at 00.00 time only

Can i get report for a specific node for a month?

can you help me rewrite the code?

Thanks

Chandru

  • | Post Points: 1

1 Posts
Points 3
zsquid replied on Mon, Jul 6 2009 6:00 PM
rated by 0 users

Hi I tried this query on my server, and it works but I get the ToS value returned as an ID, seems its not reading the valutes for the TypesOfService table. anyway to correct this?

  • | Post Points: 3

1,000 Posts
Points 5,477
SolarWinds Certified Professional
Yann replied on Tue, Jul 7 2009 4:16 AM
rated by 0 users

Hi all,

NTA 3.5 has been released and polls automatically the CBQoS MIBs on the devices where it is enabled.

http://www.solarwinds.com/products/orion/nta/network_performance_views.aspx

If you have NTA, I would advise you to use this feature as it provides out of the box charts and reports in the web console.

http://www.youtube.com/watch?v=9C5F2XmiJyU

I hope this will save time to all of you.

Yann

  • | Post Points: 1
Page 1 of 1 (9 items) | RSS

© 2003 - 2010 SolarWinds, Inc. All Rights Reserved.

Who is SolarWinds?

SolarWinds is rewriting the rules for how companies manage their networks. Guided by a global community of network engineers, SolarWinds develops simple and powerful network management software and network monitoring software for networks of all sizes. SolarWinds also offers a network certification program to become a SolarWinds Certified Professional (SCP).

What is thwack?

thwack, SolarWinds online community site, was designed by network engineers, for network engineers. thwack is a vibrant, growing community of more than 30,000 IT pros who share a passion for technology.

Explore Resources, Answers, Templates, and Advice

Download Free Networking Tools

Learn More About SolarWinds Products