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

Tos Types of Service Report

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

NodeInterfaceT kbyteT kbyte CS0T kbyte CS1T kbyte CS2T kbyte CS3...Router 1Serial0/0/0:08.43E+068305134.9122488.800.40Router 2Serial0/0/0:02.36E+0722994710.461959204.40Router 3Serial0/0/0:01.73E+06170427429146.300.20Router 4Serial0/0/0:00.50390600000Router 5Serial0/0/0:017.468800000

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

tos2.jpg

Find more posts tagged with

Accepted answers

charly_DF

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

All comments

Yann

Hi,

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.

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.

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

charly_DF

I Have the final version XD

VIEW QoS_Carlos

SQL Query

Yann

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.

Chandru

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

charly_DF

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

zsquid

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?