作者：李宇 流量統計 流量統計使用ntop提供的統計功能來實現。ntop在統計過程中，會在內存中創建一個數據列表，即時更新數據信息。同時，它提供定時向數據庫中發送相關數據的功能，時間間隔可以在編譯前設置。我們可以根據實際需要定義ntop統計的數據。利用ntop提供的向mysql數據庫中更新數據的方法，定期存儲數據到備份數據庫表中。定期存儲數據的功能使用select _test.c實現，同時刷新內存中ntop流量值及ntop默認數據庫表。 有了保存於MySQL數據庫中的統計數據，通過來用PHP編寫動態網頁，完成數據庫中數據的查詢和統計。具體的實現方法比較簡單。如果需要了解如何構建Apache+MySQL+PHP完成流量查詢，可以在很多Linux技術交流網站查找到相關的技術文檔。 由於ntop記錄的信息非常詳細，有些協議是很少使用的，或是產生的流量比較少，可以忽略不計，所以我們要對ntop記錄的數據做一調整。 以下所做的工作有簡化數據表結構（保留我們需要的字段，簡化保存的數據）；創建備份數據庫表test；修改ntop源代碼中的sql.c，簡化默認的ntop向MySQL數據庫中添加的數據；編寫數據庫表數據的備份程序；設定ntop對數據庫的刷新時間，保證刷新內存的時間小於我們設定的循環時間（參見ntop.h）；將網卡設置為非混雜模式；編寫腳本完成循環統計功能。 簡化後的數據表結構（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) ); 創建備份數據庫表test Field Type Null Key Default Extra IPaddress varchar(20) YES NULL data int(11) YES NULL dates date YES NULL 修改ntop源代碼中的sql.c 例如： 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)); 將需要的字段保留，其它的去除！ 編寫數據庫表數據的備份程序select_test.c的編譯程序（編譯時運行make） 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) 數據庫表數據備份程序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\"" /*獲取當前日期*/ getdates(char *currentdate) { struct timeval datetemp; struct tm * datetemp1; gettimeofday(&datetemp,(void *)NULL); datetemp1 = localtime(&(datetemp.tv_sec)); sprintf(currentdate,"%d-%02d-%02d",datetemp1->tm_year+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\n");＃設定需要統計的網段 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\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 included 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 to engine!\n%s\n\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);
