第一步:导入数据
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定时作业,每隔一小时执行上面的修正脚本即可。