메뉴 닫기

아파치 로그파일을 쿼리로 검색

아파치 로그를 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이 만들어져 있다.
apache_sql

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″]

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다