gpt4 book ai didi

azure - 使用 userAgent 列对 Azure CDN 访问日志进行 KQL 查询,按设备类型进行排序和计数

转载 作者:行者123 更新时间:2023-12-03 03:34:16 27 4
gpt4 key购买 nike

我是 KQL 新手,所以对我来说很简单。我的目标是搜索“Microsoft.Cdn/Profiles/AccessLog/Write”并首先过滤到不同的 IP 地址。我已经弄清楚 userAgent_s 列中的哪些值可以告诉我哪些设备是什么。我可以搜索“macintosh”、“ipad”和“iphone”来获取不同的设备类型。

我想创建一个饼图来显示这三个设备的计数百分比,但仅使用不同的 IP 地址(每个 IP 地址仅一个)。以下是要在 userAgent_s 列中搜索的三个字符串,它们将显示哪个设备是什么:“macintosh”、“ipad”和“iphone”。

以下是一些数据的样子。

TimeGenerated [Local Time]  OperationName   userAgent_s clientIp_s  Type
9/26/2022, 10:48:33.238 AM Microsoft.Cdn/Profiles/AccessLog/Write yourApplicationName/4.1.4 (Linux;Android 10) ExoPlayerLib/2.9.2 2405:201:f00c:2015:4fe:9d1f:f77a:c2ab AzureDiagnostics
9/26/2022, 10:48:07.481 AM Microsoft.Cdn/Profiles/AccessLog/Write AppleCoreMedia/1.0.0.14G60 (iPhone; U; CPU OS 10_3_3 like Mac OS X; en_us) 2600:8801:42c:5400:f01f:d3dd:b55f:88de AzureDiagnostics
9/26/2022, 10:48:56.714 AM Microsoft.Cdn/Profiles/AccessLog/Write iTunes/12.12 (Windows; Microsoft Windows 10 x64; x64) AppleWebKit/7613.2007 68.98.143.209 AzureDiagnostics
9/26/2022, 10:47:27.620 AM Microsoft.Cdn/Profiles/AccessLog/Write Mozilla/5.0 (Linux; Android 11; motorola one 5G ace) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Mobile Safari/537.36 2600:387:15:1637::4 AzureDiagnostics
9/26/2022, 10:47:27.793 AM Microsoft.Cdn/Profiles/AccessLog/Write Mozilla/5.0 (Linux; Android 11; motorola one 5G ace) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/102.0.0.0 Mobile Safari/537.36 2600:387:15:1637::4 AzureDiagnostics

这是我所能得到的最接近的结果:

AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| extend MacOS = userAgent_s has "macintosh"
| extend iPhone = userAgent_s has "iphone"
| extend iPad = userAgent_s has "iPad"
| where MacOS == true or iPad == true or iPhone == true
| summarize Total=dcount(clientIp_s) by MacOS, iPhone, iPad
//| summarize MacOSTotal=countif(MacOS == true),iPadTotal=countif(iPad == true),iPhoneTotal=countif(iPhone == true)
| render table

我也尝试过这样的事情:

let MacOSX =
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where
userAgent_s has "macintosh"
| summarize MacOSX=dcount(clientIp_s) by bin(TimeGenerated,1h);
let iPhone =
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where
userAgent_s has "iphone"
| summarize iPhone=dcount(clientIp_s) by bin(TimeGenerated,1h);
let iPad =
AzureDiagnostics
| where OperationName == "Microsoft.Cdn/Profiles/AccessLog/Write" and Category == "AzureCdnAccessLog"
| where
userAgent_s has "ipad"
| summarize iPad=dcount(clientIp_s) by bin(TimeGenerated,1h);
MacOSX
| join iPad on TimeGenerated
| render columnchart

这也可以,但我想计算三个设备的数量。

| where userAgent_s has "iphone" or userAgent_s has "ipad" or userAgent_s has "macintosh"
| summarize count() by userAgent_s
| render piechart

更接近,但不会让我用它制作饼图。

AzureDiagnostics
| where userAgent_s has "iphone" or userAgent_s has "ipad" or userAgent_s has "macintosh"
| summarize MacOs=dcountif(clientIp_s, userAgent_s has "macintosh"),
iPad=dcountif(clientIp_s, userAgent_s has "ipad"),
iPhone=dcountif(clientIp_s, userAgent_s has "iphone")

我知道我错过了一些如此基本的东西,但我只是知道得不够。

最佳答案

正如我在评论中所说,图表是基于表格数据构建的。
饼图需要两列,类别
然而,您的查询会产生透视形式,即每个类别都存储在不同的列中。

附注
Log Analytics 使用 client_ip_s(而不是clientIp_s)

// Sample data generation. Not part of the solution. 
let prob = toscalar(range i from 0 to 2 step 1 | summarize make_list(repeat(i, tolong(bin(rand() * 1000, 1)))));
let prob_len = array_length(prob);
let devices = dynamic(["macintosh", "ipad", "iphone"]);
let AzureDiagnostics = range i from 1 to 10000 step 1
| extend userAgent_s = tostring(devices[toint(prob[toint(rand(prob_len))])])
,client_ip_s = format_ipv4(toint(rand(0x00FFFFFF)));
// Solution starts here
let search_terms = dynamic(["macintosh", "ipad", "iphone"]);
AzureDiagnostics
| where userAgent_s has_any (search_terms)
| mv-apply search_term = search_terms to typeof(string) on (where userAgent_s has search_term)
| summarize dcount(client_ip_s) by search_term
| render piechart
<表类=“s-表”><标题>搜索词dcount_client_ip_s <正文>ipad6538麦金塔2988iPhone428

Pie Chart

Fiddle

关于azure - 使用 userAgent 列对 Azure CDN 访问日志进行 KQL 查询,按设备类型进行排序和计数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/73854293/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com