apache log in mysql

Beitrag von **Shqiperia** » 08.02.2005, 10:48

Hallo!

Kennt jemand von euch ein fertiges PHP oder Perl Skript, mit dem man Apache Logfiles in MySQL importieren kann? Hab bereits gegoogelt, aber nichts gefunden außer einem Apache-Zusatzmodul, aber das geht in meinem Fall nicht.

Gruß
S.

von **Anzeige von ABAKUS** »

SEO Consulting bei ABAKUS Internet Marketing
Erfahrung seit 2002

persönliche Betreuung
individuelle Beratung
kompetente Umsetzung

Jetzt anfragen: 0511 / 300325-0.

Beitrag von **bull** » 08.02.2005, 13:51

eregi notfalls minimal anpassen.

Code: Alles auswählen

<?php
$i=0;

function from_apachedate&#40;$date&#41;
&#123;
	list&#40;$d, $M, $y, $h, $m, $s, $z&#41; = sscanf&#40;$date, "%2d/%3s/%4d&#58;%2d&#58;%2d&#58;%2d %5s"&#41;;
	return date&#40;"Y-m-d h&#58;m&#58;s", strtotime&#40;"$d $M $y $h&#58;$m&#58;$s"&#41;&#41;;
&#125;

//-----------------------

$dbconnection = @mysql_connect&#40;"127.0.0.1",'root',"kennwort"&#41;; 
  if &#40;$dbconnection&#41; 
    $db = mysql_select_db&#40;"logs"&#41;;
  if &#40;!$dbconnection || !$db&#41; 
  &#123; echo mysql_error&#40;&#41;;  &#125;

$pfad='d&#58;\\ordner\\access.log.05';
$fp = fopen&#40;$pfad, 'r'&#41;;
$inhalt = fread &#40;$fp, filesize &#40;$pfad&#41;&#41;;
fclose&#40;$fp&#41;;

$tok = strtok &#40;$inhalt,"\r\n"&#41;;
while &#40;$tok&#41; 
&#123;
	if&#40;eregi&#40;"&#40;.*&#41; - - \&#91;&#40;.*&#41;\&#93; \"GET &#40;.*&#41; HTTP\/1\.&#40;&#91;0-1&#93;&#41;\" &#40;.*&#41; &#40;.*&#41; &#40;.*&#41; \"&#40;.*&#41;\" \"&#40;.*&#41;\" \"&#40;.*&#41;\"", $tok, $analy&#41; &#41;
	&#123;
		$date = from_apachedate&#40;$analy&#91;2&#93;&#41;;
		
		$uri = addslashes&#40;mysql_escape_string&#40;$analy&#91;3&#93;&#41;&#41;;
		$referer = addslashes&#40;mysql_escape_string&#40;$analy&#91;8&#93;&#41;&#41;;
		$ua = addslashes&#40;mysql_escape_string&#40;$analy&#91;9&#93;&#41;&#41;;
	
		$query = "INSERT INTO logz &#40;REMOTE_ADDR, REQUEST_URI, STATUSCODE, SIZE, HTTP_REFERER, HTTP_USER_AGENT, HTTP_VIA, TIME&#41; VALUES &#40;'$analy&#91;1&#93;', '$uri', $analy&#91;5&#93;, '$analy&#91;6&#93;', '$referer', '$ua', '$analy&#91;10&#93;', '$date'&#41;; ";
    @mysql_query&#40;$query&#41;;
		if&#40;mysql_errno&#40;&#41; > 0&#41;
		&#123;
			print mysql_error&#40;&#41;;
			print $query;
		&#125;
    echo '. ';
	&#125;
	$tok = strtok &#40;"\r\n"&#41;;
&#125;
mysql_close&#40;$dbconnection&#41;;

?>

Beitrag von **Shqiperia** » 16.02.2005, 09:40

Danke, Herr Moderator!
Das funktioniert sehr gut, es ist aber bei grossen Logdateien etwas langsam. Wenn ich verstehe richtig den Code, es liegt an strtok, das das ganze Logfile bekommt.

Beitrag von **bull** » 16.02.2005, 10:11

strtok

