천

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

· 2015-11-23 (월) 20:22:05 · 3108

아래글에 이어 아파치와 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) {}
    }
}

?>

댓글을 작성하시려면 로그인이 필요합니다.

프로그램

8,187건
+
제목 글쓴이 날짜 조회
15-12-13 조회 2,977
15-12-11 조회 3,452
15-12-11 조회 3,161
15-12-11 조회 2,994
15-12-11 조회 2,654
15-12-11 조회 2,602
15-12-11 조회 2,606
15-12-10 조회 2,562
15-12-10 조회 2,628
15-12-10 조회 2,610
15-12-10 조회 2,851
15-12-09 조회 2,770
15-12-08 조회 3,748
15-12-08 조회 2,978
15-12-08 조회 2,714
15-12-08 조회 3,012
15-12-07 조회 3,082
15-12-07 조회 2,799
15-12-07 조회 3,219
15-12-07 조회 2,799
15-12-06 조회 2,944
15-12-06 조회 2,658
15-12-06 조회 2,985
15-12-05 조회 2,810
15-12-05 조회 3,115
15-12-05 조회 2,849
15-12-04 조회 2,701
15-12-04 조회 2,813
15-12-04 조회 2,868
15-12-03 조회 2,822
15-12-03 조회 2,848
15-12-03 조회 2,892
15-12-03 조회 2,976
15-12-03 조회 3,242
15-12-03 조회 2,754
15-12-02 조회 3,000
15-12-02 조회 2,948
15-12-02 조회 2,921
15-12-02 조회 3,108
15-12-02 조회 3,254
15-12-01 조회 2,792
15-12-01 조회 2,601
15-12-01 조회 2,673
15-11-30 조회 2,858
15-11-30 조회 3,050
15-11-30 조회 2,936
15-11-29 조회 2,674
15-11-29 조회 2,930
15-11-29 조회 2,742
15-11-28 조회 2,674
15-11-28 조회 2,707
15-11-28 조회 2,737
15-11-27 조회 2,827
15-11-27 조회 2,687
15-11-27 조회 2,701
15-11-26 조회 2,882
15-11-26 조회 3,750
15-11-25 조회 2,855
15-11-25 조회 2,805
15-11-25 조회 2,851
15-11-25 조회 2,649
15-11-25 조회 3,036
15-11-23 조회 2,946
15-11-23 조회 3,109
15-11-23 조회 2,904
15-11-21 조회 2,805
15-11-21 조회 2,662
15-11-21 조회 2,609
15-11-20 조회 2,603
15-11-20 조회 2,543
15-11-20 조회 2,683
15-11-20 조회 2,947
15-11-18 조회 2,829
15-11-18 조회 2,714
15-11-18 조회 2,702
15-11-17 조회 2,907
15-11-17 조회 2,659
15-11-17 조회 2,796
15-11-16 조회 2,679
15-11-16 조회 2,643
15-11-16 조회 2,832
15-11-15 조회 2,839
15-11-15 조회 2,705
15-11-15 조회 2,896
15-11-13 조회 3,642
15-11-12 조회 3,546
15-11-12 조회 3,008
15-11-12 조회 3,677
15-11-11 조회 2,805
15-11-11 조회 3,065
15-11-11 조회 3,312
15-11-09 조회 2,825
15-11-09 조회 3,045
15-11-09 조회 3,069
15-11-09 조회 2,796
15-11-09 조회 2,839
15-11-09 조회 3,314
15-11-06 조회 3,432
15-11-05 조회 2,931
15-11-05 조회 2,756