dosubot[bot] commented on issue #7562:
URL:
https://github.com/apache/incubator-devlake/issues/7562#issuecomment-2145932710
<!-- Greeting -->
Hey @kzhou57! 👋 I'm here to help you with any bugs, questions, or
contributions you have for the project. Just give me a shout if you need
anything!
<!-- Answer -->
To resolve the `AxiosError: Request failed with status code 500` on the
projects page and the SQL query execution errors in the Grafana DORA dashboard,
follow these steps:
1. **Check Data Sources**: Ensure that the required data sources
(`Deployments`, `Pull Requests`, `Incidents`) are correctly set up and
connected in Grafana. These data sources should be from Jenkins, GitLab CI,
GitHub Action, webhook, Jira issues, GitHub issues, TAPD issues, PagerDuty
Incidents, etc.
2. **Define Transformations**: Make sure that the `deployments` and
`incidents` are defined in the data transformations while configuring the
blueprint of a project. This is crucial for the SQL queries to fetch the
correct data.
3. **Validate SQL Queries**: Ensure that the SQL queries used in the Grafana
panels are correct and can execute without errors. Here is an example of a SQL
query used in the DORA dashboard:
```sql
-- Metric 1: Deployment Frequency
with last_few_calendar_months as(
-- construct the last few calendar months within the selected time
period in the top-right corner
SELECT CAST(($__timeTo()-INTERVAL (H+T+U) DAY) AS date) day
FROM ( SELECT 0 H
UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT
300
) H CROSS JOIN ( SELECT 0 T
UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT
30
UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT
60
UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT
90
) T CROSS JOIN ( SELECT 0 U
UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT
3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT
6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT
9
) U
WHERE
($__timeTo()-INTERVAL (H+T+U) DAY) > $__timeFrom()
),
_production_deployment_days as(
-- When deploying multiple commits in one pipeline, GitLab and
BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.
SELECT
cdc.cicd_deployment_id as deployment_id,
max(DATE(cdc.finished_date)) as day
FROM cicd_deployment_commits cdc
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and
pm.`table` = 'cicd_scopes'
WHERE
pm.project_name in (${project})
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
),
_days_weekly_deploy as(
-- calculate the number of deployment days every week
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-WEEKDAY(last_few_calendar_months.day) DAY)) as week,
MAX(if(_production_deployment_days.day is not null, 1, 0))
as weeks_deployed,
COUNT(distinct _production_deployment_days.day) as
days_deployed
FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY week
),
_days_monthly_deploy as(
-- calculate the number of deployment days every month
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-DAY(last_few_calendar_months.day)+1 DAY)) as month,
MAX(if(_production_deployment_days.day is not null, 1,
null)) as months_deployed,
COUNT(distinct _production_deployment_days.day) as
days_deployed
FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY month
),
_days_six_months_deploy AS (
SELECT
month,
SUM(days_deployed) OVER (
ORDER BY month
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS days_deployed_per_six_months,
COUNT(months_deployed) OVER (
ORDER BY month
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS months_deployed_count,
ROW_NUMBER() OVER (
PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6
ORDER BY month DESC
) AS rn
FROM _days_monthly_deploy
),
_median_number_of_deployment_days_per_week_ranks as(
SELECT *, percent_rank() over(order by days_deployed) as ranks
FROM _days_weekly_deploy
),
_median_number_of_deployment_days_per_week as(
SELECT max(days_deployed) as
median_number_of_deployment_days_per_week
FROM _median_number_of_deployment_days_per_week_ranks
WHERE ranks <= 0.5
),
_median_number_of_deployment_days_per_month_ranks as(
SELECT *, percent_rank() over(order by days_deployed) as ranks
FROM _days_monthly_deploy
),
_median_number_of_deployment_days_per_month as(
SELECT max(days_deployed) as
median_number_of_deployment_days_per_month
FROM _median_number_of_deployment_days_per_month_ranks
WHERE ranks <= 0.5
),
_days_per_six_months_deploy_by_filter AS (
SELECT
month,
days_deployed_per_six_months,
months_deployed_count
FROM _days_six_months_deploy
WHERE rn%6 = 1
),
_median_number_of_deployment_days_per_six_months_ranks as(
SELECT *, percent_rank() over(order by days_deployed_per_six_months)
as ranks
FROM _days_per_six_months_deploy_by_filter
),
_median_number_of_deployment_days_per_six_months as(
SELECT min(days_deployed_per_six_months) as
median_number_of_deployment_days_per_six_months, min(months_deployed_count) as
is_collected
FROM _median_number_of_deployment_days_per_six_months_ranks
WHERE ranks >= 0.5
)
SELECT
CASE
WHEN ('$dora_report') = '2023' THEN
CASE
WHEN median_number_of_deployment_days_per_week >= 7 THEN
CONCAT(median_number_of_deployment_days_per_week, ' deployment days per
week(elite)')
WHEN median_number_of_deployment_days_per_week >= 1 THEN
CONCAT(median_number_of_deployment_days_per_week, ' deployment days per
week(high)')
WHEN median_number_of_deployment_days_per_month >= 1
THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days per
month(medium)')
WHEN median_number_of_deployment_days_per_month < 1 and
is_collected is not null THEN
CONCAT(median_number_of_deployment_days_per_month, ' deployment days per
month(low)')
ELSE "N/A. Please check if you have collected
deployments." END
WHEN ('$dora_report') = '2021' THEN
CASE
WHEN median_number_of_deployment_days_per_week >= 7 THEN
CONCAT(median_number_of_deployment_days_per_week, ' deployment days per
week(elite)')
WHEN median_number_of_deployment_days_per_month >= 1
THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days per
month(high)')
WHEN median_number_of_deployment_days_per_six_months >=
1 THEN CONCAT(median_number_of_deployment_days_per_six_months, ' deployment
days per six months(medium)')
WHEN median_number_of_deployment_days_per_six_months < 1
and is_collected is not null THEN
CONCAT(median_number_of_deployment_days_per_six_months, ' deployment days per
six months(low)')
ELSE "N/A. Please check if you have collected
deployments." END
ELSE 'Invalid dora report'
END AS 'Deployment Frequency'
FROM _median_number_of_deployment_days_per_week,
_median_number_of_deployment_days_per_month,
_median_number_of_deployment_days_per_six_months
```
4. **Validate and Debug**: Use the [DORA validation
dashboard](https://devlake.apache.org/docs/DORA) to validate and debug the
dashboard. This can help identify any issues with the data or queries.
By following these steps, you should be able to resolve the `AxiosError:
Request failed with status code 500` and the SQL query execution errors in the
Grafana DORA dashboard
<sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/grafana/dashboards/DORA.json)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
