Controlling IP traffic from Internet to DMZ or Intranet

• Download source - 50.41 KB
• Download EXE - 28.89 KB

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:

• IPS - the IP address from source host (external)
• IPD - the IP address to destination host (internal)
• BYTES - the number of bytes path through router
• RDATE - the time when line read from router in format:
  C++

  tCheckPoint=CTime::GetCurrentTime();
  CString sCheckTime=tCheckPoint.Format("{ts \'%Y-%m-%d %H:%M:%S\'}");

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;        // timer counter
    CButton *pRunning=(CButton *)GetDlgItem(IDC_CHECK_RUN_THREADs);
    if(pRunning->GetCheck()!=1)
    {
        m_bRun=-1;// stop
    }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
/*    IPTRAFFIC table    */
DECLARE @RDATE smalldatetime,@SNAME varchar(256),@DNAME varchar(256)
DECLARE @IPS char(15),@IPD char(15)
DECLARE @PACKETS int,@BYTES int
/*    R_IPTRAFFIC table    */
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    
/* URL Source Name     */
     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
/* URL Destination Name */
    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

/* IPURL (IP,ID_URL,RDATE) SOURCE*/

    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
/* IPURL (IP,ID_URL,RDATE) DESTINATION*/
    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
    
/*  IPTRAFFIC    */
    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:

• IPTRAFFIC
• IPURL
• URL

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

• Published 05.12.2011