I'm in need of modifiyng WAN optimization reports. Currently the reports are working but since we have several locations with these devices, I want to be able to split those reports by region. I do have a custom property for each node, specifying the region (Americas, Europe, Asia, etc...). I'm in no way expert at SQL, can any body out there help me out modifying the SQL instructions to do this. Here is the code i'm currently using. I got it from
SET NOCOUNT ON;
declare @StartDate datetime
declare @EndDate datetime
--
-- CUSTOMIZE: Change these to change the date range.
--
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
declare @ResultsTable table
(
PreInterfaceId int,
PostInterfaceId int,
TimeInterval datetime null,
PreTotalBytes real null,
PreTotalPackets real null,
PostTotalBytes real null,
PostTotalPackets real null
)
insert into @ResultsTable (PreInterfaceId, TimeInterval, PreTotalBytes, PreTotalPackets)
select t.InterfaceID PreInterfaceId, dbo.DateOnly(DateTime) PreDate, Sum(In_TotalBytes) PreTotalBytes, Sum(In_TotalPkts) PreTotalPackets
from InterfaceTraffic t
inner join Interfaces i on i.InterfaceID = t.InterfaceID
where DateTime > @StartDate and DateTime <= @EndDate
and OptimizedInterfaceID is not null
group by t.InterfaceID, dbo.DateOnly(DateTime)
update @ResultsTable
set PostInterfaceId = a.PostInterfaceId,
PostTotalBytes = a.PostTotalBytes,
PostTotalPackets = a.PostTotalPackets
from
(
select i.InterfaceId InternalPreInterfaceId, t.InterfaceID PostInterfaceId, dbo.DateOnly(DateTime) PostDate, Sum(In_TotalBytes) PostTotalBytes, Sum(In_TotalPkts) PostTotalPackets
from InterfaceTraffic t
inner join Interfaces i on i.OptimizedInterfaceID = t.InterfaceID
where DateTime > @StartDate and DateTime <= @EndDate
group by i.InterfaceId, t.InterfaceID, dbo.DateOnly(DateTime)
) a
where TimeInterval = a.PostDate
and PreInterfaceId = a.InternalPreInterfaceId
select TimeInterval,
PreNodes.Caption AS Pre_Optimized_NodeName,
PreNodes.VendorIcon AS Pre_Optimized_Vendor_Icon,
PreInterfaces.Caption AS Pre_Optimized_Interface_Caption,
PostNodes.Caption AS Post_Optimized_NodeName,
PostNodes.VendorIcon AS Post_Optimized_Vendor_Icon,
PostInterfaces.Caption AS Post_Optimized_Interface_Caption,
PreTotalBytes Pre_Optimization_Traffic_Bytes,
PostTotalBytes Post_Optimization_Traffic_Bytes,
(PreTotalBytes - PostTotalBytes) / PreTotalBytes * 100 Percent_Optimized_Bytes,
PreTotalPackets Pre_Optimization_Traffic_Packets,
PostTotalPackets Post_Optimization_Traffic_Packets,
(PreTotalPackets - PostTotalPackets) / PreTotalPackets * 100 Percent_Optimized_Packets
from
(
--
-- CUSTOMIZE: Use this query to show an entry per day for each interface-pair or...
--
select * from @ResultsTable
--
-- CUSTOMIZE: Use this query to combine all the data for an interface-pair to a single result
--
-- select
-- PreInterfaceId,
-- PostInterfaceId,
-- MIN(TimeInterval) TimeInterval,
-- SUM(PreTotalBytes) PreTotalBytes,
-- SUM(PreTotalPackets) PreTotalPackets,
-- SUM(PostTotalBytes) PostTotalBytes,
-- SUM(PostTotalPackets) PostTotalPackets
-- from @ResultsTable
-- group by PreInterfaceId, PostInterfaceId
) a
inner join Interfaces PreInterfaces on PreInterfaces.InterfaceID = a.PreInterfaceId
inner join Nodes PreNodes on PreNodes.NodeID = PreInterfaces.NodeID
inner join Interfaces PostInterfaces on PostInterfaces.InterfaceID = a.PostInterfaceId
inner join Nodes PostNodes on PostNodes.NodeID = PostInterfaces.NodeID
order by Pre_Optimized_NodeName, Pre_Optimized_Interface_Caption, TimeInterval
SET NOCOUNT OFF;
Thanks...