Das ist richtig, ich habe diesen PHP-Code schnell umgeschrieben. Ich gebe zu, daß ich nicht weiß, ob es Zeiger bei Strings in PHP gibt.
Eigentlich verwende ich ein kleines C++ - Programm unter Windows, es sollte für Linux aber auch mit kleinen Änderungen funktionieren (vorsicht, CMySQL ist nicht vorbildlicher Programmierstil). Wenn man PHP kann, ist es nicht schwer, den Code zu verstehen. Vielleicht kann es jemand brauchen?

Code: Alles auswählen

// logtomysql.cpp &#58; Definiert den Einsprungpunkt für die Konsolenanwendung.
//

#define WIN32_LEAN_AND_MEAN
#include "stdafx.h"
#include <stdio.h>

#include <windows.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <memory.h>
#include <winsock.h>

#define USE_OLD_FUNCTIONS
#include <mysql.h>

//------------------------------------------------

static char g_query&#91;16384&#93;;

//------------------------------------------------

class CMySQL
&#123;
public&#58;
   __forceinline my_ulonglong NumRows&#40;MYSQL_RES* pRes&#41;;
   __forceinline void QueryNoResult&#40;LPCTSTR lpszQuery&#41;;
   __forceinline unsigned FetchRow0AsUnsigned&#40;MYSQL_RES* res&#41;;
   CMySQL&#40;LPCSTR lpszServer, LPCSTR lpszUser, LPCSTR lpszPwd, LPCSTR lpszDB&#41;;
   __forceinline void OnError&#40;unsigned line, LPCSTR g_query&#41;;
   __forceinline MYSQL_RES*  Query&#40;LPCSTR lpszQuery&#41;;
   __forceinline MYSQL_ROW FetchRow&#40;MYSQL_RES* res&#41;;
   __forceinline LPSTR FetchRow0&#40;MYSQL_RES* res&#41;;
   void Close&#40;&#41;;
   __forceinline LPSTR EscapeString&#40;LPSTR string&#41;;
   __forceinline my_ulonglong InsertID&#40;&#41;;
   __forceinline MYSQL* GetConnectionHandle&#40;&#41;;
protected&#58;
   char buffer&#91;16384&#93;;
   MYSQL* connection;
   MYSQL_RES* g_anyrequest;
&#125;;

//------------------------------------------------

__forceinline my_ulonglong CMySQL&#58;&#58;NumRows&#40;MYSQL_RES *pRes&#41;
&#123;
	return mysql_num_rows&#40;pRes&#41;;
&#125;

//------------------------------------------------

__forceinline MYSQL* CMySQL&#58;&#58;GetConnectionHandle&#40;&#41;
&#123;
   return connection;
&#125;

//------------------------------------------------

__forceinline my_ulonglong CMySQL&#58;&#58;InsertID&#40;&#41;
&#123;
   return mysql_insert_id&#40;connection&#41;;
&#125;

//------------------------------------------------

__forceinline LPSTR CMySQL&#58;&#58;EscapeString&#40;LPSTR string&#41;
&#123;
   mysql_real_escape_string&#40;connection, buffer, string, lstrlen&#40;string&#41;&#41;;
   return buffer;
&#125;

//------------------------------------------------

__forceinline void CMySQL&#58;&#58;QueryNoResult&#40;LPCTSTR lpszQuery&#41;
&#123;
   mysql_query&#40;connection, lpszQuery&#41;;
   g_anyrequest = mysql_store_result&#40;connection&#41;;

   if&#40;mysql_errno&#40;connection&#41; > 0&#41;
      printf&#40;"%s\r\n%s\r\n", mysql_error&#40;connection&#41;, g_query&#41;;
   mysql_free_result&#40;g_anyrequest&#41;;
&#125;

//------------------------------------------------

void CMySQL&#58;&#58;Close&#40;&#41;
&#123;
   mysql_close&#40;connection&#41;;
&#125;

//------------------------------------------------

__forceinline MYSQL_ROW CMySQL&#58;&#58;FetchRow&#40;MYSQL_RES* res&#41;
&#123;
   return mysql_fetch_row&#40;res&#41;;
&#125;

//------------------------------------------------

