使用Log Parser将IIS日志导入SQL分析

C# 4543次浏览

第一步:导入数据

Log Parser支持将解析结果以多种格式导出(以下为帮助文档截图):

在此,我建议选择输出格式为 SQL 。
注意:这里的SQL并不是指SQLSERVER,而是指所有提供ODBC访问接口的数据库。

更多信息可以参考:IIS日志-网站运维的好帮手

结合作者的思路,写了个批处理,配合windows定时任务,可以将每日的IIS写入数据库做分析了。
------------------------------------------------------
: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="***"

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脚本
 * 创建时间: 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="****"
SET DB_Table="****"
SET DB_USERID="****"
SET DB_PASSWORD="****"

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="******\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 = DATEADD(hour, 8,   
           CAST( CONVERT(NVARCHAR(MAX), date, 23) + ' ' +CONVERT(NVARCHAR(MAX), time, 8)  AS DATETIME)
       ), 
       isProcess = 1
WHERE  isProcess = 0


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


发表评论

电子邮件地址不会被公开。 必填项已用*标注