基于Kusto中的一个键合并多个表中的数据

Question

我正在尝试在Azure日志分析中合并多个表。每个表都有一个唯一的列和一个公共列。将它们与Join((合并效率很低，因为我一次只能处理两个表。Union((似乎是正确的函数，但当我合并表时，我的公共列中出现了重复的行。

示例：

// CPU usage
let CPU_table=VPN_Metrics_CL | extend timestamp = (todatetime(ts_s)+7h) 
| where metric_s == "system/cpmCPUTotal1Min.rrd"  
| extend region = substring(host_s,0,4)
| summarize maxCPU = max(val_d) by region
| extend score_CPU = case(maxCPU <= 59, 0, 
maxCPU <= 79, 1, 
3)
| project score_CPU, region;
// Memory usage
let Memory_table=VPN_Metrics_CL| extend timestamp = todatetime(ts_s)+7h
| where metric_s in ("hw_mem_used_pct") and val_d >= 0 and host_s contains "vpn"
| extend region = substring(host_s,0,4)
| summarize maxMemory = max(val_d) by region
| extend score_mem = case(maxMemory <= 59, 0, 
maxMemory <= 79, 1, 
3)
| project score_mem, region;
union CPU_table, Memory_table

我计划总共有10张以上的桌子。

结果如下：

score_mem  |  score_CPU  |  region
0                         USA
0            USA
etc. etc.

如何根据关键字合并行？关键在于区域。

感谢

Answer 1

如果源是同一个表，最有效的方法是使用条件聚合：

let isCpuMetric = (metric_s:string) {metric_s == "system/cpmCPUTotal1Min.rrd"};
let isMemoryMetric =  (metric_s:string, val_d:double, host_s:string) {metric_s in ("hw_mem_used_pct") and val_d >= 0 and host_s contains "vpn"};
VPN_Metrics_CL 
| extend timestamp = (todatetime(ts_s)+7h) 
| extend region = substring(host_s,0,4)
| where isCpuMetric(metric_s) or isMemoryMetric(metric_s, val_d, host_s)
| summarize maxCPU = maxif(val_d, isCpuMetric(metric_s)), maxMemory=maxif(val_d, isMemoryMetric(metric_s, val_d, host_s)) by region
| extend score_mem = case(maxMemory <= 59, 0, maxMemory <= 79, 1, 3),
score_CPU = case(maxCPU <= 59, 0, maxCPU <= 79, 1, 3)

如果来源不同，您仍然可以加入或查找运算符。如果您有结果R1。。RN-来自子查询：

R1 
| lookup R2 on Region
| lookup R3 on Region
...

查找运算符的文档：https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/lookupoperator

Answer 2

我发现给每个类别的分数列起相同的名称更容易："得分；然后使用Union，我将所有表格合并并汇总总分。

union CPU_table, Memory_table, AAA_table, bw_data, more_tables.....
| summarize score_total = sum(score) by region, bin(timestamp, $__interval)
| project score_total, region, timestamp