1. Time Aggregation

---

요런 형식의 time field를 가지고 데이터들을 분석해보자.
우선 parsing 작업을 위해 전에서 배운 substr과 concat이라는 문자열 연결 함수 c언어에서 다 해봤다구욧! 를 사용하게 된다.

전 처럼 view를 만드는 작업이 들어가는데 이 view가 이제서야 뭔지 이해가 됐다. view는 select해서 보여주는 데이터들의 덩어리 그 자체인것 같다.

CREATE VIEW IF NOT EXISTS by_month
AS SELECT host, identity, `user` ,`time`
,CASE substr(time,5,3) 
WHEN 'Jan'  THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun'  THEN '06'
WHEN 'Jul'   THEN '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep'  THEN '09'
WHEN 'Oct'  THEN '10'
WHEN 'Nov' THEN '11'
WHEN 'Dec' THEN '12'
ELSE '00'
END
AS month
,substr(time,9,4) AS year
,concat(substr(time,9,4)
,CASE substr(time,5,3) 
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN  '06'
WHEN 'Jul' THEN  '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
WHEN 'Dec' THEN '12'
ELSE '00'
END) AS yearmonth
,concat(CASE substr(time,5,3)
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN  '06'
WHEN 'Jul' THEN  '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
WHEN 'Dec' THEN '12'
ELSE '00'
END,
substr(time,2,2)) AS monthday
,request, status, size, referer, agent
FROM apachelog;

위 코드는 필자가 요령껏 수정한 거고 실제 강의노트의 코드는
교수님의 취미이신지 군데군데 함정이 있는 코드이므로
실행이 곱게 되지 않는다. 아래 3가지 사항을 보고 자신이 수정해보자

1. 첫 문단의 Sep 부분에 뜬금없이 따옴표 대신 `를 쓰셨다..
2. referrer -> referer
3. 예약어 확인

우선 각 substr들의 역할은 하나하나 자릿수를 새 보면 파악할 수 있다.

5, 3 은 5번째부터 3글자니까 Feb 부분,
9, 4 는 2016
2, 2 는 29 부분이 된다.
즉 붙여진 이름대로 yearmonth는 201602 이런식으로, monthday는 0229 형식으로 나타나게 될 것임을 알 수 있다.

SELECT time, month, year, monthday, yearmonth FROM by_month LIMIT 1;

예시로 하나 출력해보자.

?? 화가 솟구치지만 분노를 가라앉히고 10개 출력해보자

흠~....

뇌리에는 온갖 어지러운 생각들이 스쳐 간다.
1. 내가 강의노트에 있는 코드를 고쳐서 제 기능을 못하나
2. 애당초 미완성 코드니 알아서 재구성해 짜라는 건가
3. 주어진 log 데이터에 오류가 있는가
4. 드랍 못한게 한이다.

다행히도 1분 간의 현타 뒤에 이성적인 생각이 들어 쿼리문에 00이 아닌 경우를 추가해서 출력해봤다.

휴 어서 넘어가자

2. 일별, 월별 Failed Request 분석

---

dos 공격의 징후일 수 있으니 1에서 만든 by_month를 바탕으로 Failed Request, Failed Response (각각 400대, 오류 500대 오류)가 난 데이터들을 살펴보자.

INSERT OVERWRITE LOCAL DIRECTORY 
'/mnt/hgfs/BigDataAnalytics/Project1/TopHostFailedLogonsByDay'
SELECT monthday, host, COUNT(host) AS host_freq 
FROM by_month 
WHERE substr(status,1,1) IN('4', '5')
GROUP BY monthday, host 
ORDER BY host_freq DESC
LIMIT 10;

저번에 어쩔수 없이만든 디렉토리와 같은 위치에 아래 select한 내용들을 저장하면 된다. 참고로 이 쿼리 때는 아무것도 출력되지 않으니 select~10; 부분만 따로 쿼리문을 날려서 값을 조회해보자.

이 또한 첫 줄을 예시로 구분을 해보면
daymonth - 0724
host - 121.205.226.76
host_freq - 57
에 해당한다

7월 24일에 121~ 머시기 host가 57번 등장하셨단 소리다.

INSERT OVERWRITE LOCAL DIRECTORY 
'/mnt/hgfs/BigDataAnalytics/Project1/TopHostFailedLogonsByMonth'
SELECT yearmonth, host, COUNT(host) AS host_freq 
FROM by_month 
WHERE substr(status,1,1) IN('4', '5')
GROUP BY yearmonth, host 
ORDER BY host_freq DESC
LIMIT 10;

위 코드도 거의 같은 맥락인데 차이점은 yearmonth로 월 단위를 이용한다는 것이다.
참고로 여기서도 강의노트 베끼면 오류난다 ;가 두번 있음

3. 월별 time series 기반 Failed Request 분석

---

Create VIEW FailedRequestsTimeSeriesByMonth AS
SELECT yearmonth, COUNT(yearmonth) AS failedrequest_freq 
FROM by_month 
WHERE substr(status,1,1) IN('4', '5')
GROUP BY yearmonth
ORDER BY yearmonth ASC;

요렇게 하면 실패시도 빈도수가 view로 저장이 되고 FailedRequestsTimeSeriresByMonth 를 고대로 select하면 다음과 같은 결과가 나타난다.

8월 9월에 많은 실패 시도가 있었다.

Create VIEW SuccessfulRequestsTimeSeriesByMonth AS
SELECT yearmonth, COUNT(yearmonth) AS successfulrequest_freq 
FROM by_month 
WHERE substr(status,1,1) IN ('1', '2', '3')
GROUP BY yearmonth
ORDER BY yearmonth ASC;

성공한 request 들도 8월 9월에 많을까?

많다.

실패율을 확인해보자.

SELECT a.yearmonth, failedrequest_freq / successfulrequest_freq AS failratio
FROM FailedRequestsTimeSeriesByMonth a
JOIN SuccessfulRequestsTimeSeriesByMonth b
ON a.yearmonth = b.yearmonth
ORDER BY yearmonth ASC;

역시 8월 9월에 실패율이 높다.

이제 일별로 데이터를 정리해보자.

CREATE VIEW by_day AS
SELECT host, identity, `user`, `time`, concat(year, monthday) AS yearmonthday, 
           request, status, size, referer, agent
FROM by_month;

일별 데이터도 확인해야하는데 그냥 where status>0 해서 보자.

SELECT * FROM by_day where status>0 LIMIT 10;

조건 안 달면 가끔 공백만 select해서 가져올 때가 있다..

status를 확인해보면 200, 404, 302 등의 숫자들이 보인다.

이제 앞선 과정들을 모두 일별 view로 생성해보자.

Create VIEW FailedRequestsTimeSeriesByDay AS
SELECT yearmonthday, COUNT(yearmonthday) AS failedrequest_freq 
FROM by_day 
WHERE substr(status,1,1) IN ('4', '5')
GROUP BY yearmonthday   
ORDER BY yearmonthday ASC;

Create VIEW SuccessfulRequestsTimeSeriesByDay AS
SELECT yearmonthday, COUNT(yearmonthday) AS successfulrequest_freq 
FROM by_day 
WHERE substr(status,1,1) IN ('1', '2', '3')
GROUP BY yearmonthday  
ORDER BY yearmonthday ASC;

//실패율
SELECT a.yearmonthday, a.failedrequest_freq / b.successfulrequest_freq AS failratio
FROM FailedRequestsTimeSeriesByDay a
JOIN SuccessfulRequestsTimeSeriesByDay b
ON a.yearmonthday = b.yearmonthday
ORDER BY yearmonthday ASC;

호로로록