Introduction
If you are an administrator of application servers or network, it is interesting to get the overall picture: what's wrong in the network. Here is the sample picture changing each ten mutes. It is the WebPart with installed OWC (Office Web Components):
I have done this work some years ago, but I hope this will be interesting for you.
Background
Time to time, some problems occur in the network. You need to login to each system and view state of the interfaces and traffic there.
If you look at the picture above, you can view hosts and traffic grouped by hour.
This can help you to decide where to change in the network architecture. This work was done some years ago and was worked on for several years.
Before, I graduated Cisco CCNA course and this help me to do this work. So, my network looks like (there is static translation of addresses from DMZ to the outside interface of Pix appliance):
Using the Code
The idea of this program to read router's data as fast as possible and store this data into the 'Rain
' table. The data are calculated by the Microsoft SQL server triggers and batch procedures and result viewed by SharePoint with OWC installed. The main purpose of the triggers is making for user view result as fast as possible. The Cisco's operating system has the possibility accumulating information about traffic path-though. Each interface must be configured like this:
Conf t
Int s0
...
ip accounting output-packets
...
Int e0
...
ip accounting output-packets
...
ip accounting-threshold 10000
This data must be stored into the database table. I have created this table in Microsoft SQL2005 database as:
CREATE TABLE [dbo].[RAIN_TRAFFIC] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[IPS] [char] (15) NOT NULL ,
[IPD] [char] (15) NOT NULL ,
[PACKETS] [int] NOT NULL ,
[BYTES] [int] NOT NULL ,
[RDATE] [smalldatetime] NOT NULL ,
[SNAME] [varchar] (256) NULL ,
[DNAME] [varchar] (256) NULL
) ON [PRIMARY]
Here is:
How does it work?
There may be any number of threads for writing data to the SQL database (can be many data waiting for writing). Here the code of how the thread is started:
void CIPtrafficDlg::OnTimer(UINT nIDEvent)
{
m_uTimer-=1; CButton *pRunning=(CButton *)GetDlgItem(IDC_CHECK_RUN_THREADs);
if(pRunning->GetCheck()!=1)
{
m_bRun=-1; }else{
CStatic *pStxtTimer=(CStatic *)GetDlgItem(IDC_STATICTIMER);
CString sTxt;
sTxt.Format("%u",m_uTimer);
pStxtTimer->SetWindowText(sTxt);
}
if(m_uTimer<=0 || !m_iOne)
{
m_iOne=1;
m_uTimer=m_uMXTIMER;
if(getRouterAccounting())
checkCreateThreads();
}
clearTHREADS();
CDialog::OnTimer(nIDEvent);
}
The data read from router in the procedure:
bool CIPtrafficDlg::getRouterAccounting(void)
I need to say some words about how the conversation between program and router happens.
Here is the config of dialog in this conversation:
Data in square brackets needs to be changed by your secure data, in order to connect to the router and execute some commands. The program sends data and waits for an answer as shown in this sample scripting dialog. You need to fill initial data in the properties dialog to connect database and router:
Here is the how the above dialogs can be called:
When the data read from router and inserted into the database, the SQL stored procedure is called (to split data from table into several tables for fast data extract for presentation):
execSQL(_T("Exec splitDataFast"));
Microsoft SQL stored procedure:
CREATE PROCEDURE [dbo].[splitDataFast]
AS
DECLARE @RDATE smalldatetime,@SNAME varchar(256),@DNAME varchar(256)
DECLARE @IPS char(15),@IPD char(15)
DECLARE @PACKETS int,@BYTES int
DECLARE @iTopID bigint
DECLARE @iSNAME bigint,@iDNAME bigint
DECLARE @iIPS int,@iIPD int
DECLARE iptraffic_cursor CURSOR FOR
SELECT ID,IPS,IPD,SNAME,DNAME,PACKETS,BYTES,RDATE FROM RAIN_TRAFFIC
begin
OPEN iptraffic_cursor
FETCH NEXT FROM iptraffic_cursor INTO @iTopID,@IPS,@IPD,@SNAME,_
@DNAME,@PACKETS,@BYTES,@RDATE
WHILE @@FETCH_STATUS = 0
begin
begin transaction
SET @iSNAME=(select ID from URL where URL=@SNAME)
if @iSNAME is NULL
begin
insert into URL(URL) VALUES (@SNAME)
SET @iSNAME=IDENT_CURRENT('URL')
end
if @@ERROR<>0
begin
print 'ERR' + 'insert into URL(URL) VALUES (@SNAME)'
print '@SNAME):'+@SNAME
break
end
SET @iDNAME=(select ID from URL where URL=@DNAME)
if @iDNAME is NULL
begin
insert into URL(URL) VALUES (@DNAME)
SET @iDNAME=IDENT_CURRENT('URL')
end
if @@ERROR<>0
begin
print 'ERR' + 'select ID from URL where URL=@DNAME'
print '@DNAME):'+@DNAME
break
end
SET @iIPS=(select ID from IPURL where IP=@IPS AND ID_URL=@iSNAME)
if @iIPS is NULL
begin
insert into IPURL(IP,ID_URL,RDATE) VALUES (@IPS,@iSNAME,@RDATE)
SET @iIPS=IDENT_CURRENT('IPURL')
end
if @@ERROR<>0
begin
print 'ERR' + 'insert into IPURL(IP,ID_URL) VALUES (@IPS,@iSNAME)'
print '(@IPS,@iDNAME):'+@IPS+','+STR(@iSNAME)
break
end
SET @iIPD=(select ID from IPURL where IP=@IPD AND ID_URL=@iDNAME)
if @iIPD is NULL
begin
insert into IPURL(IP,ID_URL,RDATE) VALUES (@IPD,@iDNAME,@RDATE)
SET @iIPD=IDENT_CURRENT('IPURL')
end
if @@ERROR<>0
begin
print 'ERR' + 'insert into IPURL(IP,ID_URL) VALUES (@IPS,@iDNAME)'
print '(@IPS,@iDNAME):'+@IPD+','+STR(@iDNAME)
break
end
Set @iTopID=(select ID from IPTRAFFIC where IDS=@iIPS and IDD=@iIPD and RDATE=@RDATE)
if @iTopID is NULL
insert into IPTRAFFIC(IDS,IDD,PACKETS,BYTES,RDATE) VALUES _
(@iIPS,@iIPD,@PACKETS,@BYTES,@RDATE)
if @@ERROR<>0
begin
print 'Can Not insert into IPTRAFFIC(IDS,IDD,PACKETS,BYTES,RDATE)'
print 'IDS:'+STR(@iIPS)
print 'IDD:'+STR(@iIPD)
print 'PACKETS:'+STR(@PACKETS)
print 'BYTES'+STR(@BYTES)
print 'RDATE'+CONVERT(VARCHAR(10),@RDATE)
end
delete from RAIN_TRAFFIC where CURRENT OF iptraffic_cursor
commit
FETCH NEXT FROM iptraffic_cursor INTO @iTopID,@IPS,@IPD,_
@SNAME,@DNAME,@PACKETS,@BYTES,@RDATE
end
CLOSE iptraffic_cursor
DEALLOCATE iptraffic_cursor
end
The above procedure takes data from the rain_iptraffic
table and fills the tables:
This is only an idea, you can create your own post working task!
Stored data in the database can be presented, for example by Pivot Tables in Excel, or in Microsoft SharePoint server with installed OWC. The result can look like:
IP traffic by week:
IP traffic by month:
The above WebParts in SharePoint pages use the views in Microsoft SQL table:
BytesIPhoursTODAYtotal
(from table: R_BytesIPURLhours
) BytesIPhoursWEEKtotal
(from table: R_BytesIPURLDays
) viewKBytesByDays
(from table: R_BytesIPURLDays
)
The stored procedures need to be executed regularly with different intervals:
Exec splitDataFast
(fill table: IPTRAFFIC
) Exec [dbo].spFillReportDaysTable 7
(fill table: R_BytesIPURLDays
) Exec spFillReportMonthTable
(fill table: R_BytesDaysTraffic
)
The data, stored in the database can be ranged by 3 months:
delete from IPTRAFFIC where datediff(m,rdate,getdate())>3
History