Azure Application Insights - How to display a row with default values if the kusto query returned no results?

Question

I am using the below Kusto query in the Azure Application Insights workbook to get the count of satisfied users, tolerating users, and frustrated users.

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"]

The above query returned the results without any issues. But whenever there is no data, then this query returns a text message that says, "no results". But I want to display a single row with the default value "0" in each column.

Example:

Updated:

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

The above query adds the empty row, even in the case of results. I tried to update the above query with the below lines of code to add the empty row in the event of no results only. but it is still not working as expected.

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

You could do it in various ways, like unioning with an empty row:

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

but that will ALWAYS add the empty row. you could then possibly use the scan operator (https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/scan-operator) to possibly filter out the empty group?

might be more work than just the "no results" message (you can customize the no results message in the advanced settings tab as well)

Edit: it looks like your edits created a bunch of things that just are invalid syntax. StackOverflow's goal isn't to do all your work for you...

but if i copy and paste your stuff from all above and just fix the syntax issues it seems like it works:

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)