This is an automated email from the ASF dual-hosted git repository.
zky pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake-website.git
The following commit(s) were added to refs/heads/main by this push:
new 76e80a1098 docs: upgrade dora benchmarks to 2023 version (#712)
76e80a1098 is described below
commit 76e80a1098468408a7c5bd14468ed05252ead160
Author: abeizn <[email protected]>
AuthorDate: Thu Feb 8 21:50:27 2024 +0800
docs: upgrade dora benchmarks to 2023 version (#712)
* docs: upgrade dora benchmarks to 2023 version
* docs: upgrade dora benchmarks to 2023 version
* feat: update dora 2023 benchmarks docs
* docs: update some details
* docs: update v0.21 dora docs
---
docs/Metrics/CFR.md | 74 ++++++++----
docs/Metrics/DeploymentFrequency.md | 132 ++++++++++++++++-----
docs/Metrics/LeadTimeForChanges.md | 60 +++++++---
docs/Metrics/MTTR.md | 66 +++++++----
versioned_docs/version-v0.21/Metrics/CFR.md | 74 ++++++++----
.../version-v0.21/Metrics/DeploymentFrequency.md | 132 ++++++++++++++++-----
.../version-v0.21/Metrics/LeadTimeForChanges.md | 60 +++++++---
versioned_docs/version-v0.21/Metrics/MTTR.md | 66 +++++++----
8 files changed, 486 insertions(+), 178 deletions(-)
diff --git a/docs/Metrics/CFR.md b/docs/Metrics/CFR.md
index 7aa2dd2b7e..915961b707 100644
--- a/docs/Metrics/CFR.md
+++ b/docs/Metrics/CFR.md
@@ -25,16 +25,30 @@ The number of deployments affected by incidents/total
number of deployments. For
When there are multiple deployments triggered by one pipeline, tools like
GitLab and BitBucket will generate more than one deployment. In these cases,
DevLake will consider these deployments as ONE deployment and use the last
deployment's finished date as the deployment finished date.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
change failure rate is `18%` or `40%`. Therefore, DevLake provides its own
benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's change failure rate is between 15% and 64%. Therefore, DevLake
provides its own benchmarks to address this problem:
| Groups | Benchmarks | DevLake Benchmarks |
| ----------------- | ---------- | ------------------ |
-| Elite performers | 0%-15% | 0%-15% |
-| High performers | 16%-30% | 16-20% |
-| Medium performers | 16%-30% | 21%-30% |
-| Low performers | 16%-30% | > 30% |
+| Elite performers | 5% | (0, 5%] |
+| High performers | 10% | (5%, 10%] |
+| Medium performers | 15% | (10%, 15%] |
+| Low performers | 64% | (15%, 100%] |
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
+
+| Groups | Benchmarks | DevLake Benchmarks |
+| ----------------- | ---------- | ------------------ |
+| Elite performers | 0%-15% | (0, 15%] |
+| High performers | 16%-30% | (16%, 20%] |
+| Medium performers | 16%-30% | (21%, 30%] |
+| Low performers | 16%-30% | (30%, 100%] |
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
+
<b>Data Sources Required</b>
@@ -58,11 +72,11 @@ with _deployments as (
SELECT
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date
- FROM
+ 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)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -83,9 +97,9 @@ _failure_caused_by_deployments as (
),
_change_failure_rate_for_each_month as (
- SELECT
+ SELECT
date_format(deployment_finished_date,'%y/%m') as month,
- case
+ case
when count(deployment_id) is null then null
else sum(has_incident)/count(deployment_id) end as
change_failure_rate
FROM
@@ -93,10 +107,10 @@ _change_failure_rate_for_each_month as (
GROUP BY 1
)
-SELECT
+SELECT
cm.month,
cfr.change_failure_rate
-FROM
+FROM
calendar_months cm
LEFT JOIN _change_failure_rate_for_each_month cfr on cm.month =
cfr.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -107,16 +121,17 @@ If you want to measure in which category your team falls,
run the following SQL
```
+-- Metric 4: 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
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date
- FROM
+ FROM
cicd_deployment_commits cdc
- JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and
pm.`table` = 'cicd_scopes'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -137,8 +152,8 @@ _failure_caused_by_deployments as (
),
_change_failure_rate as (
- SELECT
- case
+ SELECT
+ case
when count(deployment_id) is null then null
else sum(has_incident)/count(deployment_id) end as
change_failure_rate
FROM
@@ -146,13 +161,26 @@ _change_failure_rate as (
)
SELECT
- case
- when change_failure_rate <= .15 then "0-15%"
- when change_failure_rate <= .20 then "16%-20%"
- when change_failure_rate <= .30 then "21%-30%"
- else "> 30%"
- end as change_failure_rate
-FROM
+ CASE
+ WHEN ('$benchmarks') = '2023 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/incidents."
+ END
+ WHEN ('$benchmarks') = '2021 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/incidents."
+ END
+ ELSE 'Invalid Benchmarks'
+ END AS change_failure_rate
+FROM
_change_failure_rate
```
diff --git a/docs/Metrics/DeploymentFrequency.md
b/docs/Metrics/DeploymentFrequency.md
index bae538ef0c..780f0916b8 100644
--- a/docs/Metrics/DeploymentFrequency.md
+++ b/docs/Metrics/DeploymentFrequency.md
@@ -25,20 +25,39 @@ Deployment frequency is calculated based on the number of
`deployment days`, not
When there are multiple deployments triggered by one pipeline, tools like
GitLab and BitBucket will generate more than one deployment. In these cases,
DevLake will consider these deployments as ONE deployment and use the last
deployment's finished date as the deployment finished date.
-Below are the benchmarks for different development teams from Google's report.
DevLake uses the same benchmarks.
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's deployment frequency is `between once per week and once per
month`. Therefore, DevLake provides its own benchmarks to address this problem:
+
+
+| Groups | Benchmarks | DevLake
Benchmarks | The Criteria of DevLake Benchmarks
|
+| ----------------- | ---------------------------------------------- |
---------------------------------------------- |
--------------------------------------------------|
+| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 7 |
+| High performers | Between once per day and once per week | Between
once per day and once per week | Median Number of `Deployment Days` per
Week >= 1 |
+| Medium performers | Between once per week and once per month | Between
once per week and once per month | Median Number of `Deployment Days` per
Month >= 1 |
+| Low performers | Between once per week and once per month | Fewer
than once per month | Median Number of `Deployment Days`
per Month < 1 |
+
+<p><i>Source: 2023 Accelerate State of DevOps, Google</i></p>
+
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
| Groups | Benchmarks | DevLake
Benchmarks | The Criteria of DevLake Benchmarks
|
| ----------------- | ---------------------------------------------- |
---------------------------------------------- |
--------------------------------------------------|
-| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 3 |
-| High performers | Between once per week and once per month | Between
once per week and once per month | Median Number of `Deployment Days` per
Week >= 1 |
-| Medium performers | Between once per month and once every 6 months | Between
once per month and once every 6 months | Median Number of `Deployment Days` per
Month >= 1 |
-| Low performers | Fewer than once per six months | Fewer
than once per six months | Median Number of `Deployment Days`
per Month < 1 |
+| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 7 |
+| High performers | Between once per week and once per month | Between
once per day and once per month | Median Number of `Deployment Days` per
Month >= 1 |
+| Medium performers | Between once per month and once every 6 months | Between
once per month and once every 6 months | Median Number of `Deployment Days` per
six Months >= 1 |
+| Low performers | Fewer than once per six months | Fewer
than once per six months | Median Number of `Deployment Days`
per six Months < 1 |
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
+
<b>Data Sources Required</b>
-`Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
Webhook, etc.
+- `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
Webhook, etc.
+
<b>Transformation Rules Required</b>
@@ -54,7 +73,7 @@ DevLake deployments can be found in table
[cicd_deployment_commits](/docs/DataMo
-- Metric 1: Number of deployments per month
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
+ SELECT
date_format(deployment_finished_date,'%y/%m') as month,
count(cicd_deployment_id) as deployment_count
FROM (
@@ -64,7 +83,7 @@ with _deployments 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)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -73,10 +92,10 @@ with _deployments as(
GROUP BY 1
)
-SELECT
- cm.month,
+SELECT
+ cm.month,
case when d.deployment_count is null then 0 else d.deployment_count end
as deployment_count
-FROM
+FROM
calendar_months cm
LEFT JOIN _deployments d on cm.month = d.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -87,6 +106,7 @@ If you want to measure in which category your team falls as
in the picture shown
```
+-- Metric 1: Deployment Frequency
with last_few_calendar_months as(
-- construct the last few calendar months within the selected time period in
the top-right corner
SELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day
@@ -111,40 +131,59 @@ _production_deployment_days as(
cdc.cicd_deployment_id as deployment_id,
max(DATE(cdc.finished_date)) as day
FROM cicd_deployment_commits cdc
- JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table`
= 'cicd_scopes'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
),
-_days_weeks_deploy as(
+_days_weekly_deploy as(
-- calculate the number of deployment days every week
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-WEEKDAY(last_few_calendar_months.day) DAY)) as week,
- MAX(if(_production_deployment_days.day is not null, 1,
0)) as weeks_deployed,
+ MAX(if(_production_deployment_days.day is not null, 1,
null)) as weeks_deployed,
COUNT(distinct _production_deployment_days.day) as
days_deployed
- FROM
+ FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY week
),
-_monthly_deploy as(
+_days_monthly_deploy as(
-- calculate the number of deployment days every month
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-DAY(last_few_calendar_months.day)+1 DAY)) as month,
- MAX(if(_production_deployment_days.day is not null, 1,
0)) as months_deployed
- FROM
+ MAX(if(_production_deployment_days.day is not null, 1,
null)) as months_deployed,
+ COUNT(distinct _production_deployment_days.day) as
days_deployed
+ FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY month
),
+_days_six_months_deploy AS (
+ SELECT
+ month,
+ SUM(days_deployed) OVER (
+ ORDER BY month
+ ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
+ ) AS days_deployed_per_six_months,
+ COUNT(months_deployed) OVER (
+ ORDER BY month
+ ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
+ ) AS months_deployed_count,
+ ROW_NUMBER() OVER (
+ PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6
+ ORDER BY month DESC
+ ) AS rn
+ FROM _days_monthly_deploy
+),
+
_median_number_of_deployment_days_per_week_ranks as(
SELECT *, percent_rank() over(order by days_deployed) as ranks
- FROM _days_weeks_deploy
+ FROM _days_weekly_deploy
),
_median_number_of_deployment_days_per_week as(
@@ -154,23 +193,56 @@ _median_number_of_deployment_days_per_week as(
),
_median_number_of_deployment_days_per_month_ranks as(
- SELECT *, percent_rank() over(order by months_deployed) as ranks
- FROM _monthly_deploy
+ SELECT *, percent_rank() over(order by days_deployed) as ranks
+ FROM _days_monthly_deploy
),
_median_number_of_deployment_days_per_month as(
- SELECT max(months_deployed) as
median_number_of_deployment_days_per_month
+ SELECT max(days_deployed) as median_number_of_deployment_days_per_month
FROM _median_number_of_deployment_days_per_month_ranks
WHERE ranks <= 0.5
-)
+),
+_days_per_six_months_deploy_by_filter AS (
SELECT
- CASE
- WHEN median_number_of_deployment_days_per_week >= 3 THEN
'On-demand'
- WHEN median_number_of_deployment_days_per_week >= 1 THEN
'Between once per week and once per month'
- WHEN median_number_of_deployment_days_per_month >= 1 THEN
'Between once per month and once every 6 months'
- ELSE 'Fewer than once per six months' END AS 'Deployment
Frequency'
-FROM _median_number_of_deployment_days_per_week,
_median_number_of_deployment_days_per_month
+ month,
+ days_deployed_per_six_months,
+ months_deployed_count
+FROM _days_six_months_deploy
+WHERE rn%6 = 1
+),
+
+
+_median_number_of_deployment_days_per_six_months_ranks as(
+ SELECT *, percent_rank() over(order by days_deployed_per_six_months) as
ranks
+ FROM _days_per_six_months_deploy_by_filter
+),
+
+_median_number_of_deployment_days_per_six_months as(
+ SELECT min(days_deployed_per_six_months) as
median_number_of_deployment_days_per_six_months, min(months_deployed_count) as
is_collected
+ FROM _median_number_of_deployment_days_per_six_months_ranks
+ WHERE ranks >= 0.5
+)
+
+SELECT
+ CASE
+ WHEN ('$benchmarks') = '2023 report' 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)'
+ ELSE "N/A. Please check if you have collected
deployments." END
+ WHEN ('$benchmarks') = '2021 report' 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)'
+ ELSE "N/A. Please check if you have collected
deployments." END
+ ELSE 'Invalid Benchmarks'
+ 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
```
## How to improve?
diff --git a/docs/Metrics/LeadTimeForChanges.md
b/docs/Metrics/LeadTimeForChanges.md
index f18ec3963b..84ff0d0ade 100644
--- a/docs/Metrics/LeadTimeForChanges.md
+++ b/docs/Metrics/LeadTimeForChanges.md
@@ -28,7 +28,19 @@ This metric is quite similar to [PR Cycle
Time](PRCycleTime.md). The difference
PR cycle time is pre-calculated by the `dora` plugin during every data
collection. You can find it in `pr_cycle_time` in
[table.project_pr_metrics](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema/#project_pr_metrics)
of DevLake's database.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
median lead time for changes is `between one week and one month`. Therefore,
DevLake provides its own benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's median lead time for changes is `between one week and one
month`. Therefore, DevLake provides its own benchmarks to address this problem:
+
+| Groups | Benchmarks | DevLake Benchmarks
|
+| ----------------- | -------------------------------- |
------------------------------- |
+| Elite performers | Less than one day | Less than one day
|
+| High performers | Between one day and one week | Between one day and
one week |
+| Medium performers | Between one week and one month | Between one week and
one month |
+| Low performers | Between one week and one month | More than one month
|
+
+<p><i>Source: 2023 Accelerate State of DevOps, Google</i></p>
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
| Groups | Benchmarks | DevLake Benchmarks
|
| ----------------- | -------------------------------- |
------------------------------- |
@@ -38,6 +50,9 @@ Below are the benchmarks for different development teams from
Google's report. H
| Low performers | More than six months | More than six months
|
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
<b>Data Sources Required</b>
@@ -68,7 +83,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)
+ pm.project_name in (${project:sqlstring}+'')
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
@@ -86,12 +101,12 @@ _clt as(
group by month
)
-SELECT
+SELECT
cm.month,
- case
- when _clt.median_change_lead_time is null then 0
+ case
+ when _clt.median_change_lead_time is null then 0
else _clt.median_change_lead_time/60 end as
median_change_lead_time_in_hour
-FROM
+FROM
calendar_months cm
LEFT JOIN _clt on cm.month = _clt.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -102,18 +117,19 @@ If you want to measure in which category your team falls
as in the picture shown
```
+-- Metric 2: median lead time for changes
with _pr_stats as (
-- get the cycle time of PRs deployed by the deployments finished in the
selected period
SELECT
distinct pr.id,
ppm.pr_cycle_time
FROM
- pull_requests pr
+ pull_requests pr
join project_pr_metrics ppm on ppm.id = pr.id
- join project_mapping pm on pr.base_repo_id = pm.row_id
+ 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)
+ pm.project_name in (${project:sqlstring}+'')
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
@@ -131,14 +147,26 @@ _median_change_lead_time as(
WHERE ranks <= 0.5
)
-SELECT
+SELECT
CASE
- WHEN median_change_lead_time < 60 then "Less than one hour"
- WHEN median_change_lead_time < 7 * 24 * 60 then "Less than one week"
- WHEN median_change_lead_time < 180 * 24 * 60 then "Between one week and
six months"
- WHEN median_change_lead_time >= 180 * 24 * 60 then "More than six months"
- ELSE "N/A.Please check if you have collected deployments/incidents."
- END as median_change_lead_time
+ WHEN ('$benchmarks') = '2023 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/pull_requests."
+ END
+ WHEN ('$benchmarks') = '2021 report' 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."
+ END
+ ELSE 'Invalid Benchmarks'
+ END AS median_change_lead_time
FROM _median_change_lead_time
```
diff --git a/docs/Metrics/MTTR.md b/docs/Metrics/MTTR.md
index 34829efdc2..a5f83407e1 100644
--- a/docs/Metrics/MTTR.md
+++ b/docs/Metrics/MTTR.md
@@ -23,25 +23,37 @@ MTTR = Total [incident age](./IncidentAge.md) (in
hours)/number of incidents.
If you have three incidents that happened in the given data range, one lasting
1 hour, one lasting 2 hours and one lasting 3 hours. Your MTTR will be: (1 + 2
+ 3) / 3 = 2 hours.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
median time to restore service is `between one week and six months`. Therefore,
DevLake provides its own benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's median time to restore service is `between one week and six
months`. Therefore, DevLake provides its own benchmarks to address this problem:
| Groups | Benchmarks | DevLake Benchmarks
|
| ----------------- | ---------------------------- |
---------------------------- |
| Elite performers | Less than one hour | Less than one hour
|
-| High performers | Less one day | Less than one day
|
+| High performers | Less than one day | Less than one day
|
+| Medium performers | Between one day and one week | Between one day and one
week |
+| Low performers | More than six months | More than one week
|
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
+
+| Groups | Benchmarks | DevLake Benchmarks
|
+| ----------------- | ---------------------------- |
---------------------------- |
+| Elite performers | Less than one hour | Less than one hour
|
+| High performers | Less than one day | Less than one day
|
| Medium performers | Between one day and one week | Between one day and one
week |
| Low performers | More than six months | More than one week
|
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
<b>Data Sources Required</b>
-- `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
or Webhook, etc.
- `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty
Incidents, etc.
<b>Transformation Rules Required</b>
-Define `deployment` and `incident` in [data
transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional)
while configuring the blueprint of a project to let DevLake know what CI/issue
records can be regarded as deployments or incidents.
+Define `incident` in [data
transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional)
while configuring the blueprint of a project to let DevLake know what CI/issue
records can be regarded as deployments or incidents.
<b>SQL Queries</b>
@@ -51,6 +63,7 @@ If you want to measure the monthly trend of the Median Time
to Restore Service a
```
-- Metric 3: median time to restore service - MTTR
+-- Metric 3: median time to restore service - MTTR
with _incidents as (
-- get the number of incidents created each month
SELECT
@@ -63,7 +76,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)
+ pm.project_name in (${project:sqlstring}+'')
and i.type = 'INCIDENT'
and i.lead_time_minutes is not null
),
@@ -80,12 +93,12 @@ _mttr as(
GROUP BY month
)
-SELECT
+SELECT
cm.month,
- case
- when m.median_time_to_resolve is null then 0
+ case
+ when m.median_time_to_resolve is null then 0
else m.median_time_to_resolve/60 end as
median_time_to_resolve_in_hour
-FROM
+FROM
calendar_months cm
LEFT JOIN _mttr m on cm.month = m.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -96,6 +109,7 @@ If you want to measure in which category your team falls
into as in the picture
```
+-- Metric 3: Median time to restore service
with _incidents as (
-- get the incidents created within the selected time period in the top-right
corner
SELECT
@@ -105,9 +119,9 @@ with _incidents as (
issues i
join board_issues bi on i.id = bi.issue_id
join boards b on bi.board_id = b.id
- join project_mapping pm on b.id = pm.row_id
+ join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and i.type = 'INCIDENT'
and $__timeFilter(i.created_date)
),
@@ -123,15 +137,27 @@ _median_mttr as(
WHERE ranks <= 0.5
)
-SELECT
- case
- WHEN median_time_to_resolve < 60 then "Less than one hour"
- WHEN median_time_to_resolve < 24 * 60 then "Less than one Day"
- WHEN median_time_to_resolve < 7 * 24 * 60 then "Between one day and one
week"
- WHEN median_time_to_resolve >= 7 * 24 * 60 then "More than one week"
- ELSE "N/A.Please check if you have collected deployments/incidents."
- END as median_time_to_resolve
-FROM
+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
+ 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
+ ELSE 'Invalid Benchmarks'
+ END AS median_time_to_resolve
+FROM
_median_mttr
```
diff --git a/versioned_docs/version-v0.21/Metrics/CFR.md
b/versioned_docs/version-v0.21/Metrics/CFR.md
index 7aa2dd2b7e..915961b707 100644
--- a/versioned_docs/version-v0.21/Metrics/CFR.md
+++ b/versioned_docs/version-v0.21/Metrics/CFR.md
@@ -25,16 +25,30 @@ The number of deployments affected by incidents/total
number of deployments. For
When there are multiple deployments triggered by one pipeline, tools like
GitLab and BitBucket will generate more than one deployment. In these cases,
DevLake will consider these deployments as ONE deployment and use the last
deployment's finished date as the deployment finished date.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
change failure rate is `18%` or `40%`. Therefore, DevLake provides its own
benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's change failure rate is between 15% and 64%. Therefore, DevLake
provides its own benchmarks to address this problem:
| Groups | Benchmarks | DevLake Benchmarks |
| ----------------- | ---------- | ------------------ |
-| Elite performers | 0%-15% | 0%-15% |
-| High performers | 16%-30% | 16-20% |
-| Medium performers | 16%-30% | 21%-30% |
-| Low performers | 16%-30% | > 30% |
+| Elite performers | 5% | (0, 5%] |
+| High performers | 10% | (5%, 10%] |
+| Medium performers | 15% | (10%, 15%] |
+| Low performers | 64% | (15%, 100%] |
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
+
+| Groups | Benchmarks | DevLake Benchmarks |
+| ----------------- | ---------- | ------------------ |
+| Elite performers | 0%-15% | (0, 15%] |
+| High performers | 16%-30% | (16%, 20%] |
+| Medium performers | 16%-30% | (21%, 30%] |
+| Low performers | 16%-30% | (30%, 100%] |
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
+
<b>Data Sources Required</b>
@@ -58,11 +72,11 @@ with _deployments as (
SELECT
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date
- FROM
+ 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)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -83,9 +97,9 @@ _failure_caused_by_deployments as (
),
_change_failure_rate_for_each_month as (
- SELECT
+ SELECT
date_format(deployment_finished_date,'%y/%m') as month,
- case
+ case
when count(deployment_id) is null then null
else sum(has_incident)/count(deployment_id) end as
change_failure_rate
FROM
@@ -93,10 +107,10 @@ _change_failure_rate_for_each_month as (
GROUP BY 1
)
-SELECT
+SELECT
cm.month,
cfr.change_failure_rate
-FROM
+FROM
calendar_months cm
LEFT JOIN _change_failure_rate_for_each_month cfr on cm.month =
cfr.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -107,16 +121,17 @@ If you want to measure in which category your team falls,
run the following SQL
```
+-- Metric 4: 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
cdc.cicd_deployment_id as deployment_id,
max(cdc.finished_date) as deployment_finished_date
- FROM
+ FROM
cicd_deployment_commits cdc
- JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and
pm.`table` = 'cicd_scopes'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -137,8 +152,8 @@ _failure_caused_by_deployments as (
),
_change_failure_rate as (
- SELECT
- case
+ SELECT
+ case
when count(deployment_id) is null then null
else sum(has_incident)/count(deployment_id) end as
change_failure_rate
FROM
@@ -146,13 +161,26 @@ _change_failure_rate as (
)
SELECT
- case
- when change_failure_rate <= .15 then "0-15%"
- when change_failure_rate <= .20 then "16%-20%"
- when change_failure_rate <= .30 then "21%-30%"
- else "> 30%"
- end as change_failure_rate
-FROM
+ CASE
+ WHEN ('$benchmarks') = '2023 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/incidents."
+ END
+ WHEN ('$benchmarks') = '2021 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/incidents."
+ END
+ ELSE 'Invalid Benchmarks'
+ END AS change_failure_rate
+FROM
_change_failure_rate
```
diff --git a/versioned_docs/version-v0.21/Metrics/DeploymentFrequency.md
b/versioned_docs/version-v0.21/Metrics/DeploymentFrequency.md
index bae538ef0c..780f0916b8 100644
--- a/versioned_docs/version-v0.21/Metrics/DeploymentFrequency.md
+++ b/versioned_docs/version-v0.21/Metrics/DeploymentFrequency.md
@@ -25,20 +25,39 @@ Deployment frequency is calculated based on the number of
`deployment days`, not
When there are multiple deployments triggered by one pipeline, tools like
GitLab and BitBucket will generate more than one deployment. In these cases,
DevLake will consider these deployments as ONE deployment and use the last
deployment's finished date as the deployment finished date.
-Below are the benchmarks for different development teams from Google's report.
DevLake uses the same benchmarks.
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's deployment frequency is `between once per week and once per
month`. Therefore, DevLake provides its own benchmarks to address this problem:
+
+
+| Groups | Benchmarks | DevLake
Benchmarks | The Criteria of DevLake Benchmarks
|
+| ----------------- | ---------------------------------------------- |
---------------------------------------------- |
--------------------------------------------------|
+| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 7 |
+| High performers | Between once per day and once per week | Between
once per day and once per week | Median Number of `Deployment Days` per
Week >= 1 |
+| Medium performers | Between once per week and once per month | Between
once per week and once per month | Median Number of `Deployment Days` per
Month >= 1 |
+| Low performers | Between once per week and once per month | Fewer
than once per month | Median Number of `Deployment Days`
per Month < 1 |
+
+<p><i>Source: 2023 Accelerate State of DevOps, Google</i></p>
+
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
| Groups | Benchmarks | DevLake
Benchmarks | The Criteria of DevLake Benchmarks
|
| ----------------- | ---------------------------------------------- |
---------------------------------------------- |
--------------------------------------------------|
-| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 3 |
-| High performers | Between once per week and once per month | Between
once per week and once per month | Median Number of `Deployment Days` per
Week >= 1 |
-| Medium performers | Between once per month and once every 6 months | Between
once per month and once every 6 months | Median Number of `Deployment Days` per
Month >= 1 |
-| Low performers | Fewer than once per six months | Fewer
than once per six months | Median Number of `Deployment Days`
per Month < 1 |
+| Elite performers | On-demand (multiple deploys per day) |
On-demand | Median Number of `Deployment
Days` per Week >= 7 |
+| High performers | Between once per week and once per month | Between
once per day and once per month | Median Number of `Deployment Days` per
Month >= 1 |
+| Medium performers | Between once per month and once every 6 months | Between
once per month and once every 6 months | Median Number of `Deployment Days` per
six Months >= 1 |
+| Low performers | Fewer than once per six months | Fewer
than once per six months | Median Number of `Deployment Days`
per six Months < 1 |
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
+
<b>Data Sources Required</b>
-`Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
Webhook, etc.
+- `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
Webhook, etc.
+
<b>Transformation Rules Required</b>
@@ -54,7 +73,7 @@ DevLake deployments can be found in table
[cicd_deployment_commits](/docs/DataMo
-- Metric 1: Number of deployments per month
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
+ SELECT
date_format(deployment_finished_date,'%y/%m') as month,
count(cicd_deployment_id) as deployment_count
FROM (
@@ -64,7 +83,7 @@ with _deployments 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)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
@@ -73,10 +92,10 @@ with _deployments as(
GROUP BY 1
)
-SELECT
- cm.month,
+SELECT
+ cm.month,
case when d.deployment_count is null then 0 else d.deployment_count end
as deployment_count
-FROM
+FROM
calendar_months cm
LEFT JOIN _deployments d on cm.month = d.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -87,6 +106,7 @@ If you want to measure in which category your team falls as
in the picture shown
```
+-- Metric 1: Deployment Frequency
with last_few_calendar_months as(
-- construct the last few calendar months within the selected time period in
the top-right corner
SELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day
@@ -111,40 +131,59 @@ _production_deployment_days as(
cdc.cicd_deployment_id as deployment_id,
max(DATE(cdc.finished_date)) as day
FROM cicd_deployment_commits cdc
- JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
+ JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table`
= 'cicd_scopes'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and cdc.result = 'SUCCESS'
and cdc.environment = 'PRODUCTION'
GROUP BY 1
),
-_days_weeks_deploy as(
+_days_weekly_deploy as(
-- calculate the number of deployment days every week
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-WEEKDAY(last_few_calendar_months.day) DAY)) as week,
- MAX(if(_production_deployment_days.day is not null, 1,
0)) as weeks_deployed,
+ MAX(if(_production_deployment_days.day is not null, 1,
null)) as weeks_deployed,
COUNT(distinct _production_deployment_days.day) as
days_deployed
- FROM
+ FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY week
),
-_monthly_deploy as(
+_days_monthly_deploy as(
-- calculate the number of deployment days every month
SELECT
date(DATE_ADD(last_few_calendar_months.day, INTERVAL
-DAY(last_few_calendar_months.day)+1 DAY)) as month,
- MAX(if(_production_deployment_days.day is not null, 1,
0)) as months_deployed
- FROM
+ MAX(if(_production_deployment_days.day is not null, 1,
null)) as months_deployed,
+ COUNT(distinct _production_deployment_days.day) as
days_deployed
+ FROM
last_few_calendar_months
LEFT JOIN _production_deployment_days ON
_production_deployment_days.day = last_few_calendar_months.day
GROUP BY month
),
+_days_six_months_deploy AS (
+ SELECT
+ month,
+ SUM(days_deployed) OVER (
+ ORDER BY month
+ ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
+ ) AS days_deployed_per_six_months,
+ COUNT(months_deployed) OVER (
+ ORDER BY month
+ ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
+ ) AS months_deployed_count,
+ ROW_NUMBER() OVER (
+ PARTITION BY DATE_FORMAT(month, '%Y-%m') DIV 6
+ ORDER BY month DESC
+ ) AS rn
+ FROM _days_monthly_deploy
+),
+
_median_number_of_deployment_days_per_week_ranks as(
SELECT *, percent_rank() over(order by days_deployed) as ranks
- FROM _days_weeks_deploy
+ FROM _days_weekly_deploy
),
_median_number_of_deployment_days_per_week as(
@@ -154,23 +193,56 @@ _median_number_of_deployment_days_per_week as(
),
_median_number_of_deployment_days_per_month_ranks as(
- SELECT *, percent_rank() over(order by months_deployed) as ranks
- FROM _monthly_deploy
+ SELECT *, percent_rank() over(order by days_deployed) as ranks
+ FROM _days_monthly_deploy
),
_median_number_of_deployment_days_per_month as(
- SELECT max(months_deployed) as
median_number_of_deployment_days_per_month
+ SELECT max(days_deployed) as median_number_of_deployment_days_per_month
FROM _median_number_of_deployment_days_per_month_ranks
WHERE ranks <= 0.5
-)
+),
+_days_per_six_months_deploy_by_filter AS (
SELECT
- CASE
- WHEN median_number_of_deployment_days_per_week >= 3 THEN
'On-demand'
- WHEN median_number_of_deployment_days_per_week >= 1 THEN
'Between once per week and once per month'
- WHEN median_number_of_deployment_days_per_month >= 1 THEN
'Between once per month and once every 6 months'
- ELSE 'Fewer than once per six months' END AS 'Deployment
Frequency'
-FROM _median_number_of_deployment_days_per_week,
_median_number_of_deployment_days_per_month
+ month,
+ days_deployed_per_six_months,
+ months_deployed_count
+FROM _days_six_months_deploy
+WHERE rn%6 = 1
+),
+
+
+_median_number_of_deployment_days_per_six_months_ranks as(
+ SELECT *, percent_rank() over(order by days_deployed_per_six_months) as
ranks
+ FROM _days_per_six_months_deploy_by_filter
+),
+
+_median_number_of_deployment_days_per_six_months as(
+ SELECT min(days_deployed_per_six_months) as
median_number_of_deployment_days_per_six_months, min(months_deployed_count) as
is_collected
+ FROM _median_number_of_deployment_days_per_six_months_ranks
+ WHERE ranks >= 0.5
+)
+
+SELECT
+ CASE
+ WHEN ('$benchmarks') = '2023 report' 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)'
+ ELSE "N/A. Please check if you have collected
deployments." END
+ WHEN ('$benchmarks') = '2021 report' 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)'
+ ELSE "N/A. Please check if you have collected
deployments." END
+ ELSE 'Invalid Benchmarks'
+ 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
```
## How to improve?
diff --git a/versioned_docs/version-v0.21/Metrics/LeadTimeForChanges.md
b/versioned_docs/version-v0.21/Metrics/LeadTimeForChanges.md
index f18ec3963b..84ff0d0ade 100644
--- a/versioned_docs/version-v0.21/Metrics/LeadTimeForChanges.md
+++ b/versioned_docs/version-v0.21/Metrics/LeadTimeForChanges.md
@@ -28,7 +28,19 @@ This metric is quite similar to [PR Cycle
Time](PRCycleTime.md). The difference
PR cycle time is pre-calculated by the `dora` plugin during every data
collection. You can find it in `pr_cycle_time` in
[table.project_pr_metrics](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema/#project_pr_metrics)
of DevLake's database.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
median lead time for changes is `between one week and one month`. Therefore,
DevLake provides its own benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's median lead time for changes is `between one week and one
month`. Therefore, DevLake provides its own benchmarks to address this problem:
+
+| Groups | Benchmarks | DevLake Benchmarks
|
+| ----------------- | -------------------------------- |
------------------------------- |
+| Elite performers | Less than one day | Less than one day
|
+| High performers | Between one day and one week | Between one day and
one week |
+| Medium performers | Between one week and one month | Between one week and
one month |
+| Low performers | Between one week and one month | More than one month
|
+
+<p><i>Source: 2023 Accelerate State of DevOps, Google</i></p>
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
| Groups | Benchmarks | DevLake Benchmarks
|
| ----------------- | -------------------------------- |
------------------------------- |
@@ -38,6 +50,9 @@ Below are the benchmarks for different development teams from
Google's report. H
| Low performers | More than six months | More than six months
|
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
<b>Data Sources Required</b>
@@ -68,7 +83,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)
+ pm.project_name in (${project:sqlstring}+'')
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
@@ -86,12 +101,12 @@ _clt as(
group by month
)
-SELECT
+SELECT
cm.month,
- case
- when _clt.median_change_lead_time is null then 0
+ case
+ when _clt.median_change_lead_time is null then 0
else _clt.median_change_lead_time/60 end as
median_change_lead_time_in_hour
-FROM
+FROM
calendar_months cm
LEFT JOIN _clt on cm.month = _clt.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -102,18 +117,19 @@ If you want to measure in which category your team falls
as in the picture shown
```
+-- Metric 2: median lead time for changes
with _pr_stats as (
-- get the cycle time of PRs deployed by the deployments finished in the
selected period
SELECT
distinct pr.id,
ppm.pr_cycle_time
FROM
- pull_requests pr
+ pull_requests pr
join project_pr_metrics ppm on ppm.id = pr.id
- join project_mapping pm on pr.base_repo_id = pm.row_id
+ 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)
+ pm.project_name in (${project:sqlstring}+'')
and pr.merged_date is not null
and ppm.pr_cycle_time is not null
and $__timeFilter(cdc.finished_date)
@@ -131,14 +147,26 @@ _median_change_lead_time as(
WHERE ranks <= 0.5
)
-SELECT
+SELECT
CASE
- WHEN median_change_lead_time < 60 then "Less than one hour"
- WHEN median_change_lead_time < 7 * 24 * 60 then "Less than one week"
- WHEN median_change_lead_time < 180 * 24 * 60 then "Between one week and
six months"
- WHEN median_change_lead_time >= 180 * 24 * 60 then "More than six months"
- ELSE "N/A.Please check if you have collected deployments/incidents."
- END as median_change_lead_time
+ WHEN ('$benchmarks') = '2023 report' 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)"
+ ELSE "N/A. Please check if you have collected
deployments/pull_requests."
+ END
+ WHEN ('$benchmarks') = '2021 report' 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."
+ END
+ ELSE 'Invalid Benchmarks'
+ END AS median_change_lead_time
FROM _median_change_lead_time
```
diff --git a/versioned_docs/version-v0.21/Metrics/MTTR.md
b/versioned_docs/version-v0.21/Metrics/MTTR.md
index 34829efdc2..a5f83407e1 100644
--- a/versioned_docs/version-v0.21/Metrics/MTTR.md
+++ b/versioned_docs/version-v0.21/Metrics/MTTR.md
@@ -23,25 +23,37 @@ MTTR = Total [incident age](./IncidentAge.md) (in
hours)/number of incidents.
If you have three incidents that happened in the given data range, one lasting
1 hour, one lasting 2 hours and one lasting 3 hours. Your MTTR will be: (1 + 2
+ 3) / 3 = 2 hours.
-Below are the benchmarks for different development teams from Google's report.
However, it's difficult to tell which group a team falls into when the team's
median time to restore service is `between one week and six months`. Therefore,
DevLake provides its own benchmarks to address this problem:
+Below are the 2023 DORA benchmarks for different development teams from
Google's report. However, it's difficult to tell which group a team falls into
when the team's median time to restore service is `between one week and six
months`. Therefore, DevLake provides its own benchmarks to address this problem:
| Groups | Benchmarks | DevLake Benchmarks
|
| ----------------- | ---------------------------- |
---------------------------- |
| Elite performers | Less than one hour | Less than one hour
|
-| High performers | Less one day | Less than one day
|
+| High performers | Less than one day | Less than one day
|
+| Medium performers | Between one day and one week | Between one day and one
week |
+| Low performers | More than six months | More than one week
|
+
+<details>
+<summary>Click to expand or collapse 2021 DORA benchmarks</summary>
+
+| Groups | Benchmarks | DevLake Benchmarks
|
+| ----------------- | ---------------------------- |
---------------------------- |
+| Elite performers | Less than one hour | Less than one hour
|
+| High performers | Less than one day | Less than one day
|
| Medium performers | Between one day and one week | Between one day and one
week |
| Low performers | More than six months | More than one week
|
<p><i>Source: 2021 Accelerate State of DevOps, Google</i></p>
+</details>
+<br>
+</br>
<b>Data Sources Required</b>
-- `Deployments` from Jenkins, GitLab CI, GitHub Action, BitBucket Pipelines,
or Webhook, etc.
- `Incidents` from Jira issues, GitHub issues, TAPD issues, PagerDuty
Incidents, etc.
<b>Transformation Rules Required</b>
-Define `deployment` and `incident` in [data
transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional)
while configuring the blueprint of a project to let DevLake know what CI/issue
records can be regarded as deployments or incidents.
+Define `incident` in [data
transformations](https://devlake.apache.org/docs/Configuration/Tutorial#step-3---add-transformations-optional)
while configuring the blueprint of a project to let DevLake know what CI/issue
records can be regarded as deployments or incidents.
<b>SQL Queries</b>
@@ -51,6 +63,7 @@ If you want to measure the monthly trend of the Median Time
to Restore Service a
```
-- Metric 3: median time to restore service - MTTR
+-- Metric 3: median time to restore service - MTTR
with _incidents as (
-- get the number of incidents created each month
SELECT
@@ -63,7 +76,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)
+ pm.project_name in (${project:sqlstring}+'')
and i.type = 'INCIDENT'
and i.lead_time_minutes is not null
),
@@ -80,12 +93,12 @@ _mttr as(
GROUP BY month
)
-SELECT
+SELECT
cm.month,
- case
- when m.median_time_to_resolve is null then 0
+ case
+ when m.median_time_to_resolve is null then 0
else m.median_time_to_resolve/60 end as
median_time_to_resolve_in_hour
-FROM
+FROM
calendar_months cm
LEFT JOIN _mttr m on cm.month = m.month
WHERE $__timeFilter(cm.month_timestamp)
@@ -96,6 +109,7 @@ If you want to measure in which category your team falls
into as in the picture
```
+-- Metric 3: Median time to restore service
with _incidents as (
-- get the incidents created within the selected time period in the top-right
corner
SELECT
@@ -105,9 +119,9 @@ with _incidents as (
issues i
join board_issues bi on i.id = bi.issue_id
join boards b on bi.board_id = b.id
- join project_mapping pm on b.id = pm.row_id
+ join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'
WHERE
- pm.project_name in ($project)
+ pm.project_name in (${project:sqlstring}+'')
and i.type = 'INCIDENT'
and $__timeFilter(i.created_date)
),
@@ -123,15 +137,27 @@ _median_mttr as(
WHERE ranks <= 0.5
)
-SELECT
- case
- WHEN median_time_to_resolve < 60 then "Less than one hour"
- WHEN median_time_to_resolve < 24 * 60 then "Less than one Day"
- WHEN median_time_to_resolve < 7 * 24 * 60 then "Between one day and one
week"
- WHEN median_time_to_resolve >= 7 * 24 * 60 then "More than one week"
- ELSE "N/A.Please check if you have collected deployments/incidents."
- END as median_time_to_resolve
-FROM
+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
+ 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
+ ELSE 'Invalid Benchmarks'
+ END AS median_time_to_resolve
+FROM
_median_mttr
```
