This report will give you a list of interfaces that have the custom yes/no attribute of isCircuit and their DAILY 95th percentile percentage of bandwidth for the last 30 days. This is not a 30 day average report, but rather a historical daily report. The number of days in the report can be modified as can the business hours to suit your needs. My sql may need to be cleaned up a little, but it works. Enjoy!
DECLARE @IncDate varchar(50)
DECLARE @intFlag INT
DECLARE @cmd varchar (MAX)
DECLARE @ManyCol varchar(MAX)
DECLARE @InnerCmd1 varchar(MAX)
DECLARE @InnerCmd2 varchar(MAX)
DECLARE @INTFLAG1 varchar(2)
DECLARE @StartTime varchar(8)
DECLARE @EndTime varchar(8)
SET @ManyCol = ''
SET @intFlag = 1
SET @StartTime = '06:00:00'
SET @EndTime = '18:00:00'
While (@intFlag <= 30)
BEGIN
SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
SET @ManyCol = @ManyCol + ',CAST(("Maxbps_In95_' + @IncDate + '"/Interfaces.InBandwidth)*100 AS numeric(10,2)) AS ''Percent_In_' + @IncDate + '''
, ' + 'CAST(("Maxbps_Out95_' + @IncDate + '"/Interfaces.OutBandwidth)*100 AS numeric(10,2)) AS ''Percent_Out_' + @IncDate + '''
'
SET @intFlag = @intFlag + 1
end
SET @intFlag = 1
SET @InnerCmd1 = ''
WHILE @intFlag <= 30
BEGIN
SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
SET @InnerCmd1 = @InnerCmd1 + ' INNER JOIN (
SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''Maxbps_In95_' + @IncDate +
''' FROM (
SELECT DISTINCT InterfaceID
FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)
) AS AA
) AS Result_In_' + @INTFLAG1 + ' ON (Interfaces.InterfaceID = Result_In_' + @INTFLAG1 + '.InterfaceID)
'
SET @intFlag = @intFlag + 1
END
SET @intFlag = 1
SET @InnerCmd2 = ''
WHILE @intFlag <=30
BEGIN
SET @IncDate = CAST((convert(varchar(10), getdate() - @intFlag,10)) as varchar(50))
SET @INTFLAG1 = CAST(@intFlag AS VARCHAR)
SET @InnerCmd2 = @InnerCmd2 + '
INNER JOIN
(
SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime), cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)) AS ''Maxbps_Out95_' + @IncDate +
''' FROM (
SELECT DISTINCT InterfaceID
FROM dbo.InterfaceTraffic WHERE DateTime >= cast(''' + @IncDate + ' ' + @StartTime + ''' AS datetime) AND DateTime <= cast(''' + @IncDate + ' ' + @EndTime + ''' AS datetime)
) AS AA
) AS RESULT_OUT_' + @intFlag1 + ' ON (Interfaces.InterfaceID = RESULT_OUT_' + @intFlag1 + '.InterfaceID)
'
SET @intFlag = @intFlag + 1
END
SET @cmd = 'SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption' + @ManyCol + '
FROM Nodes
INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)' + @InnerCmd1 + @InnerCmd2 + '
WHERE (Interfaces.isCircuit = 1)
ORDER BY InterfaceID, NodeName, Interface_Caption'
exec (@cmd)
