IIS W3C Log 활용하기

IIS Log DB에 업로드 하여 활용하기

IIS Log 뷰어들에도 좋은 프로그램들이 많으나 자체 통계를 위해 디비 저장을 필요로 해서

정리하게 되었습니다.  IIS 로그 항목에 따라 테이블 구조 변경되어야 합니다.

USE master;


IF EXISTS (SELECT * 
   FROM   master..sysdatabases 
   WHERE  name = N'WebLogs')
DROP DATABASE WebLogs
GO
 
CREATE DATABASE WebLogs
GO

USE WebLogs; 
CREATE TABLE [JDSWEB01] (
[DATE] [DATE] NULL,
[TIME] [TIME] NULL,
[s-sitename] [VARCHAR] (32) NULL,
[s-computername] [VARCHAR] (255) NULL,
[s-ip] [VARCHAR] (16) NULL,
[cs-method] [VARCHAR] (8) NULL, 
[cs-uri-stem]  [VARCHAR] (255) NULL,
[cs-uri-query] [VARCHAR] (2048) NULL,
[s-port] [VARCHAR] (8) NULL, 
[cs-username] [VARCHAR] (16) NULL,
[c-ip] [VARCHAR] (16) NULL,
[cs-version] [VARCHAR] (1024) NULL,
[cs(User-Agent)] [VARCHAR] (1024) NULL,
[cs(Cookie)] [VARCHAR] (2048) NULL,
[cs(Referer)] [VARCHAR] (4096) NULL, 
[cs-host] [VARCHAR] (2048) NULL,
[sc-STATUS] [INT] NULL,
[sc-substatus] [INT] NULL,
[sc-win32-STATUS] [INT] NULL,
[sc-bytes] [BIGINT] NULL,
[cs-bytes] [BIGINT] NULL,
[time-taken] [INT] NULL
)

USE WebLogs;
BULK INSERT [JDSWEB01] FROM 'C:\Users\Administrator\Desktop\iisLog\20150907\u_ex150907.log'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

SELECT LEFT(TIME,2)+':00 ~ ' + LEFT(TIME,2)+':59' AS time,COUNT(*) AS [Request count]
,CAST(AVG([time-taken]) AS FLOAT)/1000 AS [avg Response Time]
,SUM(CAST([sc-bytes] AS FLOAT))/1024 FROM [JDSWEB01]
GROUP BY LEFT(TIME,2)
ORDER BY LEFT(TIME,2)

실제로 활용하게 되는 [sc-status] 및 [cs-bytes] , [sc-bytes] 송수신량 등 외에도 [sc-win32-status] 를 눈여겨 볼 필요가 있습니다.

실제 클라이언트에서 데이터를 받지 못하는 상황과도 밀접한 관련이 있기 때문입니다.

0: The operation completed successfully.
64: The specified network name is no longer available.
995: The I/O operation has been aborted because of either a thread exit or an application request.
1236: The network connection was aborted by the local system.
121: The semaphore timeout period has expired.
22: The device does not recognize the command.

참고 Url :

https://support.microsoft.com/ko-kr/kb/296085

http://www.jds.net.au/tech-tips/importing-iis-logs-into-sql-server/

https://technet.microsoft.com/ko-kr/library/Cc754702(v=WS.10).aspx