IIS日誌匯入SQL Server分析

---訪問IP數統計
SELECT   client_ip,
         Count(* ) AS total
FROM     ex090905 --表名
GROUP BY client_ip
ORDER BY client_ip DESC
         ---訪問PV統計

SELECT Count(* ) AS total
FROM   ex090905
WHERE  cs_uri_stem LIKE '%asp%'
        OR cs_uri_stem LIKE '%html'

---頁面訪問統計排行

SELECT   a.cs_uri_stem,
         a.query,
         a.total
FROM     (SELECT   cs_uri_stem,
                   query,
                   Count(* ) AS total
          FROM     full0831     --表名
          WHERE    log_time > '16:00:00'
                   AND log_time < '17:00:00'
          GROUP BY cs_uri_stem) a
WHERE    a.cs_uri_stem LIKE '%asp%'
          OR a.cs_uri_stem LIKE '%html'
ORDER BY a.total DESC

---IIS日誌匯入

DECLARE
  @file NVARCHAR(100)

DECLARE
  @table NVARCHAR(10)

SET @file = 'D:\ex090831.log' --日誌檔

SET @table = 'ex090831' --表名

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(@table)
                  AND TYPE IN ('U'))
  EXEC( 'drop table ' + @table)

EXEC( '
	create table [dbo].[' + @table + '](
		[log_date] [nvarchar](10) NULL,
		[log_time] [nvarchar](10) NULL,
		[s-sitename] [nvarchar](20) NULL,
		[cs_method] [nvarchar](10) NULL,
		[cs_uri_stem] [nvarchar](2000) NULL,
		[query] [nvarchar](4000) NULL,
		[client_ip] [nvarchar](20) NULL,
		[User_Agent] [nvarchar](3000) NULL,
		[Referer] [nvarchar](3000) NULL,
		[Status] [nvarchar](10) NULL,
		[sc-substatus] [nvarchar](10) NULL,
		[sc-win32-status] [nvarchar](10) NULL,
		[sc-bytes] [nvarchar](10) NULL,
		[cs-bytes] [nvarchar](10) NULL,
		[time-taken] [nvarchar](10) NULL
	) on [primary];

	bulk insert ' + @table + ' from ''' + @file + '''
	with
	(
		datafiletype = ''char'',
		fieldterminator = '' '',
		rowterminator = ''\n'',
		tablock
	);
	')