Aggregate data per Bank and per day in KQL

0
votes

I'm in working on project with goal of connecting multiple banks, in Netherlands, into our platform.

For now, every time a user connects to a single bank, we want to send out a metric and show it in Azure dashboard. We are almost there, except that we want to aggregate the sum per day. This is what we have right now:

connections last 3 days

For example, looking at ABN AMRO, we have:

  1. ABN AMRO had 2081 connections on 25/01/2021
  2. ABN AMRO had 2325 connections on 24/01/2021
  3. ABN AMRO had 5082 connections on 23/31/2021

But what we want is to sum it like this:

  1. ABN AMRO had 2081 + 2325 + 5082 on 25/01/2021 = 9488
  2. ABN AMRO had 2325 + 5082 on 24/01/2021 = 7407
  3. ABN AMRO had 5082 on 23/31/2021 = 5082

This is the query used so far:

customMetrics
| where name == "CustomerGrantedConsent"
| extend BankName = customDimensions.BankName
| summarize Count = count() by tostring(BankName), bin(timestamp, 1d)
| order by timestamp

How?

2
azureazure-devopsazure-functionskql
Won't this work? Just add | summarize sum(CC) as total_by_bank_time by TT, BB between last two lines (| summarize CC = count() by tostring(BankName) as BB, bin(timestamp, 1d) as TT and | order by timestamp) – Kashyap

2 Answers

1
votes
let T = datatable(day:datetime  , value:long)
[
   "2021-01-25", 3000,
   "2021-01-24", 2000,
   "2021-01-23", 1000
];
T
| order by day asc 
| serialize cs=row_cumsum(value)
| project  day, cs

enter image description here

1
votes

Try using row_cumsum

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

It will return the output as your require

  1. ABN AMRO had 2081 + 2325 + 5082 on 25/01/2021 = 9488
  2. ABN AMRO had 2325 + 5082 on 24/01/2021 = 7407
  3. ABN AMRO had 5082 on 23/31/2021 = 5082

You can read about the row_cusmum at here.