Kusto(Azure Data Explorer):如何通过给定的键值筛选器字典筛选结果

Question

我有一个运行查询的Kusto函数，我想添加一个输入参数，该参数将用于过滤函数内部的数据。

假设函数的主体如下，在一个具有Dimensions列和JSON (string)值的表上

Table
| summarize count()

我想添加一个参数"filterDictionary"，并将符合筛选器参数的结果保存在伪代码中：

Table
for each key-value in the 'filterDictionary', filter with:
where Dimensions[key_i] == value_i
| summarize count()

有可能吗？最好的方法是什么

假设"filterDictionary"的一个示例可以是：

{
"DataCenter": "CUS",
"Result": "Failure"
}

谢谢！

Answer 1

使用mv-apply，对于每个t记录，我们传递filterDictionary密钥，并计算filterDictionary[k]和t[k]之间的不匹配数量
我们只筛选出0个不匹配项。

.create-or-alter function f(filterDictionary:dynamic)
{
// Generate data sample. Not part of the solution.
let t = materialize(range i from 1 to 100 step 1 | extend dummy1 = toint(rand(100)), dummy2 = toint(rand(100)), Dimensions = dynamic_to_json(pack_dictionary("Result", tostring(dynamic(["Failure", "Success"])[toint(rand(2))]), "ZDummy", toint(rand(100)), "DataCenter", tostring(dynamic(["AUS", "BUS", "CUS", "DUS", "EUS"])[toint(rand(5))]), "MDummy2", toint(rand(100)))));
// Solution starts here.
t
| extend _Dimensions = todynamic(Dimensions)
| mv-apply _k = bag_keys(filterDictionary) to typeof(string) on 
(
summarize _count_diff = countif(tostring(filterDictionary[_k]) != tostring(_Dimensions[_k])), _max = arg_max(1, *) 
| where _count_diff == 0
)
| project-away _*
}

---

f(dynamic({"DataCenter": "CUS", "Result": "Failure"}))

>尺寸>84{数据中心"："CUS"，"MDummy2"：38，"结果"："失败"，"ZDummy"：2｝{数据中心"："CUS"，"MDummy2"：79，"结果"："失败"，"ZDummy"：4}>{数据中心"："CUS"，"MDummy2"：63，"结果"："失败"，"ZDummy"：55}

i	假人1	假人2
16	29		{数据中心"："CUS"，"MDummy2"：88，"结果"："失败"，"ZDummy"：29}
23	52
26	60	4	{数据中心"："CUS"，"MDummy2"：12，"结果"："失败"，"ZDummy"：89}
32	30	77
33	32	64	{数据中心"："CUS"，"MDummy2"：46，"结果"："失败"，"ZDummy"：69}
44	31	77	{数据中心"："CUS"，"MDummy2"：0，"结果"："失败"，"ZDummy"：98}
47	55		{数据中心"："CUS"，"MDummy2"：13，"结果"："失败"，"ZDummy"：25}
67	43	50	{数据中心"："CUS"，"MDummy2"：7，"结果"："失败"，"ZDummy"：21}
86	45	66

通过使用bag_keys&bag_move_keys我们创建了Dimensions的一个子集，该子集具有与filterDictionary相同的密钥
使用dynamic_to_json，我们将它们都更改为规范的字符串表示，然后对它们进行比较。

.create-or-alter function f(filterDictionary:dynamic)
{
// Generate data sample. Not part of the solution.
let t = materialize(range i from 1 to 100 step 1 | extend dummy1 = toint(rand(100)), dummy2 = toint(rand(100)), Dimensions = dynamic_to_json(pack_dictionary("Result", tostring(dynamic(["Failure", "Success"])[toint(rand(2))]), "ZDummy", toint(rand(100)), "DataCenter", tostring(dynamic(["AUS", "BUS", "CUS", "DUS", "EUS"])[toint(rand(5))]), "MDummy2", toint(rand(100)))));
// Solution starts here.
t
| extend _Dimensions = todynamic(Dimensions)
| where dynamic_to_json(bag_remove_keys(_Dimensions, bag_keys(bag_remove_keys(_Dimensions, bag_keys(filterDictionary))))) == dynamic_to_json(filterDictionary)
| project-away _Dimensions
}

---

f(dynamic({"DataCenter": "CUS", "Result": "Failure"}))

>尺寸{数据中心："CUS"，"MDummy2"：19，"结果"："失败"，"ZDummy"：90}>{数据中心"："CUS"，"MDummy2"：60，"结果"："失败"，"ZDummy"：65}{数据中心"："CUS"，"MDummy2"：57，"结果"："失败"，"ZDummy"：91}{数据中心"："CUS"，"MDummy2"：67，"结果"："失败"，"ZDummy"：0}4{数据中心"："CUS"，"MDummy2"：35，"结果"："失败"，"ZDummy"：1｝

i	假人1	假人2
3	16	22
6	33	29
7	92	90	{数据中心"："CUS"，"MDummy2"：38，"结果"："失败"，"ZDummy"：6}
12	36	71
46	95	0
62	97
80	71	57	{数据中心"："CUS"，"MDummy2"：7，"结果"："失败"，"ZDummy"：92}
88	4	33	{数据中心"："CUS"，"MDummy2"：41，"结果"："失败"，"ZDummy"：58}