__forceinline LPSTR CMySQL&#58;&#58;FetchRow0&#40;MYSQL_RES* res&#41;
&#123;
   MYSQL_ROW row = mysql_fetch_row&#40;res&#41;;
   return row&#91;0&#93;;
&#125;

//------------------------------------------------

__forceinline unsigned CMySQL&#58;&#58;FetchRow0AsUnsigned&#40;MYSQL_RES *res&#41;
&#123;
   MYSQL_ROW row = mysql_fetch_row&#40;res&#41;;
   return &#40;unsigned&#41;atoi&#40;row&#91;0&#93;&#41;;
&#125;

//------------------------------------------------

CMySQL&#58;&#58;CMySQL&#40;LPCSTR lpszServer, LPCSTR lpszUser, LPCSTR lpszPwd, LPCSTR lpszDB&#41;
&#123;
   connection = mysql_init&#40;NULL&#41;;
   if&#40; !mysql_real_connect&#40;connection, lpszServer, lpszUser, lpszPwd, lpszDB, 0, NULL, 0&#41; &#41;
      OnError&#40;0, "mysql_real_connect"&#41;;
   if&#40; mysql_select_db&#40;connection, lpszDB&#41; &#41;
      OnError&#40;0, "mysql_select_db"&#41;;   
&#125;

//------------------------------------------------

__forceinline MYSQL_RES* CMySQL&#58;&#58;Query&#40;LPCSTR lpszQuery&#41;
&#123;
   mysql_query&#40;connection, lpszQuery&#41;;
   MYSQL_RES* ancc = mysql_store_result&#40;connection&#41;;

   if&#40;mysql_errno&#40;connection&#41; > 0&#41;
      printf&#40;"%s\r\n%s\r\n", mysql_error&#40;connection&#41;, g_query&#41;;

   return ancc;
&#125;

//------------------------------------------------

__forceinline void CMySQL&#58;&#58;OnError&#40;unsigned line, LPCSTR g_query&#41;
&#123;
   if&#40;mysql_errno&#40;connection&#41; > 0&#41;
      printf&#40;"\r\nZeile %d &#58; %s\r\n%s\r\n", line, mysql_error&#40;connection&#41;, g_query&#41;;
&#125;

//------------------------------------------------

char buf2&#91;4096&#93;;
char values&#91;4096&#93;;
char buf3&#91;1024&#93;;
char tx1&#91;16&#93;;
char tx2&#91;16&#93;;
char szLogExtOld&#91;6&#93;;

typedef struct tagLOGLINE
&#123;
   char ip&#91;32&#93;;
   char date&#91;32&#93;;
   char method&#91;32&#93;;
   char uri&#91;4096&#93;;
   char code&#91;8&#93;;
   char size&#91;16&#93;;
   char host&#91;128&#93;;
   char referer&#91;4096&#93;;
   char ua&#91;512&#93;;
   my_ulonglong uaid;
   char via&#91;32&#93;;
   DWORD dwIP;
&#125; LOGLINE;

static LOGLINE l;
static LPSTR base;
static LPSTR point;
static unsigned d;

//------------------------------------------------

static char szOldUA&#91;512&#93;;
static my_ulonglong idOld;
static char szSQLID&#91;1024&#93;;

__forceinline my_ulonglong GetUAID&#40;CMySQL* connection, LOGLINE* pLine&#41;
&#123;
	if&#40;0 == lstrcmp&#40;pLine->ua, szOldUA&#41;&#41;
		return idOld;

redo&#58;
	my_ulonglong idx = 0;
	wsprintf&#40;szSQLID, "SELECT idx FROM ua WHERE HTTP_USER_AGENT='%s';", pLine->ua&#41;;
	MYSQL_RES* pRes = connection->Query&#40;szSQLID&#41;;
	if&#40;connection->NumRows&#40;pRes&#41; > 0&#41;
		idx = connection->FetchRow0AsUnsigned&#40;pRes&#41;;
	else
	&#123;
		wsprintf&#40;szSQLID, "INSERT INTO ua &#40;HTTP_USER_AGENT&#41; VALUES &#40;'%s'&#41;;", pLine->ua&#41;;
		connection->QueryNoResult&#40;szSQLID&#41;;
		printf&#40;"+ "&#41;;
		goto redo;
	&#125;
	mysql_free_result&#40;pRes&#41;;
	lstrcpy&#40;szOldUA, pLine->ua&#41;;
	idOld = idx;
	return idx;
