Azure应用程序洞察--如果kusto查询没有返回结果，如何显示带有默认值的行？

Question

我在Azure应用程序Insight工作簿中使用下面的Kusto查询来获取满意用户、容忍用户和沮丧用户的数量。

let apdexThreshhold = toint(1000);
let apdexData = pageViews
    | where timestamp > ago(7d)
    | where name in ('*') or '*' in ('*')
    | extend success = columnifexists('success', true)
    | extend Failure = iff('ConsiderFailures' == 'ConsiderFailures' and success == false, 1, 0)
    | extend InterestingDimension = iff(isempty(name) == true, 'Unknown', name)
    | where InterestingDimension in ('*') or '*' in ('*')
    | summarize AverageDuration = avg(duration), Failures = sum(Failure) by user_Id, InterestingDimension
    | extend UserExperience = case(AverageDuration <= apdexThreshhold, 'Satisfied', AverageDuration <= 4 * apdexThreshhold, 'Tolerating', 'Frustrated')
    | extend UserExperience = case(Failures > 0, "Frustrated", UserExperience)
    | summarize
        Satisfied = countif(UserExperience == 'Satisfied'),
        Tolerating = countif(UserExperience == 'Tolerating'),
        Frustrated = countif(UserExperience == 'Frustrated'),
        Total = count()
        by InterestingDimension
    | project
        InterestingDimension,
        ["Satisfied Users"] = Satisfied,
        ["Tolerating Users"] = Tolerating,
        ["Frustrated Users"] = Frustrated,
        ["Apdex Score"] = round((Satisfied + (Tolerating / 2.0)) / Total, 2),
        Total
    | extend Relevance = iff(["Apdex Score"] == 0, pow(Total, 1.6), Total / ["Apdex Score"])
    | project-rename Users = Total
    | order by Relevance desc
    | project-away Users, Relevance;
apdexData
| extend ["Apdex Interpretation"] = case(["Apdex Score"] <= 0.5, '⛔ Unacceptable', ["Apdex Score"] <= 0.7, '⚠️ Poor', ["Apdex Score"] <= 0.85, '⚠️ Fair', ["Apdex Score"] <= 0.94, '✔️ Good', '✔️ Excellent')
| project
    Values = InterestingDimension,
    ["Apdex Score"],
    ["Apdex Interpretation"],
    ["Satisfied Users"],
    ["Tolerating Users"],
    ["Frustrated Users"]

上面的查询返回的结果没有任何问题。但是，只要没有数据，这个查询就会返回一条短信，上面写着“没有结果”。但是我希望在每一列中显示默认值为"0“的一行。

示例：

​

​

更新：

let emptyRow = datatable( Values: string, ["Apdex Score"]: double, ["Apdex Interpretation"]: string, ["Satisfied Users"]:long, ["Tolerating Users"]: long, ["Frustrated Users"]: long) [ "0", 0, "0", 0, 0, 0] ;

<Above Query>

// add empty row
| union (emptyRow)
| order by ["Apdex Interpretation"] desc

上面的查询添加空行，即使在结果的情况下也是如此。我尝试用下面的代码行更新上面的查询，以便只在没有结果的情况下添加空行。但它仍未如预期的那样发挥作用。

let T = apdexData 
| where Values!=null
| project
    Values = InterestingDimension,
    ["Apdex Score"],
    ["Apdex Interpretation"],
    ["Satisfied Users"],
    ["Tolerating Users"],
    ["Frustrated Users"];
    
let T_has_records = toscalar(T | summarize count() > 0);
union 
(T | where T_has_records == true),
(emptyRow | where T_has_records == false)

Answer 1

您可以通过各种方式这样做，比如使用空行进行联合：

let emptyRow = datatable( Values: string, ["Apdex Score"]: double, ["Apdex Interpretation"]: string, ["Satisfied Users"]:long, ["Tolerating Users"]: long, ["Frustrated Users"]: long) [ "0", 0, "0", 0, 0, 0] ;
...
your existing query above
...

// add empty row
| union (emptyRow)
| order by ["Apdex Interpretation"] desc

但是，这将总是添加空行。然后，您可以使用scan运算符(https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scan-operator)来筛选空组吗？

可能不仅仅是“无结果”消息(您也可以在“高级设置”选项卡中自定义“无结果”消息)。

编辑:看起来你的编辑创建了一堆只是无效语法的东西。StackOverflow的目标不是为你做所有的工作..。

但是，如果我从上面复制并粘贴您的内容，只需修复语法问题，它看起来就会奏效：

let emptyRow = datatable (
    Values: string,
    ["Apdex Score"]: double,
    ["Apdex Interpretation"]: string,
    ["Satisfied Users"]: long,
    ["Tolerating Users"]: long,
    ["Frustrated Users"]: long
) [
    "0", 0, "0", 0, 0, 0
];
let apdexThreshhold = toint(1000);
let apdexData = pageViews
    | where timestamp > ago(7d)
    | where name in ('*') or '*' in ('*')
    | extend success = columnifexists('success', true)
    | extend Failure = iff('ConsiderFailures' == 'ConsiderFailures' and success == false, 1, 0)
    | extend InterestingDimension = iff(isempty(name) == true, 'Unknown', name)
    | where InterestingDimension in ('*') or '*' in ('*')
    | summarize AverageDuration = avg(duration), Failures = sum(Failure) by user_Id, InterestingDimension
    | extend UserExperience = case(AverageDuration <= apdexThreshhold, 'Satisfied', AverageDuration <= 4 * apdexThreshhold, 'Tolerating', 'Frustrated')
    | extend UserExperience = case(Failures > 0, "Frustrated", UserExperience)
    | summarize
        Satisfied = countif(UserExperience == 'Satisfied'),
        Tolerating = countif(UserExperience == 'Tolerating'),
        Frustrated = countif(UserExperience == 'Frustrated'),
        Total = count()
        by InterestingDimension
    | project
        InterestingDimension,
        ["Satisfied Users"] = Satisfied,
        ["Tolerating Users"] = Tolerating,
        ["Frustrated Users"] = Frustrated,
        ["Apdex Score"] = round((Satisfied + (Tolerating / 2.0)) / Total, 2),
        Total
    | extend Relevance = iff(["Apdex Score"] == 0, pow(Total, 1.6), Total / ["Apdex Score"])
    | project-rename Users = Total
    | order by Relevance desc
    | project-away Users, Relevance;

let T = apdexData
    | extend ["Apdex Interpretation"] = case(["Apdex Score"] <= 0.5, '⛔ Unacceptable', ["Apdex Score"] <= 0.7, '⚠️ Poor', ["Apdex Score"] <= 0.85, '⚠️ Fair', ["Apdex Score"] <= 0.94, '✔️ Good', '✔️ Excellent')
    | project
        Values = InterestingDimension,
        ["Apdex Score"],
        ["Apdex Interpretation"],
        ["Satisfied Users"],
        ["Tolerating Users"],
        ["Frustrated Users"]
    | where isnotempty(Values);

let T_has_records = toscalar(T| summarize count() > 0);

union 
    (T
    | where T_has_records == true),
    (emptyRow
    | where T_has_records == false)