투

[서버운영] 아파치로그] 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,188건
+
제목 글쓴이 날짜 조회
07-07-31 조회 4,662
07-07-30 조회 5,104
07-07-30 조회 5,565
07-07-30 조회 5,483
07-07-29 조회 3,900
07-07-29 조회 3,631
07-07-29 조회 4,573
07-07-29 조회 4,425
07-07-28 조회 4,632
07-07-26 조회 4,534
07-07-25 조회 6,206
07-07-24 조회 6,470
07-07-21 조회 4,575
07-07-21 조회 3,925
07-07-21 조회 4,136
07-07-21 조회 3,446
07-07-21 조회 3,288
07-07-21 조회 3,235
07-07-20 조회 4,337
07-07-20 조회 3,482
07-07-20 조회 3,555
07-07-20 조회 3,244
07-07-20 조회 3,693
07-07-20 조회 4,424
07-07-18 조회 5,401
07-07-18 조회 4,602
07-07-16 조회 3,200
07-07-16 조회 3,127
07-07-13 조회 4,106
07-07-12 조회 2,822
07-07-09 조회 3,216
07-07-07 조회 3,452
07-07-07 조회 3,356
07-07-07 조회 3,485
07-07-06 조회 5,298
07-07-06 조회 3,017
07-07-06 조회 5,715
07-07-06 조회 4,268
07-07-06 조회 3,447
07-07-06 조회 4,485
07-07-06 조회 4,102
07-07-05 조회 4,100
07-07-05 조회 6,863
07-07-04 조회 3,474
07-07-02 조회 4,627
07-06-30 조회 4,509
07-06-29 조회 4,893
07-06-29 조회 4,435
07-06-28 조회 3,624
07-06-23 조회 3,274
07-06-21 조회 3,739
07-06-20 조회 7,418
07-06-20 조회 4,027
07-06-20 조회 5,763
07-06-19 조회 3,219
07-06-19 조회 1.1만
07-06-17 조회 3,675
07-06-16 조회 2,783
07-06-15 조회 4,178
07-06-12 조회 4,142
07-06-11 조회 4,026
07-06-11 조회 3,310
07-06-05 조회 3,645
07-06-05 조회 4,200
07-06-05 조회 4,214
07-06-05 조회 5,321
07-06-04 조회 7,377
07-05-31 조회 3,275
07-05-30 조회 3,364
07-05-29 조회 3,067
07-05-29 조회 2,788
07-05-29 조회 3,802
07-05-29 조회 2,887
07-05-29 조회 2,697
07-05-29 조회 5,935
07-05-29 조회 2,957
07-05-29 조회 2,987
07-05-29 조회 3,000
07-05-29 조회 3,111
07-05-29 조회 4,798
07-05-29 조회 2,796
07-05-29 조회 2,662
07-05-29 조회 2,479
07-05-29 조회 3,015
07-05-29 조회 3,056
07-05-28 조회 4,973
07-05-26 조회 3,271
07-05-20 조회 4,297
07-05-18 조회 6,045
07-05-18 조회 4,106
07-05-17 조회 9,227
07-05-17 조회 5,268
07-05-17 조회 3,931
07-05-16 조회 2,676
07-05-16 조회 3,820
07-05-15 조회 6,244
07-05-14 조회 6,422
07-05-13 조회 3,262
07-05-11 조회 5,572
07-05-10 조회 5,317