Proxy Server and Firewall Based on Soft Linux (2

Article Title: proxy server and Firewall Based on Soft Linux (2. Linux is a technology channel of the IT lab in China. Includes basic categories such as desktop applications, Linux system management, kernel research, embedded systems, and open source.
Author: Li Yu
　　
Traffic Statistics
　　
Traffic Statistics are implemented using the statistics function provided by ntop. During the statistics process, ntop creates a data list in the memory to update the data information instantly. At the same time, it provides the function of regularly sending relevant data to the database. The time interval can be set before compilation. We can define ntop statistics based on actual needs. Use ntop to update data to the MySQL database and regularly store data to the backup database table. The regular data storage function is implemented using select _ test. c, and the ntop Traffic Value and ntop default database table in the memory are refreshed.
　　
With the statistical data stored in the MySQL database, you can use PHP to write dynamic webpages to query and collect statistics on the data in the database. The specific implementation method is relatively simple. If you need to know how to build Apache + MySQL + PHP for traffic query, you can find related technical documents on many Linux technical exchange websites.
　　
Because the ntop record information is very detailed, some protocols are rarely used, or the Generated Traffic is small, which can be ignored, we need to adjust the data of the ntop record.
　　
The following work simplifies the data table structure (retain the required fields to simplify the stored data), creates a backup database table test, and modifies the SQL statement in the ntop source code. c. Simplify the data added by default ntop to the MySQL database; write a backup program for the database table data; set the time for ntop to refresh the database, ensure that the time for refreshing the memory is less than the set cycle time (see ntop. h); set the NIC to non-hybrid mode; write a script to complete the cyclic statistics function.
　　
Simplified data table structure (mysqldefs.txt)
　　
Create table IPtraffic
(
IPaddress CHAR (16) not null,
TCPsentRemotely INT,
TCPrcvdFromRemote INT,
UDPsentRemotely INT,
UDPrcvdFromRemote INT,
Primary key (IPaddress)
);
Create table NameMapper
(
IPaddress CHAR (16) not null,
Name CHAR (50 ),
Primary key (IPaddress)
);
　　
　　
Create a backup database table test
　　
Field Type Null Key Default Extra
IPaddress varchar (20) YES NULL
Data int (11) YES NULL
Dates date YES NULL
　　
　　
Modify SQL. c In ntop source code
　　
For example:
　　
If (snprintf (sqlBuf, SQL _BUF_LEN, "UPDATE IPtraffic SET"
"TCPSentRemotely = % llu ,"
"TCPrcvdFromRemote = % llu ,"
"UDPSentRemotely = % llu ,"
"UDPrcvdFromRemote = % llu"
"WHERE IPaddress = '% S '",
(El-> tcpSentRemotely ),
(El-> tcpReceivedFromRemote ),
(El-> udpSentRemotely ),
(El-> udpReceivedFromRemote ),
El-> hostNumIpAddress) <0) traceEvent (TRACE_ERROR, "Buffer overflow! ");
Sendto (sqlSocket, sqlBuf, strlen (sqlBuf), 0, (struct sockaddr *) & dest,
Sizeof (dest ));
　　
Retain the required fields and remove other fields!
　　
Compile the database table data backup program select_test.c Compilation Program (run make at compile time)
　　
Makefile:
# This is a Makefile for select_test.c
CC = gcc
INCLUDE =-I/usr/include/mysql
LIBPATH =-L/usr/lib/mysql
LLIB =-lmysqlclient
Select_test: select_test.c
$ (CC)-o select_test select_test.c $ (INCLUDE) $ (LIBPATH) $ (LLIB)
　　
Database Table data backup program select_test.c:
　　
# Include
# Include
# Include "mysql. h"
# Include
# Include
# Define SELECT_QUERY "select
IPaddress, (UDPrcvdFromRemote + UDPsentRemotely + TCPrcv
DFromRemote + TCPsentRemotely) as data from IPtraffic where IPaddress like
\ "% S \" or IPaddress like \ "% s \""
# Define INSERT_QUERY "insert into test (IPaddress, data, dates) values
(\ "% S \", % d, \ "% s \")"
# Define TEST_QUERY "select dates from test where dates = \" % s \""
/* Get the current date */
Getdates (char * currentdate)
{
Struct timeval datetemp;
Struct tm * datetemp1;
Gettimeofday (& datetemp, (void *) NULL );
Datetemp1 = localtime (& (datetemp. TV _sec ));
Sprintf (currentdate, "% d-% 02d-% 02d", datetemp1-> maid + 1900, datetemp1-> tm_mon + 1,
Datetemp1-> tm_mday );
}
Int main (int argc, char ** argv)
{
Unsigned int num_fields;
Unsigned int I;
Int count, num, charge;
MYSQL mysql, * sock;
MYSQL_RES * res;
MYSQL_FIELD ** fields;
MYSQL_ROW row;
Char selectqbuf [1024];
Char insertqbuf [1024];
Char testqbuf [512];
Char currentdate [11];
Currentdate [10] = 0;
If (argc! = 3)
{
Fprintf (stderr, "usage: select_test \ N "); # Set the CIDR Block for Statistics
Exit (1 );
}
Mysql_init (& mysql );
If (! (Sock = mysql_real_connect (& mysql, NULL, 0, 0, "NTOP", 0, NULL, 0 )))
{
Fprintf (stderr, "Couldn't connect to engine! \ N % s \ n ", mysql_error (& mysql ));
Perror ("");
Exit (1 );
}
/* In ntop databases, if have some recorders same as currentdate, don't in
Sert them to data 'table */
Getdates (currentdate );
Charge = 1;
Sprintf (testqbuf, TEST_QUERY, currentdate );
If (mysql_query (sock, testqbuf ))
{
Fprintf (stderr, "Query failed (% s) \ n", mysql_error (sock ));
Exit (1 );
}
If (! (Res = mysql_store_result (sock )))
{
Fprintf (stderr, "Couldn't get result from % s \ n ",
Mysql_error (sock ));
Exit (1 );
}
I = mysql_num_rows (res );
If (I =-1)
{
Printf ("couldn't run test select with currentdate \ n ");
}
If (I! = 0) & (I! =-1 ))
{
Charge = 0;
Printf ("IN test table, having
Some recorders have ded currentdate, error! \ N ");
Exit (1 );
}
Mysql_free_result (res );
Mysql_close (sock );
If (! (Sock = mysql_real_connect (& mysql, NULL, 0, 0, "NTOP", 0, NULL, 0 )))
{
Fprintf (stderr, "Couldn't connect
Engine! \ N % s \ n ", mysql_error (& mysql ));
Perror ("");
Exit (1 );
}
/# Sleep (100 )#/
Sprintf (selectqbuf, SELECT_QUERY, argv [1], argv [2]);
If (mysql_query (sock, selectqbuf ))
{
Fprintf (stderr, "Query failed (% s) \ n", mysql_error (sock ));
Exit (1 );
}
If (! (Res = mysql_store_result (sock )))
{
Fprintf (stderr, "Couldn't get result from % s \ n ",
Mysql_error (sock ));
Exit (1 );
}
　　　　　　
While (row = mysql_fetch_row (res) & charge)
{
Num = atoi (row [1]);
Sprintf (insertqbuf, INSERT_QUERY, row [0], num, currentdate );
Mysql_query (sock, insertqbuf );