Dynamically create individual columns for last 7 days in Kusto

Question

From data gathered in Azure AppInsights, I was able to generate a report of click counts of custom events for the last 7 days using the following Kusto query.

let usg_events = dynamic(["click_event"]);
let mainTable24 = customEvents
    | where timestamp > ago(8d) and isempty(operation_SyntheticSource)
    | extend name =replace("\n", "", name)
    | where '*' in (usg_events) or name in (usg_events)
    | extend dimension = tostring(customDimensions["click_event"])
    | where dimension != ""
    | summarize
        count_24h = countif(timestamp > ago(24h)),
        count_y = countif(timestamp > ago(2d) and timestamp < ago(1d)),
        count_2 = countif(timestamp > ago(3d) and timestamp < ago(2d)),
        count_3 = countif(timestamp > ago(4d) and timestamp < ago(3d)),
        count_4 = countif(timestamp > ago(5d) and timestamp < ago(4d)),
        count_5 = countif(timestamp > ago(6d) and timestamp < ago(5d)),
        count_6 = countif(timestamp > ago(7d) and timestamp < ago(6d))
        by dimension
    | project dimension, count_24h, count_y, count_2, count_3, count_4, count_5, count_6;
mainTable24

The Output looks like this:

The output shows the number of clicks per day per button for the last 7 days.

However, I was wondering if there is a better way to achieve this dynamically than to use if clauses for every single day ? As this isn't maintainable if I want to increase it for more days.

Answer 1

However, I was wondering if there is a better way to achieve this dynamically than to use if clauses for every single day ?

I have reproduced in my environment and got expected results as below:

Used below query:

let r = datatable(D:string, timestamp:datetime, Event:long)
[
    "Button-1", datetime(2023-05-10 00:00:00), 11,
    "Button-1", datetime(2023-05-11 00:00:00), 151,
    "Button-1", datetime(2023-05-11 12:00:00), 29,
    "Button-1", datetime(2023-05-12 00:00:00), 52,
    "Button-1", datetime(2023-05-13 00:00:00), 22,
    "Button-1", datetime(2023-05-14 00:00:00), 80,
    "Button-1", datetime(2023-05-14 12:00:00), 80,
    "Button-1", datetime(2023-05-15 00:00:00), 20,
    "Button-1", datetime(2023-05-16 00:00:00), 50,
    "Button-2", datetime(2023-05-10 00:00:00), 59,
    "Button-2", datetime(2023-05-10 10:00:00), 9,
    "Button-2", datetime(2023-05-11 00:00:00), 82,
    "Button-2", datetime(2023-05-12 00:00:00), 22,
    "Button-2", datetime(2023-05-13 00:00:00), 30,
    "Button-2", datetime(2023-05-14 00:00:00), 16,
    "Button-2", datetime(2023-05-14 10:00:00), 1,
    "Button-2", datetime(2023-05-15 00:00:00), 17,
    "Button-2", datetime(2023-05-16 00:00:00), 13,
    "Button-3", datetime(2023-05-10 00:00:00), 13,
    "Button-3", datetime(2023-05-11 00:00:00), 116,
    "Button-3", datetime(2023-05-12 00:00:00), 11,
    "Button-3", datetime(2023-05-13 00:00:00), 18,
    "Button-3", datetime(2023-05-14 00:00:00), 114,
    "Button-3", datetime(2023-05-15 00:00:00), 15,
    "Button-3", datetime(2023-05-16 00:00:00), 112
];
let m = r
    | where timestamp > ago(8d) 
    | where D != ""
    | extend day1 = format_datetime(timestamp, 'yyyy-MM-dd')
    | summarize Count = count() by D, day1
    | evaluate pivot (day1, sum(Count));
m

Output:

Fiddle

Here I have got dates dynamically without adding it manually like yours.