IIS日志分析
IIS日志分析工具:LogParse、sqlserver 2008
1.从将IIS日志转换到数据库表中
logparser "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 RequestTime, * FROM 'D:\phone.log' to phonelog" -i:IISW3C -o:SQL -oConnString:"Driver={SQL Server};server=.;database=iis;uid=sa;pwd=pwd;Integrated Security=SSPI" -createtable:ON
2.使用sql分析语句分析结果
--去除url参数并保存到指定表中 alter table phonelog add url nvarchar(200) go update phonelog set url=case when Charindex('/',csUriStem,CHARINDEX('/',csUriStem,2)+1)>0 then SUBSTRING(csUriStem,0,Charindex('/',csUriStem,CHARINDEX('/',csUriStem,2)+1)) else csUriStem end --分析时间占用 select scStatus, count(*) AS count, sum(timetaken * 1.0) /1000.0 AS sum_timetaken_second from phonelog with(nolock) group by scStatus order by 3 desc --统计网络级别错误 declare @recCount bigint; select @recCount = count(*) from phonelog with(nolock) select case when scWin32Status=64 then '客户端连接已关闭' when scWin32Status=121 then '传输超时' when scWin32Status=1236 then '本地网络中断' else Convert(varchar,scWin32Status) end as scWin32Status , count(*) AS count, (count(*) * 100.0 / @recCount) AS [percent] from phonelog with(nolock) where scWin32Status > 0 group by scWin32Status order by 2 desc --查询每个用户的最大最小请求次数 select cIp,COUNT(*) as count from phonelog group by cIp order by 2 desc --查询日志最大最小时间 select MAX(l.date) as maxdate,MIN(l.date) as mindate from phonelog l --查询耗时最大的请求 select top 1000 l.csUriStem,(timetaken * 1.0) /1000.0 as timetaken_second from phonelog l order by l.timeTaken desc --查询标准请求中请求最多的url select top 1000 l.csUriStem,sum(timetaken * 1.0) /1000.0 as sum_timetaken_second,COUNT(*) as count from phonelog l group by l.csUriStem order by 3 desc --查询发杂请求中请求最多的接口 select ll.url,ll.count,ll.sum_timetaken_second,ll.sum_timetaken_second/ll.count as avg_timetaken_second from ( select l.url,sum(timetaken * 1.0) /1000.0 as sum_timetaken_second,COUNT(*) as count from phonelog l group by l.url)ll order by 2 desc --查询平均耗时最大的请求接口 select ll.url,ll.count,ll.sum_timetaken_second,ll.sum_timetaken_second/ll.count as avg_timetaken_second from ( select l.url,sum(timetaken * 1.0) /1000.0 as sum_timetaken_second,COUNT(*) as count from phonelog l group by l.url)ll order by 4 desc select * from phonelog l where l.csuristem like '%GetClassDynamicPager%'
3.其它数据库操作和分析可以随自己的意愿去写sql语句。
4.根据分析结果和业务,查找性能瓶颈,有针对的对当前的程序进行优化修改。