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:

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

How can we do this?

Answer 1

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