This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch update-dora-doc2
in repository https://gitbox.apache.org/repos/asf/incubator-devlake-website.git
The following commit(s) were added to refs/heads/update-dora-doc2 by this push:
new 22b77c6555 docs: add failed-deployment-recovery-time
22b77c6555 is described below
commit 22b77c6555b3b639ed17a0fbc4f2c887318892cf
Author: abeizn <[email protected]>
AuthorDate: Fri Mar 15 12:43:48 2024 +0800
docs: add failed-deployment-recovery-time
---
docs/Metrics/CFR.md | 52 +++++++++++++++------
docs/Metrics/DeploymentFrequency.md | 26 +++++------
docs/Metrics/LeadTimeForChanges.md | 28 +++++------
docs/Metrics/MTTR.md | 46 +++++++++---------
static/img/Metrics/cfr-text.jpeg | Bin 34089 -> 0 bytes
static/img/Metrics/deployment-frequency-text.jpeg | Bin 45416 -> 0 bytes
static/img/Metrics/lead-time-for-changes-text.jpeg | Bin 20549 -> 0 bytes
static/img/Metrics/mttr-text.jpeg | Bin 47884 -> 0 bytes
8 files changed, 88 insertions(+), 64 deletions(-)
diff --git a/docs/Metrics/CFR.md b/docs/Metrics/CFR.md
index 915961b707..a0f10d2cec 100644
--- a/docs/Metrics/CFR.md
+++ b/docs/Metrics/CFR.md
@@ -118,10 +118,10 @@ FROM
If you want to measure in which category your team falls, run the following
SQL in Grafana.
-
+
```
--- Metric 4: change failure rate
+-- Metric 3: change failure rate
with _deployments as (
-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.
SELECT
@@ -131,7 +131,7 @@ with _deployments as (
cicd_deployment_commits cdc
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and
pm.`table` = 'cicd_scopes'
WHERE
- pm.project_name in (${project:sqlstring}+'')
+ pm.project_name in (${project})
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -158,30 +158,52 @@ _change_failure_rate as (
else sum(has_incident)/count(deployment_id) end as
change_failure_rate
FROM
_failure_caused_by_deployments
+),
+
+_is_collected_data as(
+ SELECT
+ CASE
+ WHEN COUNT(i.id) = 0 AND COUNT(cdc.id) = 0 THEN 'No All'
+ WHEN COUNT(i.id) = 0 THEN 'No Incidents'
+ WHEN COUNT(cdc.id) = 0 THEN 'No Deployments'
+ END AS is_collected
+FROM
+ (SELECT 1) AS dummy
+LEFT JOIN
+ issues i ON i.type = 'INCIDENT'
+LEFT JOIN
+ cicd_deployment_commits cdc ON 1=1
)
+
SELECT
CASE
- WHEN ('$benchmarks') = '2023 report' THEN
+ WHEN ('$dora_report') = '2023' THEN
CASE
- WHEN change_failure_rate <= 5 THEN "0-5%(elite)"
- WHEN change_failure_rate <= .10 THEN
"5%-10%(high)"
- WHEN change_failure_rate <= .15 THEN
"10%-15%(medium)"
- WHEN change_failure_rate > .15 THEN "> 15%(low)"
+ WHEN is_collected = "No All" THEN "N/A. Please
check if you have collected deployments/incidents."
+ WHEN is_collected = "No Incidents" THEN "N/A.
Please check if you have collected incidents."
+ WHEN is_collected = "No Deployments" THEN
"N/A. Please check if you have collected deployments."
+ WHEN change_failure_rate <= .05 THEN
CONCAT(round(change_failure_rate*100,1), "%(elite)")
+ WHEN change_failure_rate <= .10 THEN
CONCAT(round(change_failure_rate*100,1), "%(high)")
+ WHEN change_failure_rate <= .15 THEN
CONCAT(round(change_failure_rate*100,1), "%(medium)")
+ WHEN change_failure_rate > .15 THEN
CONCAT(round(change_failure_rate*100,1), "%(low)")
ELSE "N/A. Please check if you have collected
deployments/incidents."
END
- WHEN ('$benchmarks') = '2021 report' THEN
+ WHEN ('$dora_report') = '2021' THEN
CASE
- WHEN change_failure_rate <= .15 THEN
"0-15%(elite)"
- WHEN change_failure_rate <= .20 THEN
"16%-20%(high)"
- WHEN change_failure_rate <= .30 THEN
"21%-30%(medium)"
- WHEN change_failure_rate > .30 THEN ">
30%(low)"
+ WHEN is_collected = "No All" THEN "N/A. Please check
if you have collected deployments/incidents."
+ WHEN is_collected = "No Incidents" THEN "N/A.
Please check if you have collected incidents."
+ WHEN is_collected = "No Deployments" THEN
"N/A. Please check if you have collected deployments."
+ WHEN change_failure_rate <= .15 THEN
CONCAT(round(change_failure_rate*100,1), "%(elite)")
+ WHEN change_failure_rate <= .20 THEN
CONCAT(round(change_failure_rate*100,1), "%(high)")
+ WHEN change_failure_rate <= .30 THEN
CONCAT(round(change_failure_rate*100,1), "%(medium)")
+ WHEN change_failure_rate > .30 THEN
CONCAT(round(change_failure_rate*100,1), "%(low)")
ELSE "N/A. Please check if you have collected
deployments/incidents."
END
- ELSE 'Invalid Benchmarks'
+ ELSE 'Invalid dora report'
END AS change_failure_rate
FROM
- _change_failure_rate
+ _change_failure_rate, _is_collected_data
```
## How to improve?
diff --git a/docs/Metrics/DeploymentFrequency.md
b/docs/Metrics/DeploymentFrequency.md
index 780f0916b8..5f6f233c95 100644
--- a/docs/Metrics/DeploymentFrequency.md
+++ b/docs/Metrics/DeploymentFrequency.md
@@ -103,7 +103,7 @@ FROM
If you want to measure in which category your team falls as in the picture
shown below, run the following SQL in Grafana. Unlike monthly deployments which
are based on the number of deployments, the metric below is based on
`deployment days`.
-
+
```
-- Metric 1: Deployment Frequency
@@ -133,7 +133,7 @@ _production_deployment_days as(
FROM cicd_deployment_commits cdc
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table`
= 'cicd_scopes'
WHERE
- pm.project_name in (${project:sqlstring}+'')
+ pm.project_name in (${project})
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -226,21 +226,21 @@ _median_number_of_deployment_days_per_six_months as(
SELECT
CASE
- WHEN ('$benchmarks') = '2023 report' THEN
+ WHEN ('$dora_report') = '2023' THEN
CASE
- WHEN median_number_of_deployment_days_per_week
>= 7 THEN 'On-demand(elite)'
- WHEN median_number_of_deployment_days_per_week
>= 1 THEN 'Between once per day and once per week(high)'
- WHEN median_number_of_deployment_days_per_month
>= 1 THEN 'Between once per week and once per month(medium)'
- WHEN median_number_of_deployment_days_per_month
< 1 and is_collected != NULL THEN 'Fewer than once per month(low)'
+ WHEN median_number_of_deployment_days_per_week
>= 7 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days
per week(elite)')
+ WHEN median_number_of_deployment_days_per_week
>= 1 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days
per week(high)')
+ WHEN median_number_of_deployment_days_per_month
>= 1 THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days
per month(medium)')
+ WHEN median_number_of_deployment_days_per_month
< 1 and is_collected is not null THEN
CONCAT(median_number_of_deployment_days_per_month, ' deployment days per
month(low)')
ELSE "N/A. Please check if you have collected
deployments." END
- WHEN ('$benchmarks') = '2021 report' THEN
+ WHEN ('$dora_report') = '2021' THEN
CASE
- WHEN median_number_of_deployment_days_per_week
>= 7 THEN 'On-demand(elite)'
- WHEN median_number_of_deployment_days_per_month
>= 1 THEN 'Between once per day and once per month(high)'
- WHEN
median_number_of_deployment_days_per_six_months >= 1 THEN 'Between once per
month and once every 6 months(medium)'
- WHEN
median_number_of_deployment_days_per_six_months < 1 and is_collected != NULL
THEN 'Fewer than once per six months(low)'
+ WHEN median_number_of_deployment_days_per_week
>= 7 THEN CONCAT(median_number_of_deployment_days_per_week, ' deployment days
per week(elite)')
+ WHEN median_number_of_deployment_days_per_month
>= 1 THEN CONCAT(median_number_of_deployment_days_per_month, ' deployment days
per month(high)')
+ WHEN
median_number_of_deployment_days_persix_months >= 1 THEN
CONCAT(median_number_of_deployment_days_per_six_months, ' deployment days per
six months(medium)')
+ WHEN
median_number_of_deployment_days_per_six_months < 1 and is_collected is not
null THEN CONCAT(median_number_of_deployment_days_per_six_months, ' deployment
days per six months(low)')
ELSE "N/A. Please check if you have collected
deployments." END
- ELSE 'Invalid Benchmarks'
+ ELSE 'Invalid dora report'
END AS 'Deployment Frequency'
FROM _median_number_of_deployment_days_per_week,
_median_number_of_deployment_days_per_month,
_median_number_of_deployment_days_per_six_months
```
diff --git a/docs/Metrics/LeadTimeForChanges.md
b/docs/Metrics/LeadTimeForChanges.md
index 84ff0d0ade..f9ae70910b 100644
--- a/docs/Metrics/LeadTimeForChanges.md
+++ b/docs/Metrics/LeadTimeForChanges.md
@@ -114,7 +114,7 @@ FROM
If you want to measure in which category your team falls as in the picture
shown below, run the following SQL in Grafana.
-
+
```
-- Metric 2: median lead time for changes
@@ -129,7 +129,7 @@ with _pr_stats as (
join project_mapping pm on pr.base_repo_id = pm.row_id and
pm.`table` = 'repos'
join cicd_deployment_commits cdc on ppm.deployment_commit_id =
cdc.id
WHERE
- pm.project_name in (${project:sqlstring}+'')
+ pm.project_name in (${project})
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
@@ -149,23 +149,23 @@ _median_change_lead_time as(
SELECT
CASE
- WHEN ('$benchmarks') = '2023 report' THEN
+ WHEN ('$dora_report') = '2023' THEN
CASE
- WHEN median_change_lead_time < 24 * 60 THEN
"Less than one day(elite)"
- WHEN median_change_lead_time < 7 * 24 * 60 THEN
"Between one day and one week(high)"
- WHEN median_change_lead_time < 30 * 24 * 60
THEN "Between one week and one month(medium)"
- WHEN median_change_lead_time >= 30 * 24 * 60
THEN "More than one month(low)"
+ WHEN median_change_lead_time < 24 * 60 THEN
CONCAT(round(median_change_lead_time/60,1), "(elite)")
+ WHEN median_change_lead_time < 7 * 24 * 60 THEN
CONCAT(round(median_change_lead_time/60,1), "(high)")
+ WHEN median_change_lead_time < 30 * 24 * 60
THEN CONCAT(round(median_change_lead_time/60,1), "(medium)")
+ WHEN median_change_lead_time >= 30 * 24 * 60
THEN CONCAT(round(median_change_lead_time/60,1), "(low)")
ELSE "N/A. Please check if you have collected
deployments/pull_requests."
END
- WHEN ('$benchmarks') = '2021 report' THEN
+ WHEN ('$dora_report') = '2021' THEN
CASE
- WHEN median_change_lead_time < 60 THEN "Less
than one hour(elite)"
- WHEN median_change_lead_time < 7 * 24 * 60 THEN
"Less than one week(high)"
- WHEN median_change_lead_time < 180 * 24 * 60
THEN "Between one week and six months(medium)"
- WHEN median_change_lead_time >= 180 * 24 * 60
THEN "More than six months(low)"
- ELSE "N/A. Please check if you have collected
deployments/incidents."
+ WHEN median_change_lead_time < 60 THEN
CONCAT(round(median_change_lead_time/60,1), "(elite)")
+ WHEN median_change_lead_time < 7 * 24 * 60 THEN
CONCAT(round(median_change_lead_time/60,1), "(high)")
+ WHEN median_change_lead_time < 180 * 24 * 60
THEN CONCAT(round(median_change_lead_time/60,1), "(medium)")
+ WHEN median_change_lead_time >= 180 * 24 * 60
THEN CONCAT(round(median_change_lead_time/60,1), "(low)")
+ ELSE "N/A. Please check if you have collected
deployments/pull_requests."
END
- ELSE 'Invalid Benchmarks'
+ ELSE 'Invalid dora report'
END AS median_change_lead_time
FROM _median_change_lead_time
```
diff --git a/docs/Metrics/MTTR.md b/docs/Metrics/MTTR.md
index a5f83407e1..3511e10e28 100644
--- a/docs/Metrics/MTTR.md
+++ b/docs/Metrics/MTTR.md
@@ -106,10 +106,11 @@ FROM
If you want to measure in which category your team falls into as in the
picture shown below, run the following SQL in Grafana.
-
+
```
--- Metric 3: Median time to restore service
+-- ***** 2023 report ***** --
+-- Metric 4: Failed deployment recovery time
with _incidents as (
-- get the incidents created within the selected time period in the top-right
corner
SELECT
@@ -121,7 +122,7 @@ with _incidents as (
join boards b on bi.board_id = b.id
join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'
WHERE
- pm.project_name in (${project:sqlstring}+'')
+ pm.project_name in (${project})
and i.type = 'INCIDENT'
and $__timeFilter(i.created_date)
),
@@ -135,30 +136,31 @@ _median_mttr as(
SELECT max(lead_time_minutes) as median_time_to_resolve
FROM _median_mttr_ranks
WHERE ranks <= 0.5
-)
+),
-SELECT
- CASE
- WHEN ('$benchmarks') = '2023 report' THEN
- CASE
- WHEN median_time_to_resolve < 60 THEN "Less
than one hour(elite)"
- WHEN median_time_to_resolve < 24 * 60 THEN
"Less than one day(high)"
- WHEN median_time_to_resolve < 7 * 24 * 60 THEN
"Between one day and one week(medium)"
- WHEN median_time_to_resolve >= 7 * 24 * 60 THEN
"More than one week(low)"
- ELSE "N/A. Please check if you have collected
incidents."
- END
- WHEN ('$benchmarks') = '2021 report' THEN
+_metric_mttr_2021_report as(
+ SELECT
+ CASE
+ WHEN ('$dora_report') = '2021' THEN
CASE
- WHEN median_time_to_resolve < 60 THEN "Less
than one hour(elite)"
- WHEN median_time_to_resolve < 24 * 60 THEN
"Less than one day(high)"
- WHEN median_time_to_resolve < 7 * 24 * 60 THEN
"Between one day and one week(medium)"
- WHEN median_time_to_resolve >= 7 * 24 * 60 THEN
"More than one week(low)"
+ WHEN median_time_to_resolve < 60 THEN
CONCAT(round(median_time_to_resolve/60,1), "(elite)")
+ WHEN median_time_to_resolve < 24 * 60 THEN
CONCAT(round(median_time_to_resolve/60,1), "(high)")
+ WHEN median_time_to_resolve < 7 * 24 * 60 THEN
CONCAT(round(median_time_to_resolve/60,1), "(medium)")
+ WHEN median_time_to_resolve >= 7 * 24 * 60 THEN
CONCAT(round(median_time_to_resolve/60,1), "(low)")
ELSE "N/A. Please check if you have collected
incidents."
- END
- ELSE 'Invalid Benchmarks'
+ END
END AS median_time_to_resolve
+ FROM
+ _median_mttr
+)
+
+SELECT
+ median_time_to_resolve AS median_time_to_resolve
FROM
- _median_mttr
+ _metric_mttr_2021_report
+WHERE
+ ('$dora_report') = '2021'
+
```
## How to improve?
diff --git a/static/img/Metrics/cfr-text.jpeg b/static/img/Metrics/cfr-text.jpeg
deleted file mode 100644
index b0556cc5de..0000000000
Binary files a/static/img/Metrics/cfr-text.jpeg and /dev/null differ
diff --git a/static/img/Metrics/deployment-frequency-text.jpeg
b/static/img/Metrics/deployment-frequency-text.jpeg
deleted file mode 100644
index ac0c725737..0000000000
Binary files a/static/img/Metrics/deployment-frequency-text.jpeg and /dev/null
differ
diff --git a/static/img/Metrics/lead-time-for-changes-text.jpeg
b/static/img/Metrics/lead-time-for-changes-text.jpeg
deleted file mode 100644
index 5a9e979aee..0000000000
Binary files a/static/img/Metrics/lead-time-for-changes-text.jpeg and /dev/null
differ
diff --git a/static/img/Metrics/mttr-text.jpeg
b/static/img/Metrics/mttr-text.jpeg
deleted file mode 100644
index 1733622f94..0000000000
Binary files a/static/img/Metrics/mttr-text.jpeg and /dev/null differ