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

Report Writer - Trend Month vs Month-1

Hello,

I would like to make a report about the total bytes recv/transmit with a "trend" (Evolution from the previous month).

For this trend, in a column, I want to have a percentage of recv/transmit evolution.

For example:

Devices             Month               Recv          Xmit         Total (Recv/Xmit)     Evolution from the previous month
MyDevice1     January 2008       x Gbytes     y Gbytes        z Gbytes                  30%
MyDevice1     Feburay 2008     a Gbytes    b Gbytes         c Gbytes                   15%

And if possible, another column with Month vs (Month-1 + Month-2 + Month-3) / 3

Do you know if it's possible or not ?

Thanks in advance.

Find more posts tagged with

Accepted answers

Geolem

Thanks for your code.

I try to implement it in my side. But actually, we plan to do an export to Excel and make trends with it.

All comments

savell

Month by month trending is difficult to do with the current report writer features (and the way Orion aggregates data).

I produce monthly availability stats and charts by utilizing SQL Reporting Services. We extract the data required from the Orion database each month (summarized availability for each node) and then create reports from this data.

You could easily take the same approach for the recv/transmit data you wish to trend.

Refer for some report examples using this technique.

Dave.

Geolem

Very thanks for your answer.

I have read it and I think it can help me... just a bit difficult, ... but why not.

savell

No problem.

If I get a chance over the next few days, I'll generate the DTS scripts for this and produce a sample report - we could do with this kind of report ourselves. Although I will probably group the data using the Network Name that we have assigned each device (i.e. recv/trans total for each Network).

Dave.

savell

Geolem - I finally managed to get some time to look at the SQL for this.

The InterfaceTraffic_Daily table contains some nicely rolled up data that can be used (retention is 365 days by default). As long as you don't want progress data for the current month, its quite a simple job to rollup the daily stats for each device.

Here is the SQL:

SELECT Nodes.Sysname, DATEADD(Month, DATEDIFF(Month, 0, InterfaceTraffic_Daily.DateTime), 0) as ForDate,

Sum(InterfaceTraffic_Daily.In_TotalBytes/1000000000) as In_TotalGigabytes,

Sum(InterfaceTraffic_Daily.Out_TotalBytes/1000000000)as Out_TotalGigabytes FROM

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

LEFT JOIN InterfaceTraffic_Daily ON Interfaces.InterfaceID = InterfaceTraffic_Daily.InterfaceID

WHERE InterfaceTraffic_Daily.DateTime <> ''

AND

Interfaces.InterfaceName like 'Serial0/0/0.[34]00'

AND Nodes.NetworkName = 'XP Tier 4'

Group By Nodes.SysName, DATEADD(Month, DATEDIFF(Month, 0, InterfaceTraffic_Daily.DateTime), 0)

Order By Nodes.Sysname, DATEADD(Month, DATEDIFF(Month, 0, InterfaceTraffic_Daily.DateTime), 0) asc

In the above SQL, I didn't want to generate stats for every device - I wanted to just generate data for our Branch Network devices (we have a custom property called NetworkName), and both serial interfaces on each device.

Just remove/modify the two AND statements if you wish to filter on a different property.

Once you have tested the SQL statement, you can then use it in a DTS job to populate a table for reporting purposes (perhaps using SQL Reporting Services or another reporting tool).

The table definition step looks like this (assumes you want to recreate the table each time, change tavle name to suit):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrionInterfaceTraffic_Daily_Branch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[OrionInterfaceTraffic_Daily_Branch]

CREATE TABLE [dbo].[OrionInterfaceTraffic_Daily_Branch] (

[ForDate] [datetime] NOT NULL ,

[In_TotalGigabytes] [real] NULL ,

[Out_TotalGigabytes] [real] NULL

) ON [PRIMARY]

I actually wanted to track the total volume of Branch traffic on a daily basis (using all 365 days). This meant rolling up data from the Daily, Hourly and Detail tables. I utilized three queries for this in a DTS job to populate a single table.

This provides total traffic data for this Network for each day (just the selected WAN interfaces) - which provides us with some good trending information over the last 12 months. It's run daily, so the previous day's data is always available.

From the InterfaceTraffic_Daily table:

SELECT InterfaceTraffic_Daily.DateTime as ForDate, Sum(InterfaceTraffic_Daily.In_TotalBytes)/1000000000 as In_TotalGigabytes, Sum(InterfaceTraffic_Daily.Out_TotalBytes/1000000000)as Out_TotalGigabytes

FROM (Nodes LEFT JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID) LEFT JOIN InterfaceTraffic_Daily ON Interfaces.InterfaceID = InterfaceTraffic_Daily.InterfaceID

WHERE InterfaceTraffic_Daily.DateTime <> ''

AND

Interfaces.InterfaceName like 'Serial0/0/0.[34]00'

AND Nodes.NetworkName = 'XP Tier 4'

Group By InterfaceTraffic_Daily.DateTime

Order By InterfaceTraffic_Daily.DateTime asc

From the InterfaceTraffic_Hourly table:

SELECT DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Hourly.DateTime), 0) as ForDate, Sum(InterfaceTraffic_Hourly.In_TotalBytes)/1000000000 as In_TotalGigabytes, Sum(InterfaceTraffic_Hourly.Out_TotalBytes/1000000000)as Out_TotalGigabytes

FROM (Nodes LEFT JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID) LEFT JOIN InterfaceTraffic_Hourly ON Interfaces.InterfaceID = InterfaceTraffic_Hourly.InterfaceID

WHERE InterfaceTraffic_Hourly.DateTime <> ''

AND

Interfaces.InterfaceName like 'Serial0/0/0.[34]00'

AND Nodes.NetworkName = 'XP Tier 4'

Group By DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Hourly.DateTime), 0)

Order By DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Hourly.DateTime), 0) asc

From the InterfaceTraffic_Detail table:

SELECT DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Detail.DateTime), 0) as ForDate, Sum(InterfaceTraffic_Detail.In_TotalBytes)/1000000000 as In_TotalGigabytes, Sum(InterfaceTraffic_Detail.Out_TotalBytes/1000000000)as Out_TotalGigabytes

FROM (Nodes LEFT JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID) LEFT JOIN InterfaceTraffic_Detail ON Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID

WHERE InterfaceTraffic_Detail.DateTime <> ''

AND

Interfaces.InterfaceName like 'Serial0/0/0.[34]00'

AND Nodes.NetworkName = 'XP Tier 4'

Group By DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Detail.DateTime), 0)

Order By DATEADD(Day, DATEDIFF(Day, 0, InterfaceTraffic_Detail.DateTime), 0) asc

I think I could have done the whole lot in one statement - but most of the time when I try things like that it just gets confusing!

Hope this was of some help.

Dave