Aggregation of data from custom metric

[andykrk]

Hello,

I am currently working on a Dashboard element that requires me to show error data coming from custom AWS metric. Basic DQL query in the Notebook:

timeseries sum(`my_custom_aws_metric`),
  interval: 1d,
  from: "2025-05-05",
  by: {aws.account.id, app_name, db_name, table_name},
  filter: { matchesValue(aws.account.id, { “1234” })}
| fieldsAdd error_count = arraySum(`sum(\`my_custom_aws_metric\`)`) 
| fieldsKeep timeframe, app_name, db_name, table_name, aws.account.id, error_count
| filterOut error_count == 0

produces the following output:

I am trying to breakdown the timeframe and aggregation into daily chunks. I have tested multiple ways like for example:

timeseries {
  error_count   = sum(`my_custom_aws_metric`),  
  bucket_start  = start(),           
  bucket_end    = end()              
},
interval: 1d,
from: "2025-05-05",
to:   "2025-05-07",                    
by: {
  aws.account.id,
  app_name,
  db_name,
  table_name
},
filter: { aws.account.id == "1234" }   
| expand error_count
| expand bucket_start
| expand bucket_end
| filter   app_name    == “some_app”
| filterOut error_count == 0 or isNull(error_count)
| fieldsRename
    start = bucket_start,
    end   = bucket_end
| fieldsKeep start, end, error_count,
             aws.account.id, app_name, db_name, table_name
| sort      start asc

but it produced overlapping time intervals (I guess because of multiple expand commands) and also presented total error count from the whole period for each line:

For reference we want error_count = 9 for each of those start and end dates:
05/05/2025 - 06/05/2025
06/05/2025 - 07/05/2025
(this can also be simply day date)

I have tried multiple other methods but in the end I get mostly the same problems. Some of the commands I wanted to use like expand datapoints or arrayZip were unfortunately throwing error. 

Any suggestions how to break it down the needed way?

[marco_irmer · Accepted solution]

See if this DQL snippet helps.

[andykrk]

Thank you very much, Marco! After some adaptation, this solution works great for me.

[andykrk]

@marco_irmer I have one additional question. Currently one of the apps that I have (there is couple dozens of them) is running 2 times a day so we are adding up errors within the interval. Is it possible to pick the latest occurrence within the interval in case there is more than one? I think the timestamp is not explicitly listed as part of the metric but perhaps when we get the metric the time is passed automatically.