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.
 
-![](/img/Metrics/cfr-text.jpeg)
+![](/img/Metrics/cfr-text.png)
 
 ```
--- 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`.
 
-![](/img/Metrics/deployment-frequency-text.jpeg)
+![](/img/Metrics/deployment-frequency-text.png)
 
 ```
 -- 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.
 
-![](/img/Metrics/lead-time-for-changes-text.jpeg)
+![](/img/Metrics/lead-time-for-changes-text.png)
 
 ```
 -- 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.
 
-![](/img/Metrics/mttr-text.jpeg)
+![](/img/Metrics/mttr-text.png)
 
 ```
--- 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

Reply via email to