Search code examples
azurekqlazure-monitoringazure-monitorazure-monitor-workbooks

How to create a continuous time chart with missing values (or zero) in Azure Monitor workbook?

I am trying to create a time-chart showing request per minute (RPS) metric with two separate lines: one for the total requests (throttled + non throttled), and the other for throttled only requests. I manage to get the time-chart, see the image below, but if you notice the red line representing throttled requests shows up as not-continuous and has gaps in it compared to the blue line. How can I make the red line drops to zero on the x-axis and then it spikes back when it has a positive value. Visually, this makes more sense and also clearly shows a drop to zero.

I was doing some research and found out that maybe having a series (make-series operator) makes it easier but not sure how to fit it into this query.

enter image description here

let T = datatable(Timestamp:datetime, ResultType:string, ResultSignature:string, CorrelationId:string) [
"2022-03-02T14:35:05.6846874Z", "Throttled", "200", "a8de8a0b-2b95-4e16-a90f-d96c1f404850",
"2022-03-02T14:35:06.9535229Z", "Throttled", "200", "7e00ac15-6e82-42a5-8171-3145ae27728f",
"2022-03-02T14:34:21.1880149Z", "Non-Throttled", "200", "8fa9f7ee-6a91-4b8c-b170-9649befa698c",
"2022-03-02T14:34:36.9887590Z", "Non-Throttled", "200", "de7d82be-49b8-44dc-856c-16f76c7a4ae5",
"2022-03-02T14:34:39.3999879Z", "Non-Throttled", "200", "99b67d55-3ee4-4aee-9415-03919b2f23a4",
"2022-03-02T14:34:40.7854748Z", "Non-Throttled", "400", "dec5cd49-9d64-469a-83aa-db759c2e2fb1",
"2022-03-02T14:34:44.2007485Z", "Non-Throttled", "200", "5b412e71-6e48-49e2-9298-fd13d31619d1",
"2022-03-02T14:34:55.6858503Z", "Throttled", "200", "482592f9-722c-4f5d-8e48-967fa655d704",
"2022-03-02T14:25:17.0269766Z", "Throttled", "200", "1732c865-2474-4f76-b0cd-64af5981af7c",
"2022-03-02T14:25:18.9668944Z", "Throttled", "200", "234ec84c-3a0a-4329-a492-f8d590267ec6",
"2022-03-02T14:25:21.8262878Z", "Throttled", "200", "be8bd024-8f5c-4a01-9703-2945ef3bc8ba",
    ];
T 
| project Timestamp, ResultType, ResultSignature , CorrelationId
| summarize Throttled =  countif(ResultType == "Throttled"),
            Total_Requests = count()
         by bin(Timestamp,1m)
Solution

  • in order to switch to using make-series, you can replace this:

    | summarize Throttled = countif(ResultType == "Throttled"), Total_Requests = count() by bin(Timestamp,1m)

    with this:

    | make-series Throttled = countif(ResultType == "Throttled"), Total_Requests = count() on Timestamp step 1m