&#125;

//------------------------------------------------

__forceinline void ConvertIP&#40;LOGLINE* pLine&#41;
&#123;
	BYTE a, b, c, d;

	LPSTR base = pLine->ip;
	LPSTR lpsz = strchr&#40;base, '.'&#41;;
	ZeroMemory&#40;tx1, sizeof&#40;tx1&#41;&#41;;
	memcpy&#40;tx1, base, lpsz-base&#41;;
	a = atoi&#40;tx1&#41;;
	base = lpsz + 1;

	lpsz = strchr&#40;base, '.'&#41;;
	ZeroMemory&#40;tx1, sizeof&#40;tx1&#41;&#41;;
	memcpy&#40;tx1, base, lpsz-base&#41;;
	b = atoi&#40;tx1&#41;;
	base = lpsz + 1;

	lpsz = strchr&#40;base, '.'&#41;;
	ZeroMemory&#40;tx1, sizeof&#40;tx1&#41;&#41;;
	memcpy&#40;tx1, base, lpsz-base&#41;;
	c = atoi&#40;tx1&#41;;
	base = lpsz + 1;

	d = atoi&#40;base&#41;;

	pLine->dwIP = d + &#40;c << 8&#41; + &#40;b << 16&#41; + &#40;a << 24&#41;;
&#125;

//------------------------------------------------

char szLogExt&#91;6&#93;;

__forceinline void DoInserts&#40;CMySQL* connection, LPSTR szLogExt&#41;
&#123;
	szLogExt&#91;0&#93; = l.date&#91;2&#93;;
	szLogExt&#91;1&#93; = l.date&#91;3&#93;;
	szLogExt&#91;2&#93; = '_';
	szLogExt&#91;3&#93; = l.date&#91;5&#93;;
	szLogExt&#91;4&#93; = l.date&#91;6&#93;;
	szLogExt&#91;5&#93; = 0;
	
	// ua-id
   l.uaid = GetUAID&#40;connection, &l&#41;;
   _i64toa&#40;l.uaid, tx1, 10&#41;;
   sprintf&#40;tx2, "%u", l.dwIP&#41;;


   // tabelle 
   if&#40;0 != strcmp&#40;szLogExtOld, szLogExt&#41;&#41;
   &#123;
	   sprintf&#40;buf2, "CREATE TABLE IF NOT EXISTS `_logs%s` &#40;`idx` int&#40;11&#41; NOT NULL auto_increment, `REMOTE_ADDR` varchar&#40;16&#41; NOT NULL default '',`REMOTE_ADDR_NUM` int&#40;11&#41; default NULL, `TIME` timestamp NOT NULL, `METHOD` varchar&#40;8&#41; NOT NULL default '', `REQUEST_URI` text NOT NULL, `STATUSCODE` int&#40;11&#41; NOT NULL default '0', `SIZE` int&#40;11&#41; default NULL, `HTTP_REFERER` text NOT NULL, `UA_ID` int&#40;11&#41; default NULL, `HTTP_VIA` varchar&#40;16&#41; NOT NULL default '', PRIMARY KEY  &#40;`idx`&#41;,  KEY `ip` &#40;`REMOTE_ADDR`&#41;, KEY `ua` &#40;`UA_ID`&#41;&#41; TYPE=MyISAM ;", szLogExt&#41;;
	   connection->Query&#40;buf2&#41;;
	   connection->Query&#40;"DROP TABLE logs"&#41;;

	   lstrcpy&#40;szLogExtOld, szLogExt&#41;;

	   lstrcpy&#40;buf2, "SHOW TABLES LIKE '\\_logs%%';"&#41;; 
	   MYSQL_RES* resTables = connection->Query&#40;buf2&#41;;

	   unsigned ky = 0;
	   unsigned rows_ky = &#40;unsigned&#41;mysql_num_rows&#40;resTables&#41;;

	   // komplette MERGE erstellen
	   lstrcpy&#40;buf2, "CREATE TABLE IF NOT EXISTS `logs` &#40;`idx` int&#40;11&#41; NOT NULL auto_increment, `REMOTE_ADDR` varchar&#40;16&#41; NOT NULL default '',`REMOTE_ADDR_NUM` int&#40;11&#41; default NULL, `TIME` timestamp NOT NULL, `METHOD` varchar&#40;8&#41; NOT NULL default '', `REQUEST_URI` text NOT NULL, `STATUSCODE` int&#40;11&#41; NOT NULL default '0', `SIZE` int&#40;11&#41; default NULL, `HTTP_REFERER` text NOT NULL, `UA_ID` int&#40;11&#41; default NULL, `HTTP_VIA` varchar&#40;16&#41; NOT NULL default '', PRIMARY KEY  &#40;`idx`&#41;,  KEY `ip` &#40;`REMOTE_ADDR`&#41;, KEY `ua` &#40;`UA_ID`&#41;&#41; TYPE=MERGE UNION=&#40;"&#41;;
		while&#40;ky < rows_ky-1&#41;
		&#123;
			lstrcat&#40;buf2, connection->FetchRow0&#40;resTables&#41;&#41;;
			lstrcat&#40;buf2, ", "&#41;;
			ky++;
		&#125;
		lstrcat&#40;buf2, connection->FetchRow0&#40;resTables&#41;&#41;;
		lstrcat&#40;buf2, "&#41;;"&#41;;
		connection->Query&#40;buf2&#41;;
   &#125;

   // jetzt SQL
   sprintf&#40;buf2, "INSERT INTO _logs%s &#40;REMOTE_ADDR, REQUEST_URI, STATUSCODE, SIZE, HTTP_REFERER, UA_ID, HTTP_VIA, TIME, METHOD, REMOTE_ADDR_NUM&#41; VALUES &#40;'%s', '%s', %s, '%s', '%s', '%s', '%s', '%s', '%s', '%s'&#41;; ", 
	   szLogExt, l.ip, l.uri, l.code, l.size, l.referer, tx1, l.via, l.date, l.method, tx2&#41;;
   connection->Query&#40;buf2&#41;;
