python을 이용한 IIS web log 분석 (5) - DB화

 

(1) UTC시간 조정

(2) 국가식별

(3) 확장자 구분

 

(1) ~ (3) 까지 RAW로그를 이용해 추가로 분석할 데이터를 추출하는 방법을 알아보았다. 

 

이제 Query를 이용해 분석할 수 있도록 몇개의 필드만 DB화 해보겠다.

 

변수 및 배열	필드	설명
logtime (logs[0] + logs[1])	logtime	시간 데이터
logs[12]	useragent	응답코드 (200 / 404 / 500 등 분석)
logs[16]	scstatus	용량 (용랑 큰 컨텐츠 검색)
logs[19]	scbytes	인입 에이전트 검색
logs[21]	timetaken	응답시간 (웹 사이트 응답시간)
extention	extention	확장자 (통계 및 불필요한 접근 검색)
country	country	국가식별 (국내IP와 해외IP식별)

 

 

* csuristem (컨텐츠)와 cip 등은 좋은 데이터지만 노출할 필요가 없어 제외

 

 

 

 

fromdatetimeimportdatetime
from datetime import timedelta

import sqlite3

import geoip2.database

 

reader = geoip2.database.Reader('C:\DB\GeoLite2-city.mmdb')  # path

 

conn = sqlite3.connect(r'C:\log\weblog_sqlite3.db')
c= conn.cursor()

c.execute('''CREATE TABLE weblog
(logtime datetime, useragent VARCHAR(500), scstatus VARCHAR(20), scbytes INT(50), timetaken INT(50),
country VARCHAR(20), extension VARCHAR(20))''')

rows = c.execute('create index idx on weblog(logtime)')

with open(r'C:\log\sample.log', encoding='utf-8', errors='replace') as f:
      lines = f.readlines()
      for cnt, line in enumerate(lines,0):
           logs = line.split(' ')
           logtime = logs[0] + ' ' + logs[1]

           try:
                logtime = datetime.strptime(logtime, '%Y-%m-%d %H:%M:%S') - timedelta(hours=-9)

                response = reader.city(logs[10])
                country = response.country.iso_code
                extension = logs[6].split('.')
                c.execute('''INSERT INTO weblog VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')'''
                                                                                  %(logtime, logs[12], logs[16], logs[19], logs[21], country,                                                                                                      extension[-1]))

 

           except ValueError:
                  pass

 

           except IndexError:
                  pass

           except geoip2.errors.AddressNotFoundError:
               country = None

 

conn.commit()
c.close()
conn.close()

 

 

약 170만 건의 ROW가 정상 저장되었다.