使用Log Parser将IIS日志导入SQL分析 [ASP.NET/MVC]

一、实现思路: 

1.将IIS日志设置为按小时保存(可选); 

2.通过微软官方的Log Parser工具,将IIS日志文件导入到SQL数据库; 

3.编写统计页面,实现在线分析查看。

第一步:导入数据

1.下载Log Parser:https://www.microsoft.com/en-us/download/details.aspx?id=24659

2.通过命令行将IIS日志导入SQL数据库:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT  *  FROM  'D:\Temp\u_ex160820.log'  to QingShanBoke" -i:IISW3C -o:SQL -oConnString:"Driver={SQL Server};server=.;database=IISLOG;UId=sa;Pwd=***" -createtable:ON

3.将命令行写成批处理文件:

-------------------------------------------------------------------------------------------------

:LOGPASER:LOGPASER存放路径
:LOGFILE:日志存放路径
:DB_SERVER:数据库地址
:DB_DATABASE:数据库名称
:DB_Table:数据库表名
:DB_USERID:数据库用户名
:DB_PASSWORD:数据库登录密码

@echo OFF
SET LOGPASER="C:\Program Files (x86)\Log Parser 2.2
SET LOGFILE="C:\WINDOWS\system32\LogFiles\W3SVC1236160155\ex%date:~2,2%%date:~5,2%%date:~8,2%.log"
SET DB_SERVER="."
SET DB_DATABASE="IISLOG"
SET DB_Table="Log"
SET DB_USERID="sa"
SET DB_PASSWORD="***"
c:
cd %LOGPASER%
logparser.exe "SELECT TO_LOCALTIME(TO_TIMESTAMP(ADD(TO_STRING(date, 'yyyy-MM-dd '), TO_STRING(time, 'hh:mm:ss')), 'yyyy-MM-dd hh:mm:ss')) AS CreateDate, * FROM %LOGFILE% to %DB_Table%" -i:IISW3C -o:SQL -oConnString:"Driver={SQL Server};server=%DB_SERVER%;database=%DB_DATABASE%;UId=%DB_USERID%;pwd=%DB_PASSWORD%;" -createtable:ON

-------------------------------------------------------------------------------------------------

将以上横线间的内容存为一批处理文件,把上面红色部分替换您实际的配置;运行批处理文件即可将IIS日志导入到SQL数据库中了。

再结合windows的定时任务,在每天的23:59分执行上面的批处理文件,即可实现将每日的IIS日志导入到SQL数据库了。

更详细的说明,可以参考IIS日志-网站运维的好帮手 


第二步:分析数据

通过第一步写入的数据,我们就可以对IIS日志做一些分析了,当然,这可以通过写SQL语句来实现,以下是常见问题分析脚本,供您参考:

/************************************************************
 * 脚本名称:常用IIS日志分析SQL脚本
 * 创建时间: 2016/8/20 9:54:58
 ************************************************************/ 
 
--1.查询页面路径访问次数
SELECT v.csUriStem AS '请求路径',COUNT(*) AS '请求次数'
FROM   QingShanBoke v
GROUP BY v.csUriStem
ORDER BY COUNT(*) DESC 

--2.查询页面请求耗时
SELECT v.csUriStem AS '请求路径',
       COUNT(*) AS '请求次数',
       MIN(timeTaken) AS '响应时间(快)',
       MAX(timeTaken) AS '响应时间(慢)',
       AVG(timeTaken) AS '响应时间(平均)'
FROM   QingShanBoke v
GROUP BY v.csUriStem
ORDER BY AVG(timeTaken) DESC 
       
--3.查询400,500错误页面
SELECT v.csUriStem, v.scStatus
FROM   QingShanBoke v
WHERE  v.scStatus LIKE '4%' OR  v.scStatus LIKE '5%' 
       
--4.查找访问频繁的IP
SELECT v.cIp AS '请求IP', COUNT(*) AS '请求次数'
FROM   QingShanBoke v
GROUP BY v.cIp
ORDER BY COUNT(*) DESC 

--5.查询每分钟访问量
SELECT SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 17) AS '时间', COUNT(*) AS '访问量'
FROM   QingShanBoke
GROUP BY SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 17)
ORDER BY SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 17) ASC 
       
--6.查询每小时访问量
SELECT SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 14) AS '时间', COUNT(*) AS '访问量'
FROM   QingShanBoke
GROUP BY SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 14)
ORDER BY SUBSTRING(CONVERT(NVARCHAR(20), createdate, 20), 0, 14) ASC 


2016-12-12 补充

上面的操作只能用于分析昨日的日志,如果要相对实时性的数据,您可以做以下操作:

1.将IIS日志配置为按每小时一个文件,并勾选上按本地时间命名

2.修改批处理文件:(注意红色部分)

:LOGPASER:LOGPASER存放路径
:LOGFILE:日志存放路径
:DB_SERVER:数据库地址
:DB_DATABASE:数据库名称
:DB_Table:数据库表名
:DB_USERID:数据库用户名
:DB_PASSWORD:数据库登录密码

:@echo OFF
SET LOGPASER="C:\Program Files (x86)\Log Parser 2.2" 
SET DB_SERVER="."
SET DB_DATABASE="IISLOG"
SET DB_Table="Member_Log"
SET DB_USERID="IISLOG"
SET DB_PASSWORD="IISLOG"

SET YY=%date:~2,2%
IF /i %YY% LSS 10 (set YY=0%date:~2,2%)

SET MM=%date:~5,2%
IF /i %MM% LSS 10 (set MM=0%date:~5,2%)

SET DD=%date:~8,2%
IF /i %DD% LSS 10 (set DD=0%date:~8,2%)

SET HH=%time:~0,2%
IF /i %HH% LSS 10 (set HH=0%time:~1,1%)

SET LOGFILE="E:\rc114log\member.rc114.com\W3SVC2\u_ex%YY%%MM%%DD%%HH%.log"

c:
cd %LOGPASER%
logparser.exe "SELECT TO_LOCALTIME(TO_TIMESTAMP(ADD(TO_STRING(date, 'yyyy-MM-dd '), TO_STRING(time, 'hh:mm:ss')), 'yyyy-MM-dd hh:mm:ss')) AS CreateDate,0 as IsProcess,date as DateAndTime, * FROM  %LOGFILE% to %DB_Table%" -i:IISW3C -o:SQL -oConnString:"Driver={SQL Server};server=%DB_SERVER%;database=%DB_DATABASE%;UId=%DB_USERID%;pwd=%DB_PASSWORD%;" -createtable:ON

pause


这里导入时,增加了两列:IsProcess和DateAndTime,由于IIS日志记录的特点:

(1)日期和时间是分开的,分别是date和time两个字段,所以这里增加了DateAndTime字段,用于合并日期和时间。

(2)IIS日志时间比我们东八区时间相差8个小时,在合并日期和时间的同时,需要将时间增加8个小时。

以上两步可以通过SQL定时任务来实现。


3.修正IIS日志时间字段的脚本:

UPDATE [表名]
SET    DateAndTime = CAST(SET    DateAndTime = DATEADD(hour, 8,   
           CAST( CONVERT(NVARCHAR(MAX), date, 23) + ' ' +CONVERT(NVARCHAR(MAX), time, 8)  AS DATETIME)
       ), 
       isProcess = 1
WHERE  isProcess = 0


4.添加SQL定时作业,每隔一小时执行上面的修正脚本即可。