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

NPM Reports

kandatihari

Hi there,

I have created a report through report writer, and the detatils are as below,

Filelds : Node Name , Interface Name & Avg of Circuit utilization (Both recieved and transmitted) Sorted by Descending

Filters - Default

Top XX - All

Time Frame - Last 6 Months

Summarization - By month

Field formating - Default

Report Grouping - SummaryMonth

As I run this run, I get the interface utilzation percentage for all the interfaces grouped by month. But I want to limit this to only the first 10 records grouped under a month.

Could you any please help with the logic.

Thanks - Hari

Find more posts tagged with

Accepted answers

All comments

bsciencefiction.tv

Change Top XX to 10

kandatihari

If I set the Top XX to 10, then only top 10 records for the first month are displayed and not for rest of the months.but I want the top 10 for every month.

Thanks for the reply!

JiriPsota

Create new SQL report and use this query

SELECT

SummaryMonth,

NodeName,

Interface_Name,

AVERAGE_of_CircuitUtil_AvgRecvXmit

FROM (SELECT

ROW_NUMBER() OVER ( PARTITION BY SummaryMonth ORDER BY AVERAGE_of_CircuitUtil_AvgRecvXmit DESC ) AS 'RowNumber',

SummaryMonth,

NodeName,

Interface_Name,

AVERAGE_of_CircuitUtil_AvgRecvXmit

FROM (

SELECT CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,

Nodes.Caption AS NodeName,

Interfaces.InterfaceName AS Interface_Name,

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

FROM

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

WHERE

( DateTime BETWEEN DATEADD(mm,-6,GETDATE()) AND GETDATE() )

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

Nodes.Caption, Interfaces.InterfaceName

) as data

) dt

WHERE RowNumber <= 10

kandatihari

Thanks a bunch, That works.

Also I am looking to generate a report to list the top 10 interfaces whose utilization is high in last month and trend of the utilization of these 10 interfaces in the previous 6 months. could you please give idea to get this done. THanks