아파치 로그를 SQL 쿼리로 변환해서 검색을 용이하게 하는 방법입니다.
참고 : http://www.steve.org.uk/Software/asql/
아파치 로그를 쿼리로 변환
INSTALL
# rpm -Uvh http://mirrors.kernel.org/fedora-epel/6/x86_64/epel-release-6-8.noarch.rpm
# yum install perl-DBD-SQLite perl-Term-ReadLine-Gnu
# wget http://www.steve.org.uk/Software/asql/asql-1.7.tar.gz
# tar zxvf asql-1.7.tar.gz
# cd asql-1.7
# make install
# asql
Name “Regexp::IPv6::IPv6_re” used only once: possible typo at /usr/bin/asql line 1534.
asql v1.7 – type ‘help’ for help.
asql> help
asql v1.7
The following commands are available within this shell:
alias – Define, or view, persistent aliases.
alter – Run an ALTER query against the database.
create – Run a CREATE query against the database.
delete – Run a DELETE query against the database.
drop – Run a DROP query against the database.
exit – Exit the shell.
help – Show general, or command-specific, help information.
insert – Run an INSERT query against the database.
load – Load an Apache logfile.
quit – Exit this shell.
restore – Load a previously save’d temporary database.
save – Save the temporary database.
select – Run a SELECT query against the database.
show – Show the structure of the database.
update – Run an UPDATE query against the database.
For command-specific help run “help command”.
아파치로그를 테이블로 만들기 위해 로딩
asql> load /etc/httpd/logs/chonnom.com-access_log
Loading: /etc/httpd/logs/chonnom.com-access_log
아래와 같이 테이블정보를 확인해보면 “logs” 테이블로 column이 만들어져 있다.
Loading: /etc/httpd/logs/access_log
asql> select count(*) from logs;
57245
example 1)
날짜별 검색
asql> SELECT source, request, date, status FROM logs WHERE date >= ‘2014-10-31T15:10:00’ ORDER BY source
192.55.113.6 /xxx/xxx.html 2014-10-31T15:12:18 304
192.55.113.6 /xxx/xxx.png 2014-10-31T15:12:18 200
192.55.113.6 /xxx/xxx.png 2014-10-31T15:12:18 200
192.55.113.6 /xxx/xxx.png 2014-10-31T15:12:18 200
example 2)
특정아이피와 시간을 조합해서 트래픽 총량 합산
asql> SELECT source,SUM(size) AS Number FROM logs where source=’192.55.113.6′ and date >= ‘2014-10-31T15:10:00’ GROUP BY source ORDER BY Number DESC
192.55.113.6 117787
example 3)
파일저장
asql> save smileserv-cloudserviceteamDB-20141031
Saving to : smileserv-cloudserviceteamDB-20141031
파일복구
asql> select count(*) from logs
No files loaded yet!
asql> restore smileserv-cloudserviceteamDB-20141031
asql> select count(*) from logs
57323
example 4)
404 호출메세지 검색
asql> SELECT source,date,status,request FROM logs WHERE status=’404′ ORDER BY date
192.55.113.6 2014-10-27T09:25:28 404 /xxxx/favicon.ico
192.66.113.6 2014-10-27T09:25:34 404 /xxxx/favicon.ico
192.77.113.6 2014-10-28T09:08:00 404 /xxxx/favicon.ico
192.88.113.6 2014-10-28T14:14:01 404 /favicon.ico
[polldaddy rating=”7739789″]