&#125;

//------------------------------------------------

__forceinline LPSTR ReadLogLine1und1&#40;CMySQL* connection, LPSTR szCurrent&#41;
&#123;
   // Log-Format&#58;
   // 82.39.117.93 - - &#91;08/Feb/2005&#58;00&#58;09&#58;54 +0100&#93; "GET /test.css HTTP/1.1" 200 2972 www.hostname.net "http&#58;//www.hostname.net/foo/bar.htm" "Mozilla/4.0 &#40;compatible; MSIE 6.0; Windows NT 5.1; SV1&#41;" "-"
   ZeroMemory&#40;&l, sizeof&#40;l&#41;&#41;;

   LPSTR szCurrent2 = strchr&#40;szCurrent, '\n'&#41; + 1;

   int register len = 0;

   // ip
   base = szCurrent;
   point = strchr&#40;base, ' '&#41;;
   memcpy&#40;l.ip, base, point-base&#41;;

   base = point++;

 	ConvertIP&#40;&l&#41;;

   // datum
   point = strchr&#40;base, '&#93;'&#41; + 1;
   base = strchr&#40;base, '&#91;'&#41; + 1;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;

   memcpy&#40;buf3, base, point-base&#41;;
   memcpy&#40;tx1, buf3+3, 3&#41;;
   tx1&#91;3&#93; = 0;

   switch&#40;tx1&#91;0&#93;&#41;
   &#123;
      case 'J' &#58;
         if&#40;tx1&#91;1&#93; == 'a'&#41;
            d = 1;
         else if &#40;tx1&#91;2&#93; == 'l'&#41;
            d = 7;
         else d = 6;
         break;
      case 'F' &#58;
         d = 2;
         break;
      case 'M' &#58;
         if&#40;tx1&#91;2&#93; == 'r'&#41;
            d = 3;
         else if &#40;tx1&#91;2&#93; == 'y'&#41;
            d = 5;
         break;
      case 'A' &#58;
         if&#40;tx1&#91;1&#93; == 'p'&#41;
            d = 4;
         else
            d = 8;
         break;
      case 'S' &#58;
         d = 9;
         break;
      case 'O' &#58;
         d = 10;
         break;
      case 'N' &#58;
         d = 11;
         break;
      case 'D' &#58;
         d = 12;
         break;
   &#125;

   sprintf&#40;tx2, "%2.2d", d&#41;;

   l.date&#91;0&#93; = buf3&#91;7&#93;;
   l.date&#91;1&#93; = buf3&#91;8&#93;;
   l.date&#91;2&#93; = buf3&#91;9&#93;;
   l.date&#91;3&#93; = buf3&#91;10&#93;;
   l.date&#91;4&#93; = '-';
   l.date&#91;5&#93; = tx2&#91;0&#93;;
   l.date&#91;6&#93; = tx2&#91;1&#93;;
   l.date&#91;7&#93; = '-';
   l.date&#91;8&#93; = buf3&#91;0&#93;;
   l.date&#91;9&#93; = buf3&#91;1&#93;;
   l.date&#91;10&#93; = ' ';

   l.date&#91;11&#93; = buf3&#91;12&#93;;
   l.date&#91;12&#93; = buf3&#91;13&#93;;
   l.date&#91;13&#93; = buf3&#91;14&#93;;
   l.date&#91;14&#93; = buf3&#91;15&#93;;
   l.date&#91;15&#93; = buf3&#91;16&#93;;
   l.date&#91;16&#93; = buf3&#91;17&#93;;
   l.date&#91;17&#93; = buf3&#91;18&#93;;
   l.date&#91;18&#93; = buf3&#91;19&#93;;
   l.date&#91;19&#93; = 0;

   // meth
   base = point+2;
   point = strchr&#40;base, ' '&#41;;
   memcpy&#40;l.method, base, point-base&#41;;

   base = point+1;

   // uri
   point = strchr&#40;base, ' '&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 255&#41;
	   len=255;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.uri, buf3, lstrlen&#40;buf3&#41;&#41;;

   base = point + 2;
   base = strchr&#40;base, ' '&#41; + 1;

   // code
   point = strchr&#40;base, ' '&#41; ;
   memcpy&#40;l.code, base, point-base&#41;;

   base = ++point;

   // groesse
   point = strchr&#40;base, ' '&#41; ;
   memcpy&#40;l.size, base, point-base&#41;;

   base = ++point;

   // host
   point = strchr&#40;base, ' '&#41; ;
   memcpy&#40;l.host, base, point-base&#41;;

   base = point+2;

   // ref
   point = strchr&#40;base, '\"'&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 512&#41;
	   len=512;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.referer, buf3, lstrlen&#40;buf3&#41;&#41;;

   base = point+3;

   // ua
   point = strchr&#40;base, '\"'&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 255&#41;
	   len=255;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.ua, buf3, lstrlen&#40;buf3&#41;&#41;;

   base = point+3;

   // via
   point = strchr&#40;base, '\"'&#41; ;
   memcpy&#40;l.via, base, point-base&#41;;

   base = point+2;


	// jetzt inserten
   DoInserts&#40;connection, szLogExt&#41;;

   return szCurrent2;
