How to calculate error percentage in the alert

Grafana Alerting
1

Query A

SELECT
  toString(statusCode) AS HTTP_Status_Code,
  timestamp_min AS time,
  cnt_all AS http
FROM akamai.summary_vod
WHERE
  timestamp_min >= now() - INTERVAL 6 MINUTE
  AND timestamp_min <  now() - INTERVAL 3 MINUTE
  AND workflow = 'ssai'
  AND secondaryWorkflow='segments'
  AND streamId = '81733'
  AND HTTP_Status_Code = '504'
  AND failover = 'TD-failover'
GROUP BY
  HTTP_Status_Code,
  time
ORDER BY
  time

Query B

SELECT
  toString(statusCode) AS HTTP_Status_Code,
  timestamp_min AS time,
  cnt_all AS http
FROM akamai.summary_vod
WHERE
  timestamp_min >= now() - INTERVAL 6 MINUTE
  AND timestamp_min <  now() - INTERVAL 3 MINUTE
  AND workflow = 'ssai'
  AND secondaryWorkflow='segments'
  AND streamId = '81733'
  AND HTTP_Status_Code = '200'
GROUP BY
  HTTP_Status_Code,
  time
ORDER BY
  time

I want the query to print 502, 503, 504 in the same query then I’ll use expression(Math) to calculate percentage error (5xx*2)/200. Can I do it individually for 502, 503 and 504

  • What Grafana version and what operating system are you using?

  • What are you trying to achieve?

  • How are you trying to achieve it?

  • What happened?

  • What did you expect to happen?

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

Since you have a SQL engine, you can just do the math in SQL. For example, you can remove the status code, and filter on HTTP_Status_Code LIKE ‘50%’, that would give you all errors in query A.

3

here the challenge I’m facing is, I want math to be calculated on 502/200, 503/200 etc

4

And you want alert on each code individually or on the sum of them ?

5

Yes.

Below is my query.

Query A

SELECT

now() AS time,



cnt_200,

cnt_502,

cnt_503,

cnt_504,

-- Percentages (sampling aware)

if(cnt_200 > 0, (cnt_502 * 2) / cnt_200, 0) AS pct_502,

if(cnt_200 > 0, (cnt_503 * 2) / cnt_200, 0) AS pct_503,

if(cnt_200 > 0, (cnt_504 * 2) / cnt_200, 0) AS pct_504,

-- Dynamic breached codes for Summary

concat(

‘:police_car_light: SSAI MANIFEST HINDI 5XX Threshold Breach >=0.1 (Post FA)’,

'\nEvaluation Window: ',

    formatDateTime(now() - INTERVAL 2 MINUTE, '%H:%M'),

’ - ',

    formatDateTime(now() - INTERVAL 1 MINUTE, '%H:%M'),

‘\n\nTraffic:’,

'\n200 Count: ', toString(cnt_200),

if(cnt_200 > 0 AND (cnt_502 * 2) / cnt_200 >= 0.001,

concat(

‘\n\n​:red_circle: 502’,

'\nCount: ', toString(cnt_502),

'\nPercentage: ', toString(round((cnt_502 * 2) / cnt_200,5))

), ‘’),

if(cnt_200 > 0 AND (cnt_503 * 2) / cnt_200 >= 0.001,

concat(

‘\n\n​:red_circle: 503’,

'\nCount: ', toString(cnt_503),

'\nPercentage: ', toString(round((cnt_503 * 2) / cnt_200,5))

), ‘’),

if(cnt_200 > 0 AND (cnt_504 * 2) / cnt_200 >= 0.001,

concat(

‘\n\n​:red_circle: 504’,

'\nCount: ', toString(cnt_504),

'\nPercentage: ', toString(round((cnt_504 * 2) / cnt_200,5))

), ‘’)

) AS annotation_output

FROM

(

SELECT

    sumIf(cnt, statusCode = 200) AS cnt_200,

    sumIf(cnt, statusCode = 502 AND failover='TD-failover') AS cnt_502,

    sumIf(cnt, statusCode = 503 AND failover='TD-failover') AS cnt_503,

    sumIf(cnt, statusCode = 504 AND failover='TD-failover') AS cnt_504

FROM

(

SELECT

        timestamp_min,

        statusCode,

        failover,

        countMerge(\`count()\`) AS cnt

FROM akamai.summary_vod

WHERE

        timestamp_min >= now() - INTERVAL 2 MINUTE

AND timestamp_min < now() - INTERVAL 1 MINUTE

AND workflow = ‘ssai’

AND (

            secondaryWorkflow LIKE 'cw_master%'

OR secondaryWorkflow LIKE ‘cw_child%’

OR secondaryWorkflow LIKE ‘ncw_child%’

)

AND streamId = ‘81734’

AND statusCode IN (200,502,503,504)

GROUP BY

        timestamp_min,

        statusCode,

        failover

)

)

WHERE

cnt_200 > 0

AND (

(cnt_502 * 2) / cnt_200 >= 0.00001

OR (cnt_503 * 2) / cnt_200 >= 0.00001

OR (cnt_504 * 2) / cnt_200 >= 0.00001

)

Expression B (Reduce):

Input –> A

Function –> Last

Mode –> Drop Non-numeric Values

Expression C (Threshold):
Input –> B

IS ABOVE OR EQUAL TO –> 0.00001 (for testing)

annotation used: {{ $values.A.annotation_output }}

Even after threshold breaches alert is never changing it state and alert is not triggering.

Could you please suggest.