Azure中如何通过Kusto查询获取属性关联的自定义属性
调整Kusto查询实现pkgName关联organization值输出
核心调整思路
- 原查询以
pkgName为分组维度完成去重计数、Top10截断、Other类聚合的逻辑需要完全保留,避免改动原有统计结果 - 将
organization属性从与pkgName同维度的自定义字段中提取,建立稳定的pkgName-organization映射关系 - 处理Top10截断场景下,"Other"类聚合项对应的organization值收集逻辑,避免映射丢失
完整调整后查询代码
let mainTable = union customEvents | extend name =replace("\n", "", name) | where iif('*' in ("*"), 1 == 1, name in ("*")) | where true; let queryTable = mainTable; // 统一提取pkgName、organization属性并做空值兜底 let baseExtracted = queryTable | extend pkgName = tostring(customDimensions["pkgName"]), organization = tostring(customDimensions["organization"]) | extend pkgName = iif(isempty(pkgName), "<undefined>", pkgName), organization = iif(isempty(organization), "<undefined>", organization); // 按原逻辑完成pkgName维度的计数、排名、Other类聚合 let pkgNameAgg = baseExtracted | summarize hll = hll(itemId) by pkgName | extend Events = dcount_hll(hll) | order by Events desc | serialize rank = row_number() | extend pkgName = iff(rank > 10, 'Other', pkgName) | summarize merged = hll_merge(hll) by pkgName | project pkgName, Counts = dcount_hll(merged); // 构建单pkgName到关联organization的映射 let singlePkgOrgMap = baseExtracted | summarize linkedOrgs = make_set(organization, 100) by pkgName; // 收集所有归为Other类的pkgName对应的关联organization let otherOrgMap = singlePkgOrgMap | join kind=rightanti (pkgNameAgg | where pkgName != "Other") on pkgName | summarize linkedOrgs = make_set(linkedOrgs[0], 1000); // 合并成统一的映射表 let finalOrgMap = singlePkgOrgMap | union (otherOrgMap | project pkgName="Other", linkedOrgs) | mv-expand linkedOrgs to typeof(string) | summarize linkedOrgs = make_set(linkedOrgs) by pkgName; // 关联生成最终带organization的结果 pkgNameAgg | join kind=leftouter finalOrgMap on pkgName | project pkgName, Counts, AssociatedOrganizations = linkedOrgs
提示:如果业务场景中每个
pkgName固定关联唯一的organization值,可将映射段的make_set逻辑替换为any(organization)聚合,有效降低查询扫描开销。
输出字段说明
pkgName:和原查询输出完全一致的包名维度值,含截断为"Other"的聚合项Counts:对应pkgName的去重事件计数,和原查询统计结果无差异AssociatedOrganizations:当前pkgName关联的所有organization值,以去重数组形式展示
内容的提问来源于stack exchange,提问作者Parkavi
相关产品推荐
相关产品推荐

