如何用KQL从Azure日志分析的W3C IIS日志提取自定义页面浏览量等数据？

Hey Raj, let's tackle your questions step by step, using the sample data you shared:

核心问题回应 #

1. 是的，KQL可以提取页面浏览量和下载计数——但实现的精准度取决于你拥有的日志字段。
2. 基于现有数据，部分需求可实现，但10秒页面停留的判定无法精准完成，下面我会详细说明原因和可行的替代方案。

现有数据的局限性 #

你提供的日志缺少两个关键字段，这直接影响了停留时长的判定：

• 没有客户端唯一标识（比如客户端IP cIP、用户代理csUserAgent或会话ID）：无法准确区分不同用户的访问会话，也没法把同一用户的连续请求关联起来。
• 没有办法追踪用户在页面的停留时长：通常页面停留时长是通过当前页面请求时间和下一个页面请求时间的差值计算的，但现有数据里没有足够的上下文来做这个计算。

所以严格来说，我们没法判定用户是否在页面停留了超过10秒，只能做近似统计。

可实现的KQL查询示例 #

1. 提取非匿名用户列表 #

这个很直接，过滤掉匿名用户（csUserName为-）后去重即可：

datatable (TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName :string) [
datetime(2019-04-12T11:55:13Z),"/Account/","302","-","WebsiteName",
datetime(2019-04-12T11:55:16Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Account/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/site.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/modernizr-2.8.3.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/bootstrap.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:23Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:57:13Z),"/Home/About","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:58:16Z),"/Home/Contact","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]
| where csUserName != "-"
| distinct csUserName
| project 唯一用户=csUserName

2. 统计文件下载计数（近似） #

首先得定义什么是"下载文件"——通常我们会排除页面路由、静态脚本和样式文件，只统计比如.pdf、.zip这类文件。你提供的示例数据里没有这类文件，但这里给出通用查询：

datatable (TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName :string) [
datetime(2019-04-12T11:55:13Z),"/Account/","302","-","WebsiteName",
datetime(2019-04-12T11:55:16Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Account/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/site.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/modernizr-2.8.3.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/bootstrap.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:23Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:57:13Z),"/Home/About","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:58:16Z),"/Home/Contact","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]
// 排除静态资源和页面路由
| where csUriStem !has ".css" and csUriStem !has ".js"
| where csUriStem matches regex @"^/.+\.[a-zA-Z0-9]{2,4}$" // 匹配带后缀的文件路径
| where scStatus == "200" // 只统计成功的下载请求
| summarize 下载计数=count() by 文件路径=csUriStem, 站点名称=sSiteName

3. 页面浏览量统计（近似版本，无法严格判定10秒停留） #

因为没法精准计算停留时长，我们可以退而求其次，用同一用户对同一页面的首次和末次请求间隔来近似判断——虽然这和实际停留时间有差异，但可以作为参考：

datatable (TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName :string) [
datetime(2019-04-12T11:55:13Z),"/Account/","302","-","WebsiteName",
datetime(2019-04-12T11:55:16Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Account/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/site.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/modernizr-2.8.3.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Scripts/bootstrap.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName",
datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","WebsiteName",
datetime(2019-04-12T11:55:23Z),"/","302","-","WebsiteName",
datetime(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:57:13Z),"/Home/About","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:58:16Z),"/Home/Contact","200","myemail@mycom.com","WebsiteName",
datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]
| where csUriStem !has ".css" and csUriStem !has ".js" // 排除静态资源
| where scStatus == "200" // 只统计成功的页面请求
// 按用户和页面分组，计算首次/末次访问时间
| summarize 首次访问=min(TimeGenerated), 末次访问=max(TimeGenerated), 请求次数=count() 
    by 页面路径=csUriStem, 访问用户=csUserName
| extend 访问间隔秒数=datetime_diff('second', 末次访问, 首次访问)
// 近似判定：如果用户对同一页面的访问间隔超过10秒，算一次有效浏览
| where 访问间隔秒数 >=10 or 请求次数 ==1 // 单次请求也保留（无法判断停留时长）
| project 页面路径, 访问用户, 有效浏览次数=1, 访问间隔秒数

优化建议：实现精准停留时长判定 #

如果想要严格统计"停留超过10秒"的页面浏览量，需要在IIS日志中启用以下字段：

• cIP（客户端IP）和csUserAgent（用户代理）：用来唯一标识用户会话
• 确保日志记录所有页面请求的时间戳，这样可以用KQL的next()函数获取同一用户的下一个请求时间，从而计算准确的页面停留时长。

内容的提问来源于stack exchange，提问作者Raj