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
- [2015/08/24] IIS에서 ASP 날짜 형식 YYYY-MM-DD (1984)
- [2015/07/30] MSSQL 쿼리로 CSV 파일 데이터 업로드 하는 방법 ( BULK INSERT ) (2321)
- [2015/06/24] Just in Time Debugging 비활성화 (1247)
- [2015/01/29] 2015 SQL UNPLUGGED 세미나 (529)
- [2014/10/13] Access ( *.mdb ) 비밀번호 풀기 (6643)