게시물 1,376건
   
아파치 로그파일을 쿼리로 검색
글쓴이 : 최고관리자 날짜 : 2014-10-31 (금) 09:29 조회 : 5572
글주소 :
                                
참고 : 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

이름 패스워드
비밀글 (체크하면 글쓴이만 내용을 확인할 수 있습니다.)
왼쪽의 글자를 입력하세요.
   

 



 
사이트명 : 모지리네 | 대표 : 이경현 | 개인커뮤니티 : 랭키닷컴 운영체제(OS) | 경기도 성남시 분당구 | 전자우편 : mojily골뱅이chonnom.com Copyright ⓒ www.chonnom.com www.kyunghyun.net www.mojily.net. All rights reserved.