Hi All,
We can't keep the interface detailed information for any extended time. The best option is to dump the required information to an external database.
This will preserve the most detailed information indefinitely. In addition, the reporting can be done against the new database.
This script will copy the interface information to an external database on a daily basis. If the database is not there, it will be created. It will copy only the interfaces which has the custom property isWAN=1.
IF OBJECT_ID('[Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse]') is NULL
BEGIN
CREATE TABLE [Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse]
(
[InterfaceID] [int] NOT NULL,
[Timestamp] [datetime2](7) NOT NULL,
[NodeID] [int] NULL,
[NodeName] [nvarchar](255) NULL,
[Vendor] [nvarchar](255) NULL,
[MachineType] [nvarchar](255) NULL,
[InterfaceName] [nvarchar](512) NULL,
[InterfaceType] [int] NULL,
[InterfaceTypeName] [nvarchar](255) NULL,
[InBandwidth] [float] NULL,
[OutBandwidth] [float] NULL,
[In_Averagebps] [float] NULL,
[In_TotalBytes] [float] NULL,
[In_TotalPkts] [float] NULL,
[In_AvgUniCastPkts] [float] NULL,
[In_AvgMultiCastPkts] [float] NULL,
[Out_Averagebps] [float] NULL,
[Out_TotalBytes] [float] NULL,
[Out_TotalPkts] [float] NULL,
[Out_AvgUniCastPkts] [float] NULL,
[Out_AvgMultiCastPkts] [float] NULL,
CONSTRAINT [PK_InterfaceTraffic_DataWareHouse] PRIMARY KEY CLUSTERED
(
[Timestamp] ASC,
[InterfaceID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
)
END -- create database
IF OBJECT_ID('[Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse]') is NOT NULL
begin
if (
((select datepart(day,max(timestamp))
from [Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse])
!=
DATEPART(day,dateadd(day,-1,cast(getutcdate() as date))))
OR
((select datepart(day,max(timestamp))
from [Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse]) is NULL)
)
Begin
insert into [Interfaces-External].[dbo].[InterfaceTraffic_DataWareHouse]
(
[InterfaceID],[Timestamp],[NodeID],[NodeName],[Vendor],[MachineType]
,[InterfaceName],[InterfaceType],[InterfaceTypeName]
,[InBandwidth],[OutBandwidth],[In_Averagebps],[In_TotalBytes]
,[In_TotalPkts],[In_AvgUniCastPkts],[In_AvgMultiCastPkts]
,[Out_Averagebps],[Out_TotalBytes],[Out_TotalPkts]
,[Out_AvgUniCastPkts],[Out_AvgMultiCastPkts]
)
select
InterfaceTraffic.[InterfaceID]
,InterfaceTraffic.[DateTime] as [TimeStamp]
,InterfaceTraffic.[NodeID]
,n.caption as [NodeName],n.vendor
,n.MachineType,i.Caption as [InterfaceName],i.InterfaceType
,i.InterfaceTypeName,i.InBandwidth
,i.OutBandwidth,[In_Averagebps],[In_TotalBytes],[In_TotalPkts]
,[In_AvgUniCastPkts],[In_AvgMultiCastPkts],[Out_Averagebps]
,[Out_TotalBytes],[Out_TotalPkts],[Out_AvgUniCastPkts]
,[Out_AvgMultiCastPkts]
FROM InterfaceTraffic
left join interfaces i on i.interfaceid=InterfaceTraffic.InterfaceID
left join nodes n on n.nodeid=InterfaceTraffic.nodeid
where datediff(dd,datetime,getutcdate())=1
AND i.isWAN=1
order by datetime asc
end -- insert
end -- avoid dups
Thank you,
Amit
Loop1 Systems