cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Reports

Has anyone created a custom report that will generate Historical Percent Utilization data over a specific time period but not current?

I know you can display this data for each interface, but it would be nice to compare with others without having a graph for each interface
0 Kudos
10 Replies
Level 15

I'm not sure if this is what you are looking for, but here goes.

I am using the pre-built report to create the following report:

"Average & Peak Traffic Rates - WAN links - This Month - weekly"

Time Frame is set to "this month" & summarization is set to "weekly".
Here is the SQL query which is created by Report Writer:

SELECT DatePart(Week,DateTime) AS SummaryWeek,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(Case
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
Else
( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
AVG(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
MAX(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
AVG(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
( DateTime BETWEEN 38198 AND 38223 )
AND
(
(Interfaces.Network_Type = 'Wan')
)

GROUP BY DatePart(Week,DateTime),
Nodes.Caption, Interfaces.Caption

ORDER BY SummaryWeek ASC, 2 ASC


- This is what the output looks like;

AB-r-8th-01rt1
FastEthernet0/0 · 8th Street uplink to Head Office
32 0.26 % 0.40 % 6.49 % 0.13 % 1.18 %
33 0.25 % 0.38 % 10.23 % 0.12 % 0.60 %
34 0.18 % 0.27 % 10.63 % 0.09 % 1.41 %
35 0.22 % 0.32 % 8.76 % 0.11 % 1.59 %

The columns left to right are labelled;

Week
Circuit Utilization (Avg of Recv,Xmit)
Recv Percent Util
Peak Recv Percent Util
Xmit Percent Util
Peak Xmit Percent Util

- Weekly averages and peaks are quite doable with the existing Report Writer, without using the custom SQL queries.

-=Cheers=-
NG
0 Kudos
Level 7

quote:Originally posted by iunderwo

How is the report you're working on now set up??

// Ian Underwood - Service Management
// Level 3 Communications

Basically I dropped it as I was unable to get it to work so I have nothing right now...That's why I was looking for assistance...Thanks
0 Kudos
Level 7

How is the report you're working on now set up??

// Ian Underwood - Service Management
// Level 3 Communications
0 Kudos
Level 7

I am trying to create a report that will also try to average interface utilization over the course of say a week. And checked with Solar Winds and said there might be some sql tweaking that might be done..Not being a sql person has anyone done the above and can shed some light on this (i.e Syntax) and fields used....Thanks
0 Kudos
Level 15

Hey snowjay, you could RDP into the server, map a drive to a shared folder on your workstation, run the Reports and save them directly to your WS.
There is also a remote admin package coming out for Orion, similar
to Microsofts MMC snap-in. You install it on your WS and connect remotely to the poller and database to do all the admin work.
This will apparently be another package which can be purchased seperately from Orion.

-=Cheers=-
NG
0 Kudos
Level 8

Is it possible to generate such reports automatically and send them via email?

If the reports could be generated in this way, one could use blat or some other utility of this type to send them once processing is completed.

I haven't messed around with the report writer yet...

0 Kudos
Level 15

We connect directly to the database with Crystal Reports to create
our weekly uptime and trending reports.

NG
0 Kudos
Level 7

The Report Writer is great! Now only if there was a way to run it from a workstation instead of having to be at the server...
0 Kudos
Level 7

Thank you, I will be upgrading very soon. Any issues going from 6.3 to 6.4 that I should be aware of first?


Adam
0 Kudos
Level 7

The version 6.4 of Orion NPM includes an updated Report Writer that lets you generate these types of reports. I've been running it for a couple weeks with no problems.--HTH
0 Kudos