Aggregate data by properties in KQL

Question

This question is a continuation of here I'm in working on project with goal of connecting multiple banks, in Netherlands, into our platform.

Every time a user connects to a single bank, we want to send out a metric and show it in Azure dashboard. We are already doing it, but we want to extend its functionalities.

customMetrics
| where name == "CustomerGrantedConsent" and customDimensions.IsInitialConsent == "True"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by BankName asc, timestamp asc
| serialize FirstConsents = row_cumsum(Count, BankName != prev(BankName))

With this query, we are able to aggregate the sum of the consents of banks, when they happen. This is the result so far. As you can see, , we want to sum the amount with time. I mean, if yesterday we had 4 consents, today the total is going to be: yesterday_count + today_count 4 + today_count

Right now, if there are no consents today, we don't show the sum of the previous day and that's the problem. If yesterday, we had 4 consents for BUNQ, today I want to show at least 4:

BUNQ had 4 connections 31-01-2021
BUNQ in total will have, at least, 4 connections today..

How can we do this?

In the future, please provide sample input data in datatable format, so that it would be easy to copy and paste it, to help with the query. You can easily do it in Kusto Explorer by selecting a few records in the results pane, right-click on the selection, and select "Copy as datatable() operator". Thanks. — Slavik N

Slavik N Slavik N · Accepted Answer · 2021-02-01T11:15:38

You need to use make-series instead of summarize in order to have 0s. Here's how:

datatable(Timestamp: datetime, BankName: string) [
    datetime(2021-01-29 08:00:00), "ABN AMRO",
    datetime(2021-01-29 09:00:00), "ABN AMRO",
    datetime(2021-01-28 09:00:00), "Invers",
    datetime(2021-01-28 10:00:00), "Invers",
    datetime(2021-01-28 11:00:00), "Invers",
    datetime(2021-01-29 08:00:00), "Invers",
    datetime(2021-01-29 09:00:00), "Invers",
]
| make-series Count = count() on Timestamp to now() step 1d by tostring(BankName)
| mv-expand Count to typeof(long), Timestamp to typeof(string)
| order by BankName asc, Timestamp asc
| extend FirstConsents = row_cumsum(Count, BankName != prev(BankName))

The output will be:

BankName	Count	Timestamp	FirstConsents
ABN AMRO	2	2021-01-28 11:12:50	2
ABN AMRO	0	2021-01-29 11:12:50	2
ABN AMRO	0	2021-01-30 11:12:50	2
ABN AMRO	0	2021-01-31 11:12:50	2
Invers	3	2021-01-27 11:12:50	3
Invers	2	2021-01-28 11:12:50	5
Invers	0	2021-01-29 11:12:50	5
Invers	0	2021-01-30 11:12:50	5
Invers	0	2021-01-31 11:12:50	5

Aggregate data by properties in KQL

1 Answers