&#125;

//------------------------------------------------

__forceinline LPSTR ReadLogLineCombined&#40;CMySQL* connection, LPSTR szCurrent&#41;
&#123;
   // Log-Format&#58;
   // 66.249.71.7 - - &#91;20/Feb/2005&#58;05&#58;11&#58;21 +0100&#93; "GET /robots.txt HTTP/1.0" 200 2936 "-" "Googlebot/2.1 &#40;+http&#58;//www.google.com/bot.html&#41;"

   ZeroMemory&#40;&l, sizeof&#40;l&#41;&#41;;

   LPSTR szCurrent2 = strchr&#40;szCurrent, '\n'&#41; + 1;

   int register len = 0;

   // ip
   base = szCurrent;
   point = strchr&#40;base, ' '&#41;;
   memcpy&#40;l.ip, base, point-base&#41;;

   base = point++;

 	ConvertIP&#40;&l&#41;;

   // datum
   point = strchr&#40;base, '&#93;'&#41; + 1;
   base = strchr&#40;base, '&#91;'&#41; + 1;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;

   memcpy&#40;buf3, base, point-base&#41;;
   memcpy&#40;tx1, buf3+3, 3&#41;;
   tx1&#91;3&#93; = 0;

   switch&#40;tx1&#91;0&#93;&#41;
   &#123;
      case 'J' &#58;
         if&#40;tx1&#91;1&#93; == 'a'&#41;
            d = 1;
         else if &#40;tx1&#91;2&#93; == 'l'&#41;
            d = 7;
         else d = 6;
         break;
      case 'F' &#58;
         d = 2;
         break;
      case 'M' &#58;
         if&#40;tx1&#91;2&#93; == 'r'&#41;
            d = 3;
         else if &#40;tx1&#91;2&#93; == 'y'&#41;
            d = 5;
         break;
      case 'A' &#58;
         if&#40;tx1&#91;1&#93; == 'p'&#41;
            d = 4;
         else
            d = 8;
         break;
      case 'S' &#58;
         d = 9;
         break;
      case 'O' &#58;
         d = 10;
         break;
      case 'N' &#58;
         d = 11;
         break;
      case 'D' &#58;
         d = 12;
         break;
   &#125;

   sprintf&#40;tx2, "%2.2d", d&#41;;

   l.date&#91;0&#93; = buf3&#91;7&#93;;
   l.date&#91;1&#93; = buf3&#91;8&#93;;
   l.date&#91;2&#93; = buf3&#91;9&#93;;
   l.date&#91;3&#93; = buf3&#91;10&#93;;
   l.date&#91;4&#93; = '-';
   l.date&#91;5&#93; = tx2&#91;0&#93;;
   l.date&#91;6&#93; = tx2&#91;1&#93;;
   l.date&#91;7&#93; = '-';
   l.date&#91;8&#93; = buf3&#91;0&#93;;
   l.date&#91;9&#93; = buf3&#91;1&#93;;
   l.date&#91;10&#93; = ' ';

   l.date&#91;11&#93; = buf3&#91;12&#93;;
   l.date&#91;12&#93; = buf3&#91;13&#93;;
   l.date&#91;13&#93; = buf3&#91;14&#93;;
   l.date&#91;14&#93; = buf3&#91;15&#93;;
   l.date&#91;15&#93; = buf3&#91;16&#93;;
   l.date&#91;16&#93; = buf3&#91;17&#93;;
   l.date&#91;17&#93; = buf3&#91;18&#93;;
   l.date&#91;18&#93; = buf3&#91;19&#93;;
   l.date&#91;19&#93; = 0;

	// meth
   base = point+2;
   point = strchr&#40;base, ' '&#41;;
   memcpy&#40;l.method, base, point-base&#41;;

   base = point+1;

   // uri
   point = strchr&#40;base, ' '&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 255&#41;
	   len=255;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.uri, buf3, lstrlen&#40;buf3&#41;&#41;;

   base = point + 2;
   base = strchr&#40;base, ' '&#41; + 1;

   // code
   point = strchr&#40;base, ' '&#41; ;
   memcpy&#40;l.code, base, point-base&#41;;

   base = ++point;

   // groesse
   point = strchr&#40;base, ' '&#41; ;
   memcpy&#40;l.size, base, point-base&#41;;

   base = point+2;

   // ref
   point = strchr&#40;base, '\"'&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 512&#41;
	   len=512;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.referer, buf3, lstrlen&#40;buf3&#41;&#41;;

   base = point+3;

   // ua
   point = strchr&#40;base, '\"'&#41; ;
   ZeroMemory&#40;buf3, sizeof&#40;buf3&#41;&#41;;
   len = point-base;
   if&#40;len > 255&#41;
	   len=255;
   memcpy&#40;buf3, base, len&#41;;
   mysql_real_escape_string&#40;connection->GetConnectionHandle&#40;&#41;, l.ua, buf3, lstrlen&#40;buf3&#41;&#41;;

	// jetzt inserten
   DoInserts&#40;connection, szLogExt&#41;;

   return szCurrent2;
