This is an automated email from the ASF dual-hosted git repository.
yumeng 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 7c54fb592 docs: add SQL to build metrics (#330)
7c54fb592 is described below
commit 7c54fb59272b8bd0da73a63b1e0da03413168ce6
Author: Louis.z <[email protected]>
AuthorDate: Tue Nov 22 10:53:58 2022 +0800
docs: add SQL to build metrics (#330)
Co-authored-by: Startrekzky <[email protected]>
---
docs/Metrics/BuildCount.md | 50 ++++++++++++++--
docs/Metrics/BuildDuration.md | 50 ++++++++++++++--
docs/Metrics/BuildSuccessRate.md | 67 ++++++++++++++++++++--
docs/Metrics/CFR.md | 2 +-
docs/Metrics/CodingTime.md | 2 +-
docs/Metrics/CycleTime.md | 2 +-
docs/Metrics/DeployTime.md | 2 +-
docs/Metrics/DeploymentFrequency.md | 2 +-
docs/Metrics/LeadTimeForChanges.md | 2 +-
docs/Metrics/MTTR.md | 2 +-
docs/Metrics/MergeRate.md | 2 +-
docs/Metrics/PRCount.md | 2 +-
docs/Metrics/PRSize.md | 2 +-
docs/Metrics/PickupTime.md | 2 +-
docs/Metrics/ReviewDepth.md | 2 +-
docs/Metrics/ReviewTime.md | 2 +-
docs/Metrics/TimeToMerge.md | 2 +-
static/img/Metrics/build-count-monthly.png | Bin 0 -> 28563 bytes
static/img/Metrics/build-duration-monthly.png | Bin 0 -> 25785 bytes
static/img/Metrics/build-result-distribution.png | Bin 0 -> 52889 bytes
static/img/Metrics/build-success-rate-monthly.png | Bin 0 -> 33502 bytes
21 files changed, 166 insertions(+), 29 deletions(-)
diff --git a/docs/Metrics/BuildCount.md b/docs/Metrics/BuildCount.md
index 50352bbc1..ff91addda 100644
--- a/docs/Metrics/BuildCount.md
+++ b/docs/Metrics/BuildCount.md
@@ -2,7 +2,7 @@
title: "Build Count"
description: >
Build Count
-sidebar_position: 15
+sidebar_position: 23
---
## What is this metric?
@@ -13,20 +13,60 @@ The number of successful builds.
2. Identify excellent/to-be-improved practices that impact the build, and
drive the team to precipitate reusable tools and mechanisms to build
infrastructure for fast and high-frequency delivery
## Which dashboard(s) does it exist in
-- Jenkins
+-
[Jenkins](https://grafana-lake.demo.devlake.io/grafana/d/W8AiDFQnk/jenkins?orgId=1)
## How is it calculated?
-This metric is calculated by counting the number of successful CI
builds/pipelines/runs in the given data range.
+This metric is calculated by counting the number of successful cicd_pipelines,
such as Jenkins builds, GitLab pipelines and GitHub workflow runs in the given
data range.
<b>Data Sources Required</b>
-This metric relies on CI builds/pipelines/runs collected from Jenkins, GitLab
or GitHub.
+This metric relies on Jenkins builds, GitLab pipelines or GitHub workflow runs.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the total number of successful CI builds
**finished** in the given time range.
+```
+SELECT
+ count(*)
+FROM
+ cicd_pipelines
+WHERE
+ result = 'SUCCESS'
+ and $__timeFilter(finished_date)
+ORDER BY 1
+```
+
+If you want to measure the monthly trend of the `successful build count` in
the screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+WITH _builds as(
+ SELECT
+ DATE_ADD(date(finished_date), INTERVAL -DAYOFMONTH(date(finished_date))+1
DAY) as time,
+ count(*) as build_count
+ FROM
+ cicd_pipelines
+ WHERE
+ result = "SUCCESS"
+ and $__timeFilter(finished_date)
+ -- the following condition will remove the month with incomplete data
+ and finished_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ GROUP BY 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ build_count as "Build Count"
+FROM _builds
+ORDER BY time
+```
+
## How to improve?
1. From the project dimension, compare the number of builds and success rate
by combining the project phase and the complexity of tasks.
2. From the time dimension, analyze the trend of the number of builds and
success rate to see if it has improved over time.
diff --git a/docs/Metrics/BuildDuration.md b/docs/Metrics/BuildDuration.md
index 1aa95385f..b43197250 100644
--- a/docs/Metrics/BuildDuration.md
+++ b/docs/Metrics/BuildDuration.md
@@ -2,7 +2,7 @@
title: "Build Duration"
description: >
Build Duration
-sidebar_position: 16
+sidebar_position: 24
---
## What is this metric?
@@ -13,20 +13,60 @@ The duration of successful builds.
2. Identify excellent/to-be-improved practices that impact the build, and
drive the team to precipitate reusable tools and mechanisms to build
infrastructure for fast and high-frequency delivery
## Which dashboard(s) does it exist in
-- Jenkins
+-
[Jenkins](https://grafana-lake.demo.devlake.io/grafana/d/W8AiDFQnk/jenkins?orgId=1)
## How is it calculated?
-This metric is calculated by getting the duration of successful CI
builds/pipelines/runs in the given data range.
+This metric is calculated by getting the duration of successful
cicd_pipelines, such as Jenkins builds, GitLab pipelines and GitHub workflow
runs in the given data range.
<b>Data Sources Required</b>
-This metric relies on CI builds/pipelines/runs collected from Jenkins, GitLab
or GitHub.
+This metric relies on Jenkins builds, GitLab pipelines or GitHub workflow runs.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the mean duration of successful CI builds
**finished** in the given time range.
+```
+SELECT
+ avg(duration_sec/60) as duration_in_minutes
+FROM cicd_pipelines
+WHERE
+ result = 'SUCCESS'
+ and $__timeFilter(finished_date)
+ORDER BY 1
+```
+
+If you want to measure the `mean duration of builds` in the screenshot below,
please run the following SQL in Grafana.
+
+
+
+```
+WITH _builds as(
+ SELECT
+ DATE_ADD(date(finished_date), INTERVAL -DAYOFMONTH(date(finished_date))+1
DAY) as time,
+ avg(duration_sec) as mean_duration_sec
+ FROM
+ cicd_pipelines
+ WHERE
+ $__timeFilter(finished_date)
+ and id like "%jenkins%"
+ and name in ($job_id)
+ -- the following condition will remove the month with incomplete data
+ and finished_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+ GROUP BY 1
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ mean_duration_sec/60 as mean_duration_minutes
+FROM _builds
+ORDER BY time
+```
+
## How to improve?
1. From the project dimension, compare the number of builds and success rate
by combining the project phase and the complexity of tasks.
2. From the time dimension, analyze the trend of the number of builds and
success rate to see if it has improved over time.
diff --git a/docs/Metrics/BuildSuccessRate.md b/docs/Metrics/BuildSuccessRate.md
index 401086d96..e9ea2e2d7 100644
--- a/docs/Metrics/BuildSuccessRate.md
+++ b/docs/Metrics/BuildSuccessRate.md
@@ -2,7 +2,7 @@
title: "Build Success Rate"
description: >
Build Success Rate
-sidebar_position: 17
+sidebar_position: 25
---
## What is this metric?
@@ -13,20 +13,77 @@ The ratio of successful builds to all builds.
2. Identify excellent/to-be-improved practices that impact the build, and
drive the team to precipitate reusable tools and mechanisms to build
infrastructure for fast and high-frequency delivery
## Which dashboard(s) does it exist in
-- Jenkins
-
+-
[Jenkins](https://grafana-lake.demo.devlake.io/grafana/d/W8AiDFQnk/jenkins?orgId=1)
## How is it calculated?
The number of successful builds divided by the total number of builds in the
given data range.
<b>Data Sources Required</b>
-This metric relies on CI builds/pipelines/runs collected from Jenkins, GitLab
or GitHub.
+This metric relies on Jenkins builds, GitLab pipelines or GitHub workflow runs.
-<b>Transformation Rules Required</b>
+<b>Data Transformation Required</b>
N/A
+<b>SQL Queries</b>
+
+The following SQL shows how to find the success rate of CI builds **finished**
in the given time range.
+```
+SELECT
+ 1.0 * sum(case when result = 'SUCCESS' then 1 else 0 end)/ count(*) as
"Build Success Rate"
+FROM
+ cicd_pipelines
+WHERE
+ $__timeFilter(finished_date)
+ORDER BY 1
+```
+
+If you want to measure the distribution of CI build result like the donut
chart below, please run the following SQL in Grafana.
+
+
+
+```
+SELECT
+ result,
+ count(*) as build_count
+FROM
+ cicd_pipelines
+WHERE
+ $__timeFilter(finished_date)
+ and id like "%jenkins%"
+ and name in ($job_id)
+ -- the following condition will remove the month with incomplete data
+ and finished_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+GROUP BY 1
+ORDER BY 2 DESC
+```
+
+If you want to measure the `mean build success rate per month` in the
screenshot below, please run the following SQL in Grafana.
+
+
+
+```
+WITH _build_success_rate as(
+ SELECT
+ DATE_ADD(date(finished_date), INTERVAL -DAYOFMONTH(date(finished_date))+1
DAY) as time,
+ result
+ FROM
+ cicd_pipelines
+ WHERE
+ $__timeFilter(finished_date)
+ -- the following condition will remove the month with incomplete data
+ and finished_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL
-DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)
+)
+
+SELECT
+ date_format(time,'%M %Y') as month,
+ 1.0 * sum(case when result = 'SUCCESS' then 1 else 0 end)/ count(*) as
"Build Success Rate"
+FROM _build_success_rate
+GROUP BY 1
+ORDER BY 1
+```
+
## How to improve?
1. From the project dimension, compare the number of builds and success rate
by combining the project phase and the complexity of tasks.
2. From the time dimension, analyze the trend of the number of builds and
success rate to see if it has improved over time.
diff --git a/docs/Metrics/CFR.md b/docs/Metrics/CFR.md
index eb2255b70..573c7a0a3 100644
--- a/docs/Metrics/CFR.md
+++ b/docs/Metrics/CFR.md
@@ -2,7 +2,7 @@
title: "DORA - Change Failure Rate"
description: >
DORA - Change Failure Rate
-sidebar_position: 21
+sidebar_position: 29
---
## What is this metric?
diff --git a/docs/Metrics/CodingTime.md b/docs/Metrics/CodingTime.md
index d78847481..d80a0eb2e 100644
--- a/docs/Metrics/CodingTime.md
+++ b/docs/Metrics/CodingTime.md
@@ -2,7 +2,7 @@
title: "PR Coding Time"
description: >
PR Coding Time
-sidebar_position: 2
+sidebar_position: 15
---
## What is this metric?
diff --git a/docs/Metrics/CycleTime.md b/docs/Metrics/CycleTime.md
index bbc98349a..5d9e67666 100644
--- a/docs/Metrics/CycleTime.md
+++ b/docs/Metrics/CycleTime.md
@@ -2,7 +2,7 @@
title: "PR Cycle Time"
description: >
PR Cycle Time
-sidebar_position: 2
+sidebar_position: 14
---
## What is this metric?
diff --git a/docs/Metrics/DeployTime.md b/docs/Metrics/DeployTime.md
index 39c589ad2..f0c96234e 100644
--- a/docs/Metrics/DeployTime.md
+++ b/docs/Metrics/DeployTime.md
@@ -2,7 +2,7 @@
title: "PR Deploy Time"
description: >
PR Deploy Time
-sidebar_position: 2
+sidebar_position: 18
---
## What is this metric?
diff --git a/docs/Metrics/DeploymentFrequency.md
b/docs/Metrics/DeploymentFrequency.md
index 90459adc5..9cd3c6cbc 100644
--- a/docs/Metrics/DeploymentFrequency.md
+++ b/docs/Metrics/DeploymentFrequency.md
@@ -2,7 +2,7 @@
title: "DORA - Deployment Frequency"
description: >
DORA - Deployment Frequency
-sidebar_position: 18
+sidebar_position: 26
---
## What is this metric?
diff --git a/docs/Metrics/LeadTimeForChanges.md
b/docs/Metrics/LeadTimeForChanges.md
index f9a7038f9..efd9fe7c5 100644
--- a/docs/Metrics/LeadTimeForChanges.md
+++ b/docs/Metrics/LeadTimeForChanges.md
@@ -2,7 +2,7 @@
title: "DORA - Lead Time for Changes"
description: >
DORA - Lead Time for Changes
-sidebar_position: 19
+sidebar_position: 27
---
## What is this metric?
diff --git a/docs/Metrics/MTTR.md b/docs/Metrics/MTTR.md
index d2fb407fc..aa5b3e0d1 100644
--- a/docs/Metrics/MTTR.md
+++ b/docs/Metrics/MTTR.md
@@ -2,7 +2,7 @@
title: "DORA - Median Time to Restore Service"
description: >
DORA - Median Time to Restore Service
-sidebar_position: 20
+sidebar_position: 28
---
## What is this metric?
diff --git a/docs/Metrics/MergeRate.md b/docs/Metrics/MergeRate.md
index c8c274338..6ce83bcbb 100644
--- a/docs/Metrics/MergeRate.md
+++ b/docs/Metrics/MergeRate.md
@@ -2,7 +2,7 @@
title: "PR Merge Rate"
description: >
Pull Request Merge Rate
-sidebar_position: 12
+sidebar_position: 20
---
## What is this metric?
diff --git a/docs/Metrics/PRCount.md b/docs/Metrics/PRCount.md
index 4521e7861..65bad0420 100644
--- a/docs/Metrics/PRCount.md
+++ b/docs/Metrics/PRCount.md
@@ -2,7 +2,7 @@
title: "Pull Request Count"
description: >
Pull Request Count
-sidebar_position: 11
+sidebar_position: 13
---
## What is this metric?
diff --git a/docs/Metrics/PRSize.md b/docs/Metrics/PRSize.md
index bf6a87d82..ab75daddb 100644
--- a/docs/Metrics/PRSize.md
+++ b/docs/Metrics/PRSize.md
@@ -2,7 +2,7 @@
title: "PR Size"
description: >
PR Size
-sidebar_position: 2
+sidebar_position: 22
---
## What is this metric?
diff --git a/docs/Metrics/PickupTime.md b/docs/Metrics/PickupTime.md
index 07242ae77..61033a877 100644
--- a/docs/Metrics/PickupTime.md
+++ b/docs/Metrics/PickupTime.md
@@ -2,7 +2,7 @@
title: "PR Pickup Time"
description: >
PR Pickup Time
-sidebar_position: 2
+sidebar_position: 16
---
## What is this metric?
diff --git a/docs/Metrics/ReviewDepth.md b/docs/Metrics/ReviewDepth.md
index 59bcfbe87..b97f0e77f 100644
--- a/docs/Metrics/ReviewDepth.md
+++ b/docs/Metrics/ReviewDepth.md
@@ -2,7 +2,7 @@
title: "PR Review Depth"
description: >
PR Review Depth
-sidebar_position: 2
+sidebar_position: 21
---
## What is this metric?
diff --git a/docs/Metrics/ReviewTime.md b/docs/Metrics/ReviewTime.md
index 8cfe080b0..1b7a310e1 100644
--- a/docs/Metrics/ReviewTime.md
+++ b/docs/Metrics/ReviewTime.md
@@ -2,7 +2,7 @@
title: "PR Review Time"
description: >
PR Review Time
-sidebar_position: 2
+sidebar_position: 17
---
## What is this metric?
diff --git a/docs/Metrics/TimeToMerge.md b/docs/Metrics/TimeToMerge.md
index 04a39225f..f080a8e56 100644
--- a/docs/Metrics/TimeToMerge.md
+++ b/docs/Metrics/TimeToMerge.md
@@ -2,7 +2,7 @@
title: "PR Time To Merge"
description: >
PR Time To Merge
-sidebar_position: 2
+sidebar_position: 19
---
## What is this metric?
diff --git a/static/img/Metrics/build-count-monthly.png
b/static/img/Metrics/build-count-monthly.png
new file mode 100644
index 000000000..e72c3a3b4
Binary files /dev/null and b/static/img/Metrics/build-count-monthly.png differ
diff --git a/static/img/Metrics/build-duration-monthly.png
b/static/img/Metrics/build-duration-monthly.png
new file mode 100644
index 000000000..c0b8b5cd5
Binary files /dev/null and b/static/img/Metrics/build-duration-monthly.png
differ
diff --git a/static/img/Metrics/build-result-distribution.png
b/static/img/Metrics/build-result-distribution.png
new file mode 100644
index 000000000..9ea52cf94
Binary files /dev/null and b/static/img/Metrics/build-result-distribution.png
differ
diff --git a/static/img/Metrics/build-success-rate-monthly.png
b/static/img/Metrics/build-success-rate-monthly.png
new file mode 100644
index 000000000..96a8102ec
Binary files /dev/null and b/static/img/Metrics/build-success-rate-monthly.png
differ
