투

[서버운영] 아파치로그] 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건
+
제목 글쓴이 날짜 조회
07-10-19 조회 3,462
07-10-19 조회 3,222
07-10-19 조회 3,172
07-10-19 조회 2,876
07-10-19 조회 4,452
07-10-19 조회 5,818
07-10-19 조회 6,097
07-10-19 조회 3,482
07-10-19 조회 4,946
07-10-17 조회 4,596
07-10-17 조회 5,985
07-10-16 조회 4,609
07-10-15 조회 5,573
07-10-13 조회 5,245
07-10-09 조회 4,949
07-10-09 조회 4,283
07-10-09 조회 4,213
07-10-09 조회 4,771
07-10-09 조회 3,536
07-10-09 조회 3,318
07-10-09 조회 6,791
07-10-09 조회 3,942
07-10-09 조회 3,676
07-10-09 조회 3,995
07-10-09 조회 3,368
07-10-09 조회 4,407
07-10-08 조회 1.1만
07-10-05 조회 3,153
07-10-05 조회 4,079
07-10-03 조회 4,192
07-10-03 조회 3,396
07-10-03 조회 4,241
07-10-03 조회 5,262
07-10-03 조회 4,244
07-10-03 조회 3,644
07-10-03 조회 7,330
07-10-03 조회 4,910
07-10-03 조회 7,581
07-10-03 조회 3,679
07-10-03 조회 3,621
07-10-03 조회 3,627
07-10-03 조회 4,663
07-10-03 조회 4,615
07-10-03 조회 3,312
07-10-03 조회 5,174
07-10-03 조회 2,976
07-10-03 조회 2,646
07-10-03 조회 2,699
07-10-03 조회 2,906
07-10-03 조회 2,914
07-10-03 조회 4,730
07-10-02 조회 3,620
07-10-02 조회 3,150
07-10-02 조회 3,488
07-10-02 조회 5,792
07-10-02 조회 3,524
07-10-02 조회 4,150
07-10-02 조회 3,166
07-10-02 조회 4,832
07-10-02 조회 3,077
07-10-02 조회 8,909
07-10-02 조회 4,565
07-10-01 조회 2,530
07-10-01 조회 3,729
07-09-28 조회 4,756
07-09-28 조회 3,628
07-09-27 조회 3,525
07-09-27 조회 4,034
07-09-27 조회 5,077
07-09-27 조회 4,679
07-09-27 조회 3,816
07-09-27 조회 3,226
07-09-27 조회 3,279
07-09-27 조회 3,914
07-09-27 조회 3,249
07-09-15 조회 4,187
07-09-15 조회 2,905
07-09-13 조회 3,423
07-09-13 조회 2,946
07-09-13 조회 3,380
07-09-11 조회 3,615
07-09-09 조회 3,550
07-08-28 조회 3,494
07-08-28 조회 3,368
07-08-23 조회 4,671
07-08-22 조회 2,866
07-08-21 조회 4,652
07-08-21 조회 3,232
07-08-16 조회 6,505
07-08-07 조회 3,412
07-08-04 조회 3,539
07-08-04 조회 2,909
07-08-04 조회 3,320
07-08-04 조회 4,879
07-08-04 조회 2,722
07-08-04 조회 2,928
07-08-01 조회 5,565
07-08-01 조회 4,978
07-07-31 조회 4,439
07-07-31 조회 4,690