Interface_Traffic - SQL Query for Last Month of Data

[Anon Anon]

Hello,

 

I'm currently trying to build out a SQL query to take all data points for interface_traffic QoS data and average them out into daily/monthly values.  The goal is to use this information to create a monthly report for a customer in a multi-tenent environment.  For example,

 

ACME Monthly Bandwidth Report

<fancy graph for all interfaces on firewall1>

<equally fancy chart to go with above graph>

<fancy graph for all interfaces on firewall2>

<equally fancy chart to go with above graph>

 

I realize this can be accomplished using the report_engine and/or UMP, Dustin referenced it in a similar request here: 

http://forum.nimsoft.com/t5/General-Development/QoS-Report-for-Interface-traffic-Probe/td-p/6879

The problem with using UMP or report_engine is they are each lacking in some way; either it does not provide all of the information we require in the same report or it's not as aesthetically pleasing.  Ultimately the goal is to use this data in an iReport, which is then uploaded to the repository in the UMP.

 

Has anyone put this in place (or something similar)?  I'm stumbling with the combination of multiple origins in conjunction with needing joins to link sources to table ids on the database end.

[Anon Anon]

The report dustin reference was in the Unified Reports and is one of the default reports.  I have created a monthly report we give to our customers for top 10 interfaces utilizatiion.  Below is the query that calls a view that I created based off the current interface view I just added the origin and create some input contols for each of our origins.  I also do the same for outbound.

 

 

SELECT     TOP (10) AVG(samplevalue) AS Average, MAX(samplevalue) as Max, source, target

FROM         V_QOS_INTERFACE_TRAFFIC_PERC_WORIGIN

WHERE     origin = $P{CUST} and (target LIKE 'IN-%') 

AND (MONTH(sampletime) = MONTH(GETDATE() - DAY(GETDATE()))) AND (YEAR(sampletime) = YEAR(GETDATE() - DAY(GETDATE())))

GROUP BY target, source

ORDER BY Average DESC

SELECT     TOP (10) AVG(samplevalue) AS Average, MAX(samplevalue) as Max, source, targetFROM         V_QOS_INTERFACE_TRAFFIC_PERC_WORIGIN  WHERE     origin = $P{CUST} and (target LIKE 'IN-%') AND (MONTH(sampletime) = MONTH(GETDATE() - DAY(GETDATE()))) AND (YEAR(sampletime) = YEAR(GETDATE() - DAY(GETDATE())))GROUP BY target, sourceORDER BY Average DESC

 

[Anon Anon]

Thanks Neal, this should be very helpful, I'll give it a try.  I'm still working on getting the view together for the first piece of this so I'm not quite at this point just yet.