투

[서버운영] 아파치로그] sql로 바로 저장.

아래글에 이어 아파치와 php-cli 를 연계하여 로그를 바로 sql 에 저장한다.
예제이므로 DB 클래스와 에러제어 부분은 따로 표기하지 않는다.

PostgreSQL 기준.

httpd.conf

CustomLog "| /etc/apache2/shjsqllog.php" shjsqllog

CREATE TABLE shjlog (
seq SERIAL NOT NULL,
ip CIDR NOT NULL,
port SMALLINT NOT NULL,
bytes BIGINT NOT NULL,
inbytes BIGINT NOT NULL,
outbytes BIGINT NOT NULL,
protocol VARCHAR(30) NOT NULL,
method VARCHAR(10) NOT NULL,
host VARCHAR(200) NOT NULL,
file VARCHAR(300) NOT NULL,
uri VARCHAR(500) NOT NULL,
status SMALLINT NOT NULL,
phpsessid CHAR(32) NOT NULL,
useragent VARCHAR(200) NOT NULL,
referer VARCHAR(500) NOT NULL,
responsetime INTEGER NOT NULL,
datetime TIMESTAMPTZ NOT NULL,
PRIMARY KEY (seq)
);

CREATE INDEX ip_dex ON shjlog (ip);
CREATE INDEX host_dex ON shjlog (host);
CREATE INDEX uri_dex ON shjlog (uri);
CREATE INDEX phpsessid_dex ON shjlog (phpsessid);
CREATE INDEX useragent_dex ON shjlog (useragent);
CREATE INDEX referer_dex ON shjlog (referer);


#!/usr/bin/php
<?php

include_once 'SHJ/debug.inc.php';
include_once 'SHJ/DB/PDOExt.inc.php';

$db = new SHJPDOExt('pgsql', '/etc/apache2/shjlog.ini');
$fields->ip = '';
$fields->port = '';
$fields->bytes = '';
$fields->inbytes = '';
$fields->outbytes = '';
$fields->protocol = '';
$fields->method = '';
$fields->host = '';
$fields->file = '';
$fields->uri = '';
$fields->status = '';
$fields->phpsessid = '';
$fields->useragent = '';
$fields->referer = '';
$fields->responsetime = '';
$fields->datetime = '';
$stmt = $db->insert('shjlog', $fields);

$fp = fopen('php://stdin', 'r');

while(true) {
    $line = fgets($fp, 4096);
    if(!preg_match('/^ip:"(.*?)" port:"(.*?)" bytes:"(.*?)" inbytes:"(.*?)" outbytes:"(.*?)" protocol:"(.*?)" method:"(.*?)" host:"(.*?)" file:"(.*?)" uri:"(.*?)" status:"(.*?)" phpsessid:"(.*?)" useragent:"(.*?)" referer:"(.*?)" responsetime:"(.*?)" datetime:"(.*?)"$/s', $line, $matches)) {
        continue;
    }
    $fields->ip = $matches[1];
    $fields->port = $matches[2];
    $fields->bytes = $matches[3];
    $fields->inbytes = $matches[4];
    $fields->outbytes = $matches[5];
    $fields->protocol = $matches[6];
    $fields->method = $matches[7];
    $fields->host = $matches[8];
    $fields->file = $matches[9];
    $fields->uri = $matches[10];
    $fields->status = $matches[11];
    $fields->phpsessid = $matches[12];
    $fields->useragent = $matches[13];
    $fields->referer = $matches[14];
    $fields->responsetime = $matches[15];
    $fields->datetime = $matches[16];
    try {
        $stmt->exec();
    } catch(Exception $e) {
        $fp2 = fopen('/etc/apache2/shjlogerr.txt', 'w');
        fwrite($fp2, $e->getMessage());
        fclose($fp2);
        $db = new SHJPDOExt('pgsql', '/etc/apache2/shjlog.ini');
        $stmt = $db->insert('shjlog', $fields);
        try {
            $stmt->exec();
        } catch(Exception $e) {}
    }
}

?>
[이 게시물은 관리자님에 의해 2011-10-31 17:30:25 Apache에서 이동 됨]

댓글 2개

newline 이 올경우 로그 누락이 있던거 같던데??

그 문제는 없는건가요~?
댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
08-06-15 조회 3,196
08-06-15 조회 2,426
08-06-14 조회 2,760
08-06-14 조회 3,665
08-06-14 조회 3,353
08-06-12 조회 2,232
08-06-11 조회 2,278
08-06-11 조회 2,366
08-06-11 조회 7,395
08-06-11 조회 7,127
08-06-08 조회 3,301
08-06-07 조회 2,234
08-06-05 조회 2,362
08-06-05 조회 3,334
08-06-05 조회 2,702
08-06-05 조회 2,358
08-06-05 조회 2,378
08-06-04 조회 2,296
08-06-03 조회 2,297
08-06-03 조회 5,371
08-06-03 조회 3,604
08-06-02 조회 2,901
08-06-02 조회 4,040
08-06-01 조회 2,655
08-05-31 조회 2,357
08-05-31 조회 2,369
08-05-31 조회 2,460
08-05-30 조회 2,325
08-05-29 조회 2,474
08-05-29 조회 3,745
08-05-27 조회 5,580
08-05-26 조회 2,421
08-05-26 조회 5,327
08-05-25 조회 5,417
08-05-24 조회 2,480
08-05-23 조회 3,492
08-05-23 조회 2,900
08-05-23 조회 4,659
08-05-22 조회 2,661
08-05-21 조회 5,004
08-05-21 조회 4,783
08-05-21 조회 3,928
08-05-20 조회 4,525
08-05-20 조회 4,115
08-05-20 조회 3,340
08-05-20 조회 4,608
08-05-20 조회 3,954
08-05-20 조회 4,842
08-05-19 조회 2,389
08-05-16 조회 3,516
08-05-15 조회 2,577
08-05-15 조회 2,406
08-05-15 조회 2,397
08-05-14 조회 2,338
08-05-13 조회 2,792
08-05-13 조회 4,195
08-05-13 조회 4,616
08-05-09 조회 4,384
08-05-08 조회 3,144
08-05-08 조회 5,292
08-05-08 조회 2,649
08-05-07 조회 2,342
08-05-07 조회 2,492
08-05-07 조회 2,305
08-05-01 조회 2,619
08-05-01 조회 2,969
08-04-29 조회 3,811
08-04-28 조회 3,634
08-04-26 조회 2,659
08-04-26 조회 2,409
08-04-26 조회 3,684
08-04-25 조회 4,101
08-04-23 조회 4,536
08-04-22 조회 4,496
08-04-22 조회 3,641
08-04-22 조회 5,202
08-04-22 조회 4,584
08-04-22 조회 6,964
08-04-22 조회 4,023
08-04-21 조회 3,173
08-04-21 조회 3,224
08-04-21 조회 3,154
08-04-21 조회 2,981
08-04-21 조회 4,083
08-04-20 조회 3,250
08-04-20 조회 2,933
08-04-20 조회 3,460
08-04-19 조회 2,366
08-04-18 조회 3,079
08-04-18 조회 4,414
08-04-17 조회 3,649
08-04-17 조회 3,092
08-04-17 조회 3,312
08-04-17 조회 2,601
08-04-17 조회 2,993
08-04-17 조회 2,478
08-04-17 조회 3,029
08-04-17 조회 3,093
08-04-17 조회 2,286
08-04-17 조회 4,574