호스팅 TIP > 리눅스 > 아파치 로그파일을 쿼리로 검색

호스팅 TIP

보안

시스템

프로그래밍

리눅스

월간 인기 게시물

	게시물 1,378건

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

글쓴이 : 최고관리자 날짜 : 2014-10-31 (금) 09:29 조회 : 5585

글주소 :

참고 : 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

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