&#125;

//------------------------------------------------

#define LOGFORMAT_1UND1		1
#define LOGFORMAT_COMBINED	2

__forceinline void LogToSQL&#40;CMySQL* id_connect, LPCSTR lpszFile, UINT type=LOGFORMAT_1UND1&#41;
&#123;
   printf&#40;"%s\r\n\r\n", lpszFile&#41;;
   int iStartTime = GetTickCount&#40;&#41;;

   HANDLE hf = CreateFile&#40;lpszFile, GENERIC_READ, FILE_SHARE_READ, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL&#41;;

   DWORD dh;
   DWORD reallen = &#58;&#58;GetFileSize&#40;hf, &dh&#41;;
   printf&#40;"Puffer&#58; %d\r\n", reallen&#41;;

   LPSTR szBuf = &#40;LPSTR&#41;malloc&#40;reallen + 4096&#41;;
   ZeroMemory&#40;szBuf, reallen + 4096&#41;;

   
   DWORD dwLen;
   &#58;&#58;ReadFile&#40;hf, szBuf, reallen, &dwLen, NULL&#41;;
   CloseHandle&#40;hf&#41;;

   LPSTR szCurrent = szBuf;
   LPSTR szCurrentOld = NULL;
   LPSTR lpszMax = szCurrent+dwLen-20;
   int plus = 0;

   if&#40;LOGFORMAT_COMBINED == type&#41;
   &#123;
	   while&#40;szCurrent < lpszMax&#41;
	   &#123;  
		  if&#40;!szCurrent&#41;
			 break;
		  szCurrent = ReadLogLineCombined&#40;id_connect, szCurrent&#41;;
		  plus ++;
	   &#125;
	&#125;
	else if &#40;LOGFORMAT_1UND1 == type&#41;
   &#123;
	   while&#40;szCurrent < lpszMax&#41;
	   &#123;  
		  if&#40;!szCurrent&#41;
			 break;
		  szCurrent = ReadLogLine1und1&#40;id_connect, szCurrent&#41;;
		  plus ++;
	   &#125;
	&#125;
   printf&#40;"%d Zeilen in %d msec\r\n\r\n", plus, GetTickCount&#40;&#41;-iStartTime&#41;;

   //---------------
   free&#40;szBuf&#41;;
