//
you're reading...
Apache, howto, Linux, OpenSource, scripting, security, Solutions, System Integration

MySQL Driven Apache NCSA Log Parser – Linux Ansi C (gcc)

mysql> desc apacheLog;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| myDATE  | datetime     | NO   |     | NULL    |       |
| hosts   | varchar(255) | NO   |     | NULL    |       |
| method  | char(4)      | NO   |     | NULL    |       |
| url     | varchar(255) | NO   |     | NULL    |       |
| auth    | varchar(50)  | YES  |     | NULL    |       |
| browser | varchar(50)  | YES  |     | NULL    |       |
| referer | varchar(255) | YES  |     | NULL    |       |
| status  | smallint(3)  | NO   |     | NULL    |       |
| bytes   | int(8)       | YES  |     | 0       |       |
+---------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

mysql>




/*********************************************************************/
/* Compile as follows                                                */
/* gcc apache2mysql.c -o apache2mysql `mysql_config --cflags --libs` */
/*********************************************************************/
/******************************************Apache configuration**************************************/
/*1. Logging to Disk                                                                                */
/*                                                                                                  */
/*LogFormat "\"%{%Y-%m-%d %H:%M:%S}t\" %h \"%r\" %u \"%{User-agent}i\" %{Referer}i %s %b" pilio_log */
/*CustomLog logs/access_log pilio_log                                                               */
/*    OR                                                                                            */
/*2. Realtime Logging to MySql                                                                      */
/*LogFormat "\"%{%Y-%m-%d %H:%M:%S}t\" %h \"%r\" %u \"%{User-agent}i\" %{Referer}i %s %b" mysql     */
/*CustomLog "| /usr/bin/apache2mysql" mysql                                                         */
/****************************************************************************************************/
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
int main() {
// MySQL Relevant Connection settings
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char *server = "localhost";
char *user = "apache_usr";
char *password = "mypassw";
char *database = "apacheLog";
char st1[500]="insert into access(myDATE, hosts, method, url, browser) values(";
char statement[500]="";
int n;
char c[8192];  /* declare a char array */
FILE *file;  /* declare a FILE pointer  */
char o[100]="", f[100]="",s[100]="",t[100]="",a[400]="",b[400]="",cc[400]="",d[400]="";
int result=0;
conn = mysql_init(NULL);
file = fopen("/tmp/access_log", "r");
/* open a text file for reading               */
/* file=stdin;                                */
/* if stdin is used, Apache can be configured */
/* to log to MySQL in real time               */
/* Check the Apache configuration settings    */
/* above                                      */
if(file==NULL) {
printf("Error: can't open file.\n");
fclose(file); /* DON'T PASS A NULL POINTER TO fclose !! */
return 1;
}
else {
while(fgets(c, 8192, file)!=NULL) {
result = sscanf(c, "\"%[^'\"']\"  %[^' '] \"%[^'\"']\" %[^' '] \"%[^'\"']\" %[^' '] %[^' '] %[^'\n'] ", o, s, t, f, a ,b,cc,d);
if (strcmp(s,"::1") !=0) {
printf("%s + %s + %s + %s + %s + %s + %s + %s \n", o,s,t,f,a,b,cc,d);
conn = mysql_init(NULL);
/* Connect to database */
if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(conn));
exit(0);
} //endif
/* send SQL STMT */
n=sprintf (statement, "%s'%s','%s','%s','%s','%s')", st1, o, s, t, f, a);
printf("====>>%s\n",statement);
mysql_query(conn,statement);
mysql_close(conn);
} //endif
} //endwhile
fclose(file);
} //endelse
return 0;
}
#./apache2mysql
2008-10-27 12:18:40 + 10.70.100.22 + GET / HTTP/1.1 + - + Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648) + - + 200 + 223
====>>insert into access(myDATE, hosts, method, url, browser) values('2008-10-27 12:18:40','10.70.100.22','GET / HTTP/1.1','-','Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)')
2008-10-27 12:18:40 + 10.70.100.22 + GET /favicon.ico HTTP/1.1 + - + Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648) + - + 200 + 894
====>>insert into access(myDATE, hosts, method, url, browser) values('2008-10-27 12:18:40','10.70.100.22','GET /favicon.ico HTTP/1.1','-','Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)')
2008-10-27 12:20:57 + 10.70.100.22 + GET /server-sttus HTTP/1.1 + - + Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648) + - + 404 + 294
====>>insert into access(myDATE, hosts, method, url, browser) values('2008-10-27 12:20:57','10.70.100.22','GET /server-sttus HTTP/1.1','-','Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)')
2008-10-27 12:21:04 + 10.70.100.22 + GET /server-satus HTTP/1.1 + - + Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648) + - + 404 + 294
====>>insert into access(myDATE, hosts, method, url, browser) values('2008-10-27 12:21:04','10.70.100.22','GET /server-satus HTTP/1.1','-','Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)')
2008-10-27 12:21:10 + 10.70.100.22 + GET /server-status HTTP/1.1 + - + Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648) + - + 200 + 26589
====>>insert into access(myDATE, hosts, method, url, browser) values('2008-10-27 12:21:10','10.70.100.22','GET /server-status HTTP/1.1','-','Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648)')

Discussion

Comments are closed.

%d bloggers like this: