fix: deployment dora dashboard (#6931) (#6942)
* fix: deployment dora dashboard
* fix: deployment dashbord add dora debug update
* fix: some note
* fix: adjust sql
* fix: mysql id lint
* fix: some bugs
* fix: some note
Co-authored-by: abeizn <zikuan.an@merico.dev>
diff --git a/backend/plugins/dora/models/migrationscripts/20240201_add_dora_2023_benchmark.go b/backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
similarity index 98%
rename from backend/plugins/dora/models/migrationscripts/20240201_add_dora_2023_benchmark.go
rename to backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
index 4bd6553..adaece2 100644
--- a/backend/plugins/dora/models/migrationscripts/20240201_add_dora_2023_benchmark.go
+++ b/backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
@@ -62,7 +62,7 @@
Metric: "Deployment frequency",
Low: "Fewer than once per six months(low)",
Medium: "Between once per month and once every 6 months(medium)",
- High: "Between once per week and once per month(high)",
+ High: "Between once per day and once per month(high)",
Elite: "On-demand(elite)",
Benchmarks: "2021 report",
}
@@ -185,7 +185,7 @@
}
func (*adddoraBenchmark2023) Version() uint64 {
- return 20240201000002
+ return 20240207000002
}
func (*adddoraBenchmark2023) Name() string {
diff --git a/grafana/dashboards/DORA.json b/grafana/dashboards/DORA.json
index 895ce9d..835dc6c 100644
--- a/grafana/dashboards/DORA.json
+++ b/grafana/dashboards/DORA.json
@@ -18,7 +18,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 35,
+ "id": 3,
"links": [],
"liveNow": false,
"panels": [
@@ -41,7 +41,7 @@
"showLineNumbers": false,
"showMiniMap": false
},
- "content": "- See [how to config](https://devlake.apache.org/docs/DORA) this dashboard\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, webhook, etc. \n - `Pull Requests` from GitHub PRs, GitLab MRs, BitBucket PRs, Azure DevOps PRs, etc.\n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.\n- You can validate/debug this dashboard with the [DORA validation dashboard](/grafana/d/KGkUnV-Vz/dora-dashboard-validation)",
+ "content": "- See [how to config](https://devlake.apache.org/docs/DORA) this dashboard\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, webhook, etc. \n - `Pull Requests` from GitHub PRs, GitLab MRs, BitBucket PRs, Azure DevOps PRs, etc.\n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.\n- You can validate/debug this dashboard with the [DORA validation dashboard](/grafana/d/KGkUnV-Vz/dora-dashboard-validation)\n- DORA benchmarks vary in different years. You can switch the benchmarks to change them.",
"mode": "markdown"
},
"pluginVersion": "9.5.15",
@@ -205,7 +205,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weeks_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weeks_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by months_deployed) as ranks\n\tFROM _monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(months_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_deployment_frequency as (\n\tSELECT \n\t 'Deployment frequency' as metric, \n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per month(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per six months(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month\n),\n\n-- Metric 2: median lead time for changes\n_pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_change_lead_time as (\n\tSELECT \n\t\t'Lead time for changes' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\nFROM _median_change_lead_time\n),\n\n\n-- Metric 3: Median time to restore service \n_incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n),\n\n\n_metric_mttr as (\n\tSELECT \n\t\t'Time to restore service' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\t\tEND \n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_median_mttr\n),\n\n-- Metric 4: change failure rate\n_deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n),\n\n_metric_cfr as (\n\tSELECT\n\t\t'Change failure rate' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_change_failure_rate\n),\n\n_final_results as (\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m1.metric as _metric, m1.value FROM dora_benchmarks db\n\tleft join _metric_deployment_frequency m1 on db.metric = m1.metric\n\tWHERE m1.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m2.metric as _metric, m2.value FROM dora_benchmarks db\n\tleft join _metric_change_lead_time m2 on db.metric = m2.metric\n\tWHERE m2.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m3.metric as _metric, m3.value FROM dora_benchmarks db\n\tleft join _metric_mttr m3 on db.metric = m3.metric\n\tWHERE m3.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m4.metric as _metric, m4.value FROM dora_benchmarks db\n\tleft join _metric_cfr m4 on db.metric = m4.metric\n\tWHERE m4.metric is not null and db.benchmarks = ('$benchmarks')\n)\n\n\nSELECT \n\tmetric,\n\tcase when low = value then low else null end as low,\n\tcase when medium = value then medium else null end as medium,\n\tcase when high = value then high else null end as high,\n\tcase when elite = value then elite else null end as elite\nFROM _final_results\nORDER BY id",
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weekly_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_days_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,\n\t\t COUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_days_six_months_deploy AS (\n SELECT\n month,\n SUM(days_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS days_deployed_per_six_months,\n COUNT(months_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS months_deployed_count,\n ROW_NUMBER() OVER (\n PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6\n ORDER BY month DESC\n ) AS rn\n FROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weekly_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_days_per_six_months_deploy_by_filter AS (\nSELECT\n month,\n days_deployed_per_six_months,\n months_deployed_count\nFROM _days_six_months_deploy\nWHERE rn%6 = 1\n),\n\n\n_median_number_of_deployment_days_per_six_months_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks\n\tFROM _days_per_six_months_deploy_by_filter\n),\n\n_median_number_of_deployment_days_per_six_months as(\n\tSELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected\n\tFROM _median_number_of_deployment_days_per_six_months_ranks\n\tWHERE ranks >= 0.5\n),\n\n_metric_deployment_frequency as (\n\tSELECT \n\t 'Deployment frequency' as metric, \n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 and is_collected != NULL THEN 'Fewer than once per month(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per day and once per month(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL THEN 'Fewer than once per six months(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months\n),\n\n-- Metric 2: median lead time for changes\n_pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_change_lead_time as (\n\tSELECT \n\t\t'Lead time for changes' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\nFROM _median_change_lead_time\n),\n\n\n-- Metric 3: Median time to restore service \n_incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n),\n\n\n_metric_mttr as (\n\tSELECT \n\t\t'Time to restore service' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\t\tEND \n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_median_mttr\n),\n\n-- Metric 4: change failure rate\n_deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n),\n\n_metric_cfr as (\n\tSELECT\n\t\t'Change failure rate' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_change_failure_rate\n),\n\n_final_results as (\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m1.metric as _metric, m1.value FROM dora_benchmarks db\n\tleft join _metric_deployment_frequency m1 on db.metric = m1.metric\n\tWHERE m1.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m2.metric as _metric, m2.value FROM dora_benchmarks db\n\tleft join _metric_change_lead_time m2 on db.metric = m2.metric\n\tWHERE m2.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m3.metric as _metric, m3.value FROM dora_benchmarks db\n\tleft join _metric_mttr m3 on db.metric = m3.metric\n\tWHERE m3.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m4.metric as _metric, m4.value FROM dora_benchmarks db\n\tleft join _metric_cfr m4 on db.metric = m4.metric\n\tWHERE m4.metric is not null and db.benchmarks = ('$benchmarks')\n)\n\n\nSELECT \n\tmetric,\n\tcase when low = value then low else null end as low,\n\tcase when medium = value then medium else null end as medium,\n\tcase when high = value then high else null end as high,\n\tcase when elite = value then elite else null end as elite\nFROM _final_results\nORDER BY id",
"refId": "A",
"select": [
[
@@ -343,7 +343,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weeks_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weeks_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by months_deployed) as ranks\n\tFROM _monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(months_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and per week(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and per month(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per month(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t \tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per six months(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month\n",
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weekly_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_days_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,\n\t\t COUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_days_six_months_deploy AS (\n SELECT\n month,\n SUM(days_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS days_deployed_per_six_months,\n COUNT(months_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS months_deployed_count,\n ROW_NUMBER() OVER (\n PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6\n ORDER BY month DESC\n ) AS rn\n FROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weekly_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_days_per_six_months_deploy_by_filter AS (\nSELECT\n month,\n days_deployed_per_six_months,\n months_deployed_count\nFROM _days_six_months_deploy\nWHERE rn%6 = 1\n),\n\n\n_median_number_of_deployment_days_per_six_months_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks\n\tFROM _days_per_six_months_deploy_by_filter\n),\n\n_median_number_of_deployment_days_per_six_months as(\n\tSELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected\n\tFROM _median_number_of_deployment_days_per_six_months_ranks\n\tWHERE ranks >= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 and is_collected != NULL THEN 'Fewer than once per month(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t \tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per day and once per month(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL THEN 'Fewer than once per six months(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months\n",
"refId": "A",
"select": [
[
@@ -481,7 +481,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests in a DevLake project.\"\n\t\t\t\tEND\n WHEN ('$benchmarks') = '2021 report' THEN\n\t\t CASE\n\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_change_lead_time\nFROM _median_change_lead_time",
+ "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n WHEN ('$benchmarks') = '2021 report' THEN\n\t\t CASE\n\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_change_lead_time\nFROM _median_change_lead_time",
"refId": "A",
"select": [
[
@@ -623,7 +623,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\tEND \n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n \t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_time_to_resolve\nFROM \n\t_median_mttr",
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\tEND \n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n \t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_time_to_resolve\nFROM \n\t_median_mttr",
"refId": "A",
"select": [
[
@@ -761,7 +761,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS change_failure_rate\nFROM \n\t_change_failure_rate",
+ "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS change_failure_rate\nFROM \n\t_change_failure_rate",
"refId": "A",
"select": [
[
@@ -1345,6 +1345,6 @@
"timezone": "",
"title": "DORA",
"uid": "qNo8_0M4z",
- "version": 37,
+ "version": 22,
"weekStart": ""
}
\ No newline at end of file
diff --git a/grafana/dashboards/DORAByTeam.json b/grafana/dashboards/DORAByTeam.json
index 7f49456..c5f2c0c 100644
--- a/grafana/dashboards/DORAByTeam.json
+++ b/grafana/dashboards/DORAByTeam.json
@@ -24,7 +24,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 36,
+ "id": 8,
"links": [],
"liveNow": false,
"panels": [
@@ -34,7 +34,7 @@
"uid": "grafana"
},
"gridPos": {
- "h": 6,
+ "h": 7,
"w": 24,
"x": 0,
"y": 0
@@ -47,7 +47,7 @@
"showLineNumbers": false,
"showMiniMap": false
},
- "content": "- See [how to config](https://devlake.apache.org/docs/DORA) this dashboard\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, webhook, etc. \n - `Pull Requests` from GitHub PRs, GitLab MRs, BitBucket PRs, Azure DevOps PRs, etc.\n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.\n- You can validate/debug this dashboard with the [DORA validation dashboard](/grafana/d/KGkUnV-Vz/dora-dashboard-validation) \n- You also need to do [team configuration](https://devlake.apache.org/docs/Configuration/TeamConfiguration) to use this dashboard. \n \nHow does this work? \n- Gets the author of the specific commit and then navigates to the team the user belongs to. \n- Gets the team from the PR's author. \n- Gets the team from the commit author.",
+ "content": "- See [how to config](https://devlake.apache.org/docs/DORA) this dashboard\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, webhook, etc. \n - `Pull Requests` from GitHub PRs, GitLab MRs, BitBucket PRs, Azure DevOps PRs, etc.\n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.\n- You can validate/debug this dashboard with the [DORA validation dashboard](/grafana/d/KGkUnV-Vz/dora-dashboard-validation) \n- You also need to do [team configuration](https://devlake.apache.org/docs/Configuration/TeamConfiguration) to use this dashboard. \n- DORA benchmarks vary in different years. You can switch the benchmarks to change them.\n \nHow does this work? \n- Gets the author of the specific commit and then navigates to the team the user belongs to. \n- Gets the team from the PR's author. \n- Gets the team from the commit author.",
"mode": "markdown"
},
"pluginVersion": "9.5.15",
@@ -183,7 +183,7 @@
"h": 6,
"w": 24,
"x": 0,
- "y": 6
+ "y": 7
},
"id": 8,
"links": [],
@@ -208,7 +208,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN commits c on cdc.commit_sha = c.sha\n\tjoin user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weeks_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weeks_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by months_deployed) as ranks\n\tFROM _monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(months_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_deployment_frequency as (\n\tSELECT \n\t 'Deployment frequency' as metric, \n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per month(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per six months(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month\n),\n\n-- Metric 2: median lead time for changes\n_pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin user_accounts ua on pr.author_id = ua.account_id\n \tjoin users u on ua.user_id = u.id\n \tjoin team_users tu on u.id = tu.user_id\n \tjoin teams t on tu.team_id = t.id\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_change_lead_time as (\n\tSELECT \n\t\t'Lead time for changes' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\nFROM _median_change_lead_time\n),\n\n-- Metric 3: Median time to restore service \n_incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\t join user_accounts ua on i.assignee_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_mttr as (\n\tSELECT \n\t\t'Time to restore service' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\t\tEND \n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_median_mttr\n),\n\n-- Metric 4: change failure rate\n_deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t JOIN commits c on cdc.commit_sha = c.sha\n\t join user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n),\n\n_metric_cfr as (\n\tSELECT\n\t\t'Change failure rate' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_change_failure_rate\n),\n\n\n_final_results as (\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m1.metric as _metric, m1.value FROM dora_benchmarks db\n\tleft join _metric_deployment_frequency m1 on db.metric = m1.metric\n\tWHERE m1.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m2.metric as _metric, m2.value FROM dora_benchmarks db\n\tleft join _metric_change_lead_time m2 on db.metric = m2.metric\n\tWHERE m2.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m3.metric as _metric, m3.value FROM dora_benchmarks db\n\tleft join _metric_mttr m3 on db.metric = m3.metric\n\tWHERE m3.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m4.metric as _metric, m4.value FROM dora_benchmarks db\n\tleft join _metric_cfr m4 on db.metric = m4.metric\n\tWHERE m4.metric is not null and db.benchmarks = ('$benchmarks')\n)\n\n\nSELECT \n\tmetric,\n\tcase when low = value then low else null end as low,\n\tcase when medium = value then medium else null end as medium,\n\tcase when high = value then high else null end as high,\n\tcase when elite = value then elite else null end as elite\nFROM _final_results\nORDER BY id",
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN commits c on cdc.commit_sha = c.sha\n\tjoin user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weekly_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_days_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,\n\t\t COUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_days_six_months_deploy AS (\n SELECT\n month,\n SUM(days_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS days_deployed_per_six_months,\n COUNT(months_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS months_deployed_count,\n ROW_NUMBER() OVER (\n PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6\n ORDER BY month DESC\n ) AS rn\n FROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weekly_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_days_per_six_months_deploy_by_filter AS (\nSELECT\n month,\n days_deployed_per_six_months,\n months_deployed_count\nFROM _days_six_months_deploy\nWHERE rn%6 = 1\n),\n\n\n_median_number_of_deployment_days_per_six_months_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks\n\tFROM _days_per_six_months_deploy_by_filter\n),\n\n_median_number_of_deployment_days_per_six_months as(\n\tSELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected\n\tFROM _median_number_of_deployment_days_per_six_months_ranks\n\tWHERE ranks >= 0.5\n),\n\n_metric_deployment_frequency as (\n\tSELECT \n\t 'Deployment frequency' as metric, \n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 and is_collected != NULL THEN 'Fewer than once per month(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per day and once per month(high)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\t\tWHEN median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL THEN 'Fewer than once per six months(low)'\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months\n),\n\n-- Metric 2: median lead time for changes\n_pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin user_accounts ua on pr.author_id = ua.account_id\n \tjoin users u on ua.user_id = u.id\n \tjoin team_users tu on u.id = tu.user_id\n \tjoin teams t on tu.team_id = t.id\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_change_lead_time as (\n\tSELECT \n\t\t'Lead time for changes' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\nFROM _median_change_lead_time\n),\n\n-- Metric 3: Median time to restore service \n_incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\t join user_accounts ua on i.assignee_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n),\n\n_metric_mttr as (\n\tSELECT \n\t\t'Time to restore service' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\t\tEND \n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE\n\t\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_median_mttr\n),\n\n-- Metric 4: change failure rate\n_deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t JOIN commits c on cdc.commit_sha = c.sha\n\t join user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n),\n\n_metric_cfr as (\n\tSELECT\n\t\t'Change failure rate' as metric,\n\t\tCASE\n\t\t\tWHEN ('$benchmarks') = '2023 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\t\tEND\n\t\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\t\tCASE \n\t\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\t\tEND\n\t\t\tELSE 'Invalid Benchmarks'\n\t\tEND AS value\n\tFROM \n\t\t_change_failure_rate\n),\n\n\n_final_results as (\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m1.metric as _metric, m1.value FROM dora_benchmarks db\n\tleft join _metric_deployment_frequency m1 on db.metric = m1.metric\n\tWHERE m1.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m2.metric as _metric, m2.value FROM dora_benchmarks db\n\tleft join _metric_change_lead_time m2 on db.metric = m2.metric\n\tWHERE m2.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m3.metric as _metric, m3.value FROM dora_benchmarks db\n\tleft join _metric_mttr m3 on db.metric = m3.metric\n\tWHERE m3.metric is not null and db.benchmarks = ('$benchmarks')\n\t\n\tunion \n\t\n\tSELECT distinct db.id,db.metric,db.low,db.medium,db.high,db.elite,m4.metric as _metric, m4.value FROM dora_benchmarks db\n\tleft join _metric_cfr m4 on db.metric = m4.metric\n\tWHERE m4.metric is not null and db.benchmarks = ('$benchmarks')\n)\n\n\nSELECT \n\tmetric,\n\tcase when low = value then low else null end as low,\n\tcase when medium = value then medium else null end as medium,\n\tcase when high = value then high else null end as high,\n\tcase when elite = value then elite else null end as elite\nFROM _final_results\nORDER BY id",
"refId": "A",
"sql": {
"columns": [
@@ -297,7 +297,7 @@
"h": 5,
"w": 6,
"x": 0,
- "y": 12
+ "y": 13
},
"id": 11,
"links": [],
@@ -326,7 +326,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN commits c on cdc.commit_sha = c.sha\n\tjoin user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weeks_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weeks_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by months_deployed) as ranks\n\tFROM _monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(months_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per month(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t \tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 THEN 'Fewer than once per six months(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month\n\n",
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN commits c on cdc.commit_sha = c.sha\n\tjoin user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weekly_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_days_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,\n\t\t COUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_days_six_months_deploy AS (\n SELECT\n month,\n SUM(days_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS days_deployed_per_six_months,\n COUNT(months_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS months_deployed_count,\n ROW_NUMBER() OVER (\n PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6\n ORDER BY month DESC\n ) AS rn\n FROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weekly_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_days_per_six_months_deploy_by_filter AS (\nSELECT\n month,\n days_deployed_per_six_months,\n months_deployed_count\nFROM _days_six_months_deploy\nWHERE rn%6 = 1\n),\n\n\n_median_number_of_deployment_days_per_six_months_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks\n\tFROM _days_per_six_months_deploy_by_filter\n),\n\n_median_number_of_deployment_days_per_six_months as(\n\tSELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected\n\tFROM _median_number_of_deployment_days_per_six_months_ranks\n\tWHERE ranks >= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 and is_collected != NULL THEN 'Fewer than once per month(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t \tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per day and once per month(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL THEN 'Fewer than once per six months(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months",
"refId": "A",
"select": [
[
@@ -435,7 +435,7 @@
"h": 5,
"w": 6,
"x": 6,
- "y": 12
+ "y": 13
},
"id": 12,
"links": [],
@@ -462,7 +462,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin user_accounts ua on pr.author_id = ua.account_id\n \tjoin users u on ua.user_id = u.id\n \tjoin team_users tu on u.id = tu.user_id\n \tjoin teams t on tu.team_id = t.id\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests in a DevLake project.\"\n\t\t\t\tEND\n WHEN ('$benchmarks') = '2021 report' THEN\n\t\t CASE\n\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_change_lead_time\nFROM _median_change_lead_time",
+ "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin user_accounts ua on pr.author_id = ua.account_id\n \tjoin users u on ua.user_id = u.id\n \tjoin team_users tu on u.id = tu.user_id\n \tjoin teams t on tu.team_id = t.id\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n WHEN ('$benchmarks') = '2021 report' THEN\n\t\t CASE\n\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_change_lead_time\nFROM _median_change_lead_time",
"refId": "A",
"sql": {
"columns": [
@@ -555,7 +555,7 @@
"h": 5,
"w": 6,
"x": 12,
- "y": 12
+ "y": 13
},
"id": 13,
"links": [],
@@ -582,7 +582,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\t join user_accounts ua on i.assignee_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n\t\t\t\tEND \n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents in a DevLake project.\"\n \t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_time_to_resolve\nFROM \n\t_median_mttr",
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\t join user_accounts ua on i.assignee_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\tWHERE\n\t t.name in (${team:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\tEND \n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n \t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_time_to_resolve\nFROM \n\t_median_mttr",
"refId": "A",
"sql": {
"columns": [
@@ -671,7 +671,7 @@
"h": 5,
"w": 6,
"x": 18,
- "y": 12
+ "y": 13
},
"id": 14,
"links": [],
@@ -698,7 +698,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t JOIN commits c on cdc.commit_sha = c.sha\n\t join user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents in a DevLake project.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS change_failure_rate\nFROM \n\t_change_failure_rate",
+ "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t JOIN commits c on cdc.commit_sha = c.sha\n\t join user_accounts ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on u.id = tu.user_id\n join teams t on tu.team_id = t.id\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tt.name in (${team:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS change_failure_rate\nFROM \n\t_change_failure_rate",
"refId": "A",
"sql": {
"columns": [
@@ -771,7 +771,7 @@
"h": 8,
"w": 12,
"x": 0,
- "y": 17
+ "y": 18
},
"id": 2,
"links": [],
@@ -874,7 +874,7 @@
"h": 8,
"w": 12,
"x": 12,
- "y": 17
+ "y": 18
},
"id": 6,
"links": [],
@@ -998,7 +998,7 @@
"h": 8,
"w": 12,
"x": 0,
- "y": 25
+ "y": 26
},
"id": 9,
"links": [],
@@ -1120,7 +1120,7 @@
"h": 8,
"w": 12,
"x": 12,
- "y": 25
+ "y": 26
},
"id": 5,
"links": [],
@@ -1239,6 +1239,6 @@
"timezone": "",
"title": "DORA (by Team)",
"uid": "66YkL8y4z",
- "version": 6,
+ "version": 11,
"weekStart": ""
}
\ No newline at end of file
diff --git a/grafana/dashboards/DORADebug.json b/grafana/dashboards/DORADebug.json
index b27f13e..ba5224c 100644
--- a/grafana/dashboards/DORADebug.json
+++ b/grafana/dashboards/DORADebug.json
@@ -18,7 +18,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 5,
+ "id": 24,
"links": [],
"liveNow": false,
"panels": [
@@ -44,7 +44,7 @@
"content": "This dashboard is designed to validate the [DORA dashboard](/grafana/d/qNo8_0M4z/dora?orgId=1).",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -105,7 +105,7 @@
"content": "- See the definition and calculation logic of [Deployment Frequency](https://devlake.apache.org/docs/Metrics/DeploymentFrequency)\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines, or Webhook, etc. \n- Transformation Required: Define `deployments` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -191,7 +191,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -237,24 +237,43 @@
"mappings": [
{
"options": {
- "Between once per month and once every 6 months": {
- "color": "yellow",
- "index": 1
- },
- "Between once per week and once per month": {
- "color": "green",
- "index": 2
- },
- "Fewer than once per six months": {
- "color": "red",
- "index": 0
- },
- "On-demand": {
+ "pattern": ".*elite.*",
+ "result": {
"color": "purple",
+ "index": 0
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
"index": 3
}
},
- "type": "value"
+ "type": "regex"
}
],
"thresholds": {
@@ -292,16 +311,17 @@
"text": {},
"textMode": "auto"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in ($project)\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weeks_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, 0)) as months_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weeks_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by months_deployed) as ranks\n\tFROM _monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(months_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n\tCASE \n\t\tWHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand'\n\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month'\n\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months'\n\t\tELSE 'Fewer than once per six months' END AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month\n",
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- construct the last few calendar months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3\n\t\t\tUNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6\n\t\t\tUNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9\n\t\t) U\n\tWHERE\n\t\t(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()\n),\n\n_production_deployment_days as(\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(DATE(cdc.finished_date)) as day\n\tFROM cicd_deployment_commits cdc\n\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_days_weekly_deploy as(\n-- calculate the number of deployment days every week\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as weeks_deployed,\n\t\t\tCOUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY week\n\t),\n\n_days_monthly_deploy as(\n-- calculate the number of deployment days every month\n\tSELECT\n\t\t\tdate(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,\n\t\t\tMAX(if(_production_deployment_days.day is not null, 1, null)) as months_deployed,\n\t\t COUNT(distinct _production_deployment_days.day) as days_deployed\n\tFROM \n\t\tlast_few_calendar_months\n\t\tLEFT JOIN _production_deployment_days ON _production_deployment_days.day = last_few_calendar_months.day\n\tGROUP BY month\n\t),\n\n_days_six_months_deploy AS (\n SELECT\n month,\n SUM(days_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS days_deployed_per_six_months,\n COUNT(months_deployed) OVER (\n ORDER BY month\n ROWS BETWEEN 5 PRECEDING AND CURRENT ROW\n ) AS months_deployed_count,\n ROW_NUMBER() OVER (\n PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6\n ORDER BY month DESC\n ) AS rn\n FROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_week_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_weekly_deploy\n),\n\n_median_number_of_deployment_days_per_week as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_week\n\tFROM _median_number_of_deployment_days_per_week_ranks\n\tWHERE ranks <= 0.5\n),\n\n_median_number_of_deployment_days_per_month_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed) as ranks\n\tFROM _days_monthly_deploy\n),\n\n_median_number_of_deployment_days_per_month as(\n\tSELECT max(days_deployed) as median_number_of_deployment_days_per_month\n\tFROM _median_number_of_deployment_days_per_month_ranks\n\tWHERE ranks <= 0.5\n),\n\n_days_per_six_months_deploy_by_filter AS (\nSELECT\n month,\n days_deployed_per_six_months,\n months_deployed_count\nFROM _days_six_months_deploy\nWHERE rn%6 = 1\n),\n\n\n_median_number_of_deployment_days_per_six_months_ranks as(\n\tSELECT *, percent_rank() over(order by days_deployed_per_six_months) as ranks\n\tFROM _days_per_six_months_deploy_by_filter\n),\n\n_median_number_of_deployment_days_per_six_months as(\n\tSELECT min(days_deployed_per_six_months) as median_number_of_deployment_days_per_six_months, min(months_deployed_count) as is_collected\n\tFROM _median_number_of_deployment_days_per_six_months_ranks\n\tWHERE ranks >= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per day and once per week(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per week and once per month(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month < 1 and is_collected != NULL THEN 'Fewer than once per month(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t \tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN median_number_of_deployment_days_per_week >= 7 THEN 'On-demand(elite)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per day and once per month(high)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per month and once every 6 months(medium)'\n\t\t\t\tWHEN median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL THEN 'Fewer than once per six months(low)'\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments.\" END\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS 'Deployment Frequency'\nFROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month, _median_number_of_deployment_days_per_six_months",
"refId": "A",
"select": [
[
@@ -313,6 +333,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "_devlake_tasks",
"timeColumn": "created_at",
"timeColumnType": "timestamp",
@@ -401,7 +438,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -628,7 +665,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -746,7 +783,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -864,7 +901,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -947,7 +984,7 @@
"content": "- See the definition and calculation logic of [Median Lead Time for Changes](https://devlake.apache.org/docs/Metrics/LeadTimeForChanges)\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines, or Webhook, etc. \n - `Pull Requests` from GitHub PRs, GitLab MRs, BitBucket PRs, Azure DevOps PRs, etc.\n- Transformation Required: Define `deployments` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.\n- Validatation Steps below:\n - Step 1 - check the data integrity of PRs in table `pull_requests`\n - Step 2 - check the data integrity of deployment_commit in table `cicd_deployment_commits`\n - Step 3 - check if a deployment_commit is associated with the correct PR in table `project_pr_metrics`\n - Step 4 - check if metrics like PR Coding/Pickup/Review/Deploy/cycle time are correct in table `project_pr_metrics`\n - Step 5 - check if the median lead time for changes in each month is correct",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -1010,7 +1047,7 @@
},
"showHeader": true
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1056,24 +1093,43 @@
"mappings": [
{
"options": {
- "Between one week and six months": {
- "color": "yellow",
- "index": 1
- },
- "Less than one hour": {
+ "pattern": ".*elite.*",
+ "result": {
"color": "purple",
- "index": 3
- },
- "Less than one week": {
- "color": "green",
- "index": 2
- },
- "More than six months": {
- "color": "red",
"index": 0
}
},
- "type": "value"
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
+ "index": 3
+ }
+ },
+ "type": "regex"
}
],
"thresholds": {
@@ -1111,16 +1167,17 @@
"text": {},
"textMode": "auto"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in ($project) \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN median_change_lead_time < 60 then \"Less than one hour\"\n WHEN median_change_lead_time < 7 * 24 * 60 then \"Less than one week\"\n WHEN median_change_lead_time < 180 * 24 * 60 then \"Between one week and six months\"\n WHEN median_change_lead_time >= 180 * 24 * 60 then \"More than six months\"\n ELSE \"N/A.Please check if you have collected deployments/incidents.\"\n END as median_change_lead_time\nFROM _median_change_lead_time",
+ "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'') \n\t\tand pr.merged_date is not null\n\t\tand ppm.pr_cycle_time is not null\n\t\tand $__timeFilter(cdc.finished_date)\n),\n\n_median_change_lead_time_ranks as(\n\tSELECT *, percent_rank() over(order by pr_cycle_time) as ranks\n\tFROM _pr_stats\n),\n\n_median_change_lead_time as(\n-- use median PR cycle time as the median change lead time\n\tSELECT max(pr_cycle_time) as median_change_lead_time\n\tFROM _median_change_lead_time_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_change_lead_time < 24 * 60 THEN \"Less than one day(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Between one day and one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 30 * 24 * 60 THEN \"Between one week and one month(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 30 * 24 * 60 THEN \"More than one month(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n WHEN ('$benchmarks') = '2021 report' THEN\n\t\t CASE\n\t\t\t\tWHEN median_change_lead_time < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_change_lead_time < 7 * 24 * 60 THEN \"Less than one week(high)\"\n\t\t\t\tWHEN median_change_lead_time < 180 * 24 * 60 THEN \"Between one week and six months(medium)\"\n\t\t\t\tWHEN median_change_lead_time >= 180 * 24 * 60 THEN \"More than six months(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/pull_requests.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_change_lead_time\nFROM _median_change_lead_time",
"refId": "A",
"select": [
[
@@ -1132,6 +1189,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "_devlake_tasks",
"timeColumn": "created_at",
"timeColumnType": "timestamp",
@@ -1179,8 +1253,7 @@
"mode": "absolute",
"steps": [
{
- "color": "red",
- "value": null
+ "color": "red"
}
]
}
@@ -1216,8 +1289,7 @@
"mode": "absolute",
"steps": [
{
- "color": "red",
- "value": null
+ "color": "red"
},
{
"color": "green",
@@ -1265,7 +1337,7 @@
},
"showHeader": true
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1335,8 +1407,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -1434,8 +1505,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -1463,7 +1533,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1573,8 +1643,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -1602,7 +1671,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1670,8 +1739,7 @@
"mode": "absolute",
"steps": [
{
- "color": "red",
- "value": null
+ "color": "red"
}
]
}
@@ -1699,8 +1767,7 @@
"mode": "absolute",
"steps": [
{
- "color": "red",
- "value": null
+ "color": "red"
},
{
"color": "green",
@@ -1762,7 +1829,7 @@
},
"showHeader": true
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1834,8 +1901,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -1863,7 +1929,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -1911,8 +1977,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
},
{
"color": "red",
@@ -1944,7 +2009,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2022,8 +2087,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2051,7 +2115,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2129,8 +2193,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2158,7 +2221,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2236,8 +2299,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2265,7 +2327,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2343,8 +2405,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2372,7 +2433,7 @@
"showThresholdMarkers": true,
"text": {}
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2428,8 +2489,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2510,7 +2570,7 @@
},
"showHeader": true
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2593,7 +2653,7 @@
"content": "- See the definition and calculation logic of [Median Time to Restore Service](https://devlake.apache.org/docs/Metrics/MTTR)\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines, or Webhook, etc. \n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -2627,7 +2687,7 @@
"content": "- See the definition and calculation logic of [Change Failure Rate](https://devlake.apache.org/docs/Metrics/CFR)\n- Data Sources Required: \n - `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines, or Webhook, etc. \n - `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty Incidents, etc. \n- Transformation Required: Define `deployments` and `incidents` in [data transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional) while configuring the blueprint of a project.",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -2675,8 +2735,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2704,7 +2763,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2750,32 +2809,50 @@
"mappings": [
{
"options": {
- "Between one day and one week": {
- "color": "yellow",
- "index": 1
- },
- "Less than one day": {
- "color": "green",
- "index": 2
- },
- "Less than one hour": {
+ "pattern": ".*elite.*",
+ "result": {
"color": "purple",
- "index": 3
- },
- "More than one week": {
- "color": "red",
"index": 0
}
},
- "type": "value"
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
+ "index": 3
+ }
+ },
+ "type": "regex"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
},
{
"color": "red",
@@ -2809,16 +2886,17 @@
"text": {},
"textMode": "auto"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n\tcase\n\t\tWHEN median_time_to_resolve < 60 then \"Less than one hour\"\n WHEN median_time_to_resolve < 24 * 60 then \"Less than one day\"\n WHEN median_time_to_resolve < 7 * 24 * 60 then \"Between one day and one week\"\n WHEN median_time_to_resolve >= 7 * 24 * 60 then \"More than one week\"\n ELSE \"N/A.Please check if you have collected deployments/incidents.\"\n END as median_time_to_resolve\nFROM \n\t_median_mttr",
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in (${project:sqlstring}+'')\n\t\tand i.type = 'INCIDENT'\n\t\tand $__timeFilter(i.created_date)\n),\n\n_median_mttr_ranks as(\n\tSELECT *, percent_rank() over(order by lead_time_minutes) as ranks\n\tFROM _incidents\n),\n\n_median_mttr as(\n\tSELECT max(lead_time_minutes) as median_time_to_resolve\n\tFROM _median_mttr_ranks\n\tWHERE ranks <= 0.5\n)\n\nSELECT \n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n\t\t\t\tEND \n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE\n\t\t\t\tWHEN median_time_to_resolve < 60 THEN \"Less than one hour(elite)\"\n\t\t\t\tWHEN median_time_to_resolve < 24 * 60 THEN \"Less than one day(high)\"\n\t\t\t\tWHEN median_time_to_resolve < 7 * 24 * 60 THEN \"Between one day and one week(medium)\"\n\t\t\t\tWHEN median_time_to_resolve >= 7 * 24 * 60 THEN \"More than one week(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected incidents.\"\n \t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS median_time_to_resolve\nFROM \n\t_median_mttr",
"refId": "A",
"select": [
[
@@ -2830,6 +2908,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "_devlake_tasks",
"timeColumn": "created_at",
"timeColumnType": "timestamp",
@@ -2880,8 +2975,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -2909,7 +3003,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -2967,7 +3061,7 @@
"content": "<img src = 'https://devlake.apache.org/assets/images/cfr-definition-94d92cc75f857f183443ad5390d31d65.png' />\n\nIn this case:\n\n- Deployment-1 maps to Incident-1\n- Deployment-3 maps to Incident-2 and Incident-3\n- Deployment-2,4,5 doesn't map to any Incident",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -3015,8 +3109,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
},
{
"color": "red",
@@ -3120,32 +3213,50 @@
"mappings": [
{
"options": {
- "0-15%": {
+ "pattern": ".*elite.*",
+ "result": {
"color": "purple",
- "index": 3
- },
- "16%-20%": {
- "color": "green",
- "index": 2
- },
- "21%-30%": {
- "color": "yellow",
- "index": 1
- },
- "> 30%": {
- "color": "red",
"index": 0
}
},
- "type": "value"
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
+ "index": 3
+ }
+ },
+ "type": "regex"
}
],
"thresholds": {
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -3175,16 +3286,17 @@
"text": {},
"textMode": "auto"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"format": "table",
"group": [],
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in ($project)\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n\tcase \n\t\twhen change_failure_rate <= .15 then \"0-15%\"\n\t\twhen change_failure_rate <= .20 then \"16%-20%\"\n\t\twhen change_failure_rate <= .30 then \"21%-30%\"\n\t\telse \"> 30%\" \n\tend as change_failure_rate\nFROM \n\t_change_failure_rate",
+ "rawSql": "-- Metric 4: change failure rate\nwith _deployments as (\n-- 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.\n\tSELECT\n\t\tcdc.cicd_deployment_id as deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM \n\t\tcicd_deployment_commits cdc\n\t\tJOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n\tWHERE\n\t\tpm.project_name in (${project:sqlstring}+'')\n\t\tand cdc.result = 'SUCCESS'\n\t\tand cdc.environment = 'PRODUCTION'\n\tGROUP BY 1\n\tHAVING $__timeFilter(max(cdc.finished_date))\n),\n\n_failure_caused_by_deployments as (\n-- calculate the number of incidents caused by each deployment\n\tSELECT\n\t\td.deployment_id,\n\t\td.deployment_finished_date,\n\t\tcount(distinct case when i.type = 'INCIDENT' then d.deployment_id else null end) as has_incident\n\tFROM\n\t\t_deployments d\n\t\tleft join project_issue_metrics pim on d.deployment_id = pim.deployment_id\n\t\tleft join issues i on pim.id = i.id\n\tGROUP BY 1,2\n),\n\n_change_failure_rate as (\n\tSELECT \n\t\tcase \n\t\t\twhen count(deployment_id) is null then null\n\t\t\telse sum(has_incident)/count(deployment_id) end as change_failure_rate\n\tFROM\n\t\t_failure_caused_by_deployments\n)\n\nSELECT\n CASE\n WHEN ('$benchmarks') = '2023 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= 5 THEN \"0-5%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .10 THEN \"5%-10%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"10%-15%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .15 THEN \"> 15%(low)\"\n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tWHEN ('$benchmarks') = '2021 report' THEN\n\t\t\tCASE \n\t\t\t\tWHEN change_failure_rate <= .15 THEN \"0-15%(elite)\"\n\t\t\t\tWHEN change_failure_rate <= .20 THEN \"16%-20%(high)\"\n\t\t\t\tWHEN change_failure_rate <= .30 THEN \"21%-30%(medium)\"\n\t\t\t\tWHEN change_failure_rate > .30 THEN \"> 30%(low)\" \n\t\t\t\tELSE \"N/A. Please check if you have collected deployments/incidents.\"\n\t\t\t\tEND\n\t\tELSE 'Invalid Benchmarks'\n\tEND AS change_failure_rate\nFROM \n\t_change_failure_rate",
"refId": "A",
"select": [
[
@@ -3196,6 +3308,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "_devlake_tasks",
"timeColumn": "created_at",
"timeColumnType": "timestamp",
@@ -3247,8 +3376,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -3276,7 +3404,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -3348,8 +3476,7 @@
"mode": "absolute",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
}
@@ -3377,7 +3504,7 @@
"showHeader": true,
"sortBy": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": "mysql",
@@ -3467,8 +3594,7 @@
"mode": "percentage",
"steps": [
{
- "color": "green",
- "value": null
+ "color": "green"
}
]
},
@@ -3594,8 +3720,8 @@
{
"current": {
"selected": false,
- "text": "https://github.com/apache/incubator-devlake-website/pull/121",
- "value": "github:GithubPullRequest:1:1000234032"
+ "text": "https://github.com/apache/incubator-devlake/pull/5799",
+ "value": "github:GithubPullRequest:1:1460714136"
},
"datasource": "mysql",
"definition": "select concat(Url, '--', id) from pull_requests",
@@ -3611,6 +3737,27 @@
"skipUrlSync": false,
"sort": 0,
"type": "query"
+ },
+ {
+ "current": {
+ "selected": false,
+ "text": "2023 report",
+ "value": "2023 report"
+ },
+ "datasource": "mysql",
+ "definition": "select benchmarks from dora_benchmarks",
+ "hide": 0,
+ "includeAll": false,
+ "label": "Benchmarks",
+ "multi": false,
+ "name": "benchmarks",
+ "options": [],
+ "query": "select benchmarks from dora_benchmarks",
+ "refresh": 1,
+ "regex": "",
+ "skipUrlSync": false,
+ "sort": 0,
+ "type": "query"
}
]
},
@@ -3622,6 +3769,6 @@
"timezone": "",
"title": "DORA Validation",
"uid": "KGkUnV-Vz",
- "version": 2,
+ "version": 4,
"weekStart": ""
}
\ No newline at end of file
