时间戳

首页 > 解决方案 > 是否有任何 KQL 查询来提取页面视图，从 Azure-Log 分析上的 W3C IISlogs 下载计数？

azure-log-analytics - 是否有任何 KQL 查询来提取页面视图，从 Azure-Log 分析上的 W3C IISlogs 下载计数？

问题描述

我们正在尝试从 w3c IIS 日志中提取页面浏览量、文件下载计数、用户列表。我们要定义什么是页面视图，即任何用户在同一页面上停留超过 10 秒才能成为一页视图。少一点就不是页面浏览量。w3c 日志似乎没有足够的数据来提取它。这可以用已经可用的东西来实现吗？

这是可用于从中提取上述信息的数据，

数据表运算符

数据表（TimeGenerated:datetime, csUriStem:string, scStatus:string, csUserName:string, sSiteName:string) [datetime(2019-04-12T11:55:13Z),"/Account/","302","-", "网站名称", 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","-","网站名称", datetime(2019-04-12T11:55:17Z),"/Content/bootstrap.css","200","-","WebsiteName", datetime(2019-04-12T11:55:18Z),"/Scripts/jquery-3.3.1.js","200","-","网站名称”，日期时间（2019-04-12T11：55：23Z），“/”，“302”，“-”，“网站名称”，日期时间（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","网站名称"]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"]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"]"网站名称", 日期时间(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","网站名称", 日期时间(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"]"网站名称", 日期时间(2019-04-12T11:56:39Z),"/","200","myemail@mycom.com","网站名称", 日期时间(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"]myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]myemail@mycom.com","WebsiteName", datetime(2019-04-12T11:59:03Z),"/","200","myemail@mycom.com","WebsiteName"]

标签： azure-log-analyticsiis-logskql

解决方案

我不确定我是否满足您的所有要求，但这里有一些东西可以开始并为您提供初步方向。

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 scStatus !in ('302') // exclude status 302
| where csUriStem !startswith '/Scripts' and csUriStem !endswith ".css"  // exclude pages coming from '/Script' and .css files
| order by TimeGenerated asc
| summarize t=make_list(TimeGenerated) by csUriStem, csUserName // create time-series of visit events
| mv-apply t to typeof(datetime) on  // run subquery on each of the series
(
    project isVisit = (t - prev(t)) > 1min // compare with previous timestamp, and see if >1min passed
    | summarize Visits=sum(isVisit)
)
| project csUriStem, csUserName, Visits

以下是make_list ()（聚合函数）、prev()（窗口函数）、summary运算符和mv-apply运算符的链接

推荐阅读