&#125;

//------------------------------------------------


int main&#40;int argc, char* argv&#91;&#93;&#41;
&#123;
   unsigned int i = 0;
   unsigned long numRows = 0;
   ZeroMemory&#40;szOldUA, sizeof&#40;szOldUA&#41;&#41;;
   ZeroMemory&#40;szLogExtOld, sizeof&#40;szLogExtOld&#41;&#41;;

   CMySQL connection&#40;"127.0.0.1","root","pwd", "logs"&#41;;
   
   // altes beseitigen
//   connection.Query&#40;"truncate logs;"&#41;;
  // connection.Query&#40;"truncate ua;"&#41;;

LogToSQL&#40;&connection, "c&#58;\\access.log"&#41;;

   connection.Close&#40;&#41;;
   return 0;
&#125;

Kompiliert mit MS Visual C++ Toolkit, das es kostenlos bei https://msdn.microsoft.com/visualc/vctoolkit2003/ gibt mit Compiler-Optionen /G6 /Ox . Das geht wesentlich schneller

Gruß,

---
Nachtrag:
Tabellen-Struktur für User-agent-Tabelle

Code: Alles auswählen

CREATE TABLE `ua` &#40;
  `idx` int&#40;10&#41; unsigned NOT NULL auto_increment,
  `HTTP_USER_AGENT` text NOT NULL,
  `IS_BOT` tinyint&#40;1&#41; default NULL,
  PRIMARY KEY  &#40;`idx`&#41;,
  FULLTEXT KEY `UA` &#40;`HTTP_USER_AGENT`&#41;
&#41; TYPE=MyISAM COMMENT='user-agents'  ;

Beitrag von **Shqiperia** » 16.02.2005, 16:55

Vielen Dank! Ich werde das probieren.

Beitrag von **naden** » 18.02.2005, 18:50

Hi,
warum das Rad neu erfinden es gibt ein wunderbaren Modul f. d. Apache,
das man laden und in die DB logen kann. Rennt sehr zuverlässig.

Einfach mal in der Apache mod - Db suchen.

Beitrag von **bull** » 18.02.2005, 19:17

Hat Shqiperia doch gesagt, daß das in seinem/ihren Fall nicht geht.

Beitrag von **naden** » 19.02.2005, 15:51