Need help with report

jtorres

Hi,

 I have Orion NPM v8.5 monitoring Juniper WXC devices (WAN Optimization/Reduction devices). Each device has 2 interfaces (In and Out). Traffic entering the In interface is reduced/optimized and forwarded out of the Out interface. I would like to write a report that SUMs up the Total Bytes Received for the In interface and substracts it to the Total Bytes Transmitted out of the Out interface and then divides the result by Total Bytes Received for the In interface and multiply it by 100. In other words, I would like to get the percentage of Reduction/Optimization. Has anybody done this in Orion or similar report? I would like to find out if this is possible at all. 

Thanks in advance. 

jtorres

OK.

 After pulling my hairs all night, I was able to figure this one out. I basically modified the the WAN Optimization report in the Orion Reports page here in Thwack so it could work for the Juniper WXC devices. For anybody out there that may be interested in a similar report, here it is. I should mention that you need to format the fields in the report accordingly to make it look neat. I should also mention that you will need to change the t.InterfaceID = 171 and t.InterfaceID = 172 values in the query to match the correct Interface ID values for your device.

-- WAN Optimization for Last 24 Hours 

SET NOCOUNT ON;
declare @StartDate datetime
declare @EndDate datetime

--
-- CUSTOMIZE: Change these to change the date range.
--
set @EndDate = GetDate()
set @StartDate = DateAdd(hour, -24, @EndDate)

declare @ResultsTable table
(
 PreInterfaceId int,
 PostInterfaceId int,
 TimeInterval datetime null,
 PreTotalBytes real null,
 PostTotalBytes real null
)

insert into @ResultsTable (PreInterfaceId, TimeInterval, PreTotalBytes)
 select t.InterfaceID PreInterfaceId, dbo.DateOnly(DateTime) PreDate, Sum(Out_TotalBytes) PreTotalBytes
 from InterfaceTraffic t
 inner join Interfaces i on i.InterfaceID = t.InterfaceID
 where DateTime > @StartDate and DateTime <= @EndDate
   and t.InterfaceID = 171
 group by t.InterfaceID, dbo.DateOnly(DateTime)

update @ResultsTable
set PostInterfaceId = a.PostInterfaceId,
    PostTotalBytes = a.PostTotalBytes
from
(
 select i.InterfaceId InternalPreInterfaceId, t.InterfaceID PostInterfaceId, dbo.DateOnly(DateTime) PostDate, Sum(In_TotalBytes) PostTotalBytes
 from InterfaceTraffic t
 inner join Interfaces i on t.InterfaceID = 172
 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
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(PostTotalBytes) PostTotalBytes
 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;