Need report for top interfaces (7 days)

I'm trying to find a way to tell which interfaces have the highest throughput over the last 7 days.

So far I've found the report "Average and Peak Traffic Rates - Last 7 Days" which is almost perfect. I can sort on the Peak columns, but it only sorts per device, not globally.

The realtime module "Top 25 Interfaces by Percent Utilization" is great too, but that's realtime. I'd like to see something like that, but showing 1 weeks data.

Any ideas?

Thanks

Shane

Find more posts tagged with

report_writer

custom_report

report

Accepted answers

mockmyberet

How about something like this?

SELECT N.Caption, I.FullName, MAX(IT.In_Maxbps + IT.Out_Maxbps) AS MAX_Thru_bps, AVG(IT.In_Averagebps) AS AVG_In_bps, MIN(IT.In_Minbps) AS MIN_In_bps, MAX(IT.In_Maxbps) AS MAX_In_bps, AVG(IT.Out_Averagebps) AS AVG_Out_bps, MIN(IT.Out_Minbps) AS MIN_Out_bps, MAX(IT.Out_Maxbps) AS MAX_Out_bpsFROM InterfaceTraffic ITINNER JOIN Interfaces I ON IT.NodeID = I.NodeID AND IT.InterfaceID = I.InterfaceID INNER JOIN Nodes N ON IT.NodeID = N.NodeIDWHERE (IT.DateTime > DATEADD(WK, - 1, GETDATE()))GROUP BY N.Caption, I.FullNameORDER BY MAX_Thru_bps DESC

All comments

mockmyberet

How about something like this?

SELECT N.Caption, I.FullName, MAX(IT.In_Maxbps + IT.Out_Maxbps) AS MAX_Thru_bps, AVG(IT.In_Averagebps) AS AVG_In_bps, MIN(IT.In_Minbps) AS MIN_In_bps, MAX(IT.In_Maxbps) AS MAX_In_bps, AVG(IT.Out_Averagebps) AS AVG_Out_bps, MIN(IT.Out_Minbps) AS MIN_Out_bps, MAX(IT.Out_Maxbps) AS MAX_Out_bpsFROM InterfaceTraffic ITINNER JOIN Interfaces I ON IT.NodeID = I.NodeID AND IT.InterfaceID = I.InterfaceID INNER JOIN Nodes N ON IT.NodeID = N.NodeIDWHERE (IT.DateTime > DATEADD(WK, - 1, GETDATE()))GROUP BY N.Caption, I.FullNameORDER BY MAX_Thru_bps DESC

FormerMember

That worked perfectly. It was my first time using Report Writer, but adding that as Advanced SQL worked fine.

THANKS!

mockmyberet

Yeah, I've just started dabbling with SQL myself. It's pretty neat what you can pull out of the database.