This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
The following commit(s) were added to refs/heads/main by this push:
new 1198e2659 feat(dashboard): use incident tables in dashboards (#7812)
1198e2659 is described below
commit 1198e2659171e09f34da3478a80e49c7d31c53a1
Author: Lynwee <[email protected]>
AuthorDate: Thu Aug 1 11:58:42 2024 +0800
feat(dashboard): use incident tables in dashboards (#7812)
* fix(dora): use table incidents to make connection with deployments
* fix(dora): fix e2e test
* fix(dora): fix unit test
* feat(dashboard): use incident tables in dashboards
---
grafana/dashboards/DORA.json | 12 ++++++------
grafana/dashboards/DORAByTeam.json | 10 +++++-----
grafana/dashboards/DORADebug.json | 12 ++++++------
3 files changed, 17 insertions(+), 17 deletions(-)
diff --git a/grafana/dashboards/DORA.json b/grafana/dashboards/DORA.json
index 5d4191978..0701b9339 100644
--- a/grafana/dashboards/DORA.json
+++ b/grafana/dashboards/DORA.json
@@ -235,7 +235,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith
last_few_calendar_months as(\n-- construct the last few calendar months within
the selected time period in the top-right corner\n\tSELECT
CAST(($__timeTo()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0
H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t)
H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20
UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT [...]
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith
last_few_calendar_months as(\n -- construct the last few calendar months
within the selected time period in the top-right corner\n SELECT\n
CAST(($__timeTo() - INTERVAL (H + T + U) DAY) AS date) day\n FROM\n (\n
SELECT\n 0 H\n UNION\n ALL\n SELECT\n 100\n
UNION\n ALL\n SELECT\n 200\n UNION\n ALL\n
SELECT\n 300\n ) H\n CROSS JOIN (\n [...]
"refId": "A",
"select": [
[
@@ -669,7 +669,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\tJOIN proje [...]
+ "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n -- When deploying multiple commits in one pipeline, GitLab and BitBucket
may generate more than one deployment. However, DevLake consider these
deployments as ONE production deployment and use the last one's finished_date
as the finished date.\n SELECT\n cdc.cicd_deployment_id as deployment_id,\n
max(cdc.finished_date) as deployment_finished_date\n FROM\n
cicd_deployment_commits cdc\n JOIN proj [...]
"refId": "A",
"select": [
[
@@ -821,7 +821,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits cdc\n
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and
cdc.result = 'SUCCESS'\n and cdc [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM\n cicd_deployment_commits cdc\n JOIN
project_mapping pm on cdc.cicd_scope_id = pm.row_id\n and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and cdc.result =
'SUCCESS'\n and cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -1237,7 +1237,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n -- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM\n cicd_deployment_commits cdc\n [...]
"refId": "A",
"select": [
[
@@ -1393,7 +1393,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits cdc\n
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and
cdc.result = 'SUCCESS'\n and cdc [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM\n cicd_deployment_commits cdc\n JOIN
project_mapping pm on cdc.cicd_scope_id = pm.row_id\n and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and cdc.result =
'SUCCESS'\n and cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -1527,4 +1527,4 @@
"uid": "qNo8_0M4z",
"version": 8,
"weekStart": ""
-}
+}
\ No newline at end of file
diff --git a/grafana/dashboards/DORAByTeam.json
b/grafana/dashboards/DORAByTeam.json
index c1901c7bd..4556ddd10 100644
--- a/grafana/dashboards/DORAByTeam.json
+++ b/grafana/dashboards/DORAByTeam.json
@@ -208,7 +208,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith
last_few_calendar_months as(\n-- construct the last few calendar months within
the selected time period in the top-right corner\n\tSELECT
CAST(($__timeTo()-INTERVAL (H+T+U) DAY) AS date) day\n\tFROM ( SELECT 0
H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t)
H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20
UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT [...]
+ "rawSql": "-- Metric 1: Deployment Frequency\nwith
last_few_calendar_months as(\n -- construct the last few calendar months
within the selected time period in the top-right corner\n SELECT\n
CAST(($__timeTo() - INTERVAL (H + T + U) DAY) AS date) day\n FROM\n (\n
SELECT\n 0 H\n UNION\n ALL\n SELECT\n 100\n
UNION\n ALL\n SELECT\n 200\n UNION\n ALL\n
SELECT\n 300\n ) H\n CROSS JOIN (\n [...]
"refId": "A",
"sql": {
"columns": [
@@ -578,7 +578,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t JOIN com [...]
+ "rawSql": "-- Metric 4: change failure rate\nwith _deployments as
(\n -- When deploying multiple commits in one pipeline, GitLab and BitBucket
may generate more than one deployment. However, DevLake consider these
deployments as ONE production deployment and use the last one's finished_date
as the finished date.\n SELECT\n cdc.cicd_deployment_id as deployment_id,\n
max(cdc.finished_date) as deployment_finished_date\n FROM\n
cicd_deployment_commits cdc\n JOIN comm [...]
"refId": "A",
"sql": {
"columns": [
@@ -699,7 +699,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits
cdc\n\t\tJOIN commits c on cdc.commit_sha = c.sha\n join user_accounts
ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n
join team_users tu on u.id = tu.user_id [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits
cdc\n\t\tJOIN commits c on cdc.commit_sha = c.sha\n join user_accounts
ua on c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n
join team_users tu on u.id = tu.user_id [...]
"refId": "A",
"sql": {
"columns": [
@@ -1032,7 +1032,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "rawSql": "-- Metric 4: change failure rate per month\nwith
_deployments as (\n -- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM\n cicd_deployment_commits cdc\n [...]
"refId": "A",
"sql": {
"columns": [
@@ -1141,7 +1141,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits
cdc\n\t\tJOIN commits c on cdc.commit_sha = c.sha\n\tjoin user_accounts ua on
c.author_id = ua.account_id\n join users u on ua.user_id = u.id\n join
team_users tu on u.id = tu.user_id\n join tea [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM\n cicd_deployment_commits cdc\n JOIN
commits c on cdc.commit_sha = c.sha\n join user_accounts ua on c.author_id =
ua.account_id\n join users u on ua.user_id = u.id\n join team_users tu on
u.id = tu.user_id\n join teams t on tu.team [...]
"refId": "A",
"sql": {
"columns": [
diff --git a/grafana/dashboards/DORADebug.json
b/grafana/dashboards/DORADebug.json
index 2b3408b01..8cfd179ed 100644
--- a/grafana/dashboards/DORADebug.json
+++ b/grafana/dashboards/DORADebug.json
@@ -2948,7 +2948,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits cdc\n
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and
cdc.result = 'SUCCESS'\n and cdc [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM\n cicd_deployment_commits cdc\n JOIN
project_mapping pm on cdc.cicd_scope_id = pm.row_id\n and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and cdc.result =
'SUCCESS'\n and cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -3065,7 +3065,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- get the incident created within the selected time
period in the top-right
corner\nSELECT\n\tpm.project_name,\n\tIF(pm.project_name in ($project),'This
project is selected','Not Selected') as
select_status,\n\ti._raw_data_table,\n\ti.type, \n\tcount(1) as
issue_count\nFROM\n\tissues i\n join board_issues bi on i.id = bi.issue_id\n
join boards b on bi.board_id = b.id\n join project_mapping pm on b.id =
pm.row_id and pm.`table` = 'boards'\nWHERE\n pm.project_name [...]
+ "rawSql": "-- get the incident created within the selected time
period in the top-right
corner\nSELECT\n\tpm.project_name,\n\tIF(pm.project_name in ($project),'This
project is selected','Not Selected') as
select_status,\n\ti._raw_data_table,\n\tcount(1) as
issue_count\nFROM\n\tincidents i \n join project_mapping pm on i.scope_id =
pm.row_id and pm.`table` = i.`table`\nWHERE\n pm.project_name in
($project)\n\tand $__timeFilter(i.created_date)\nGROUP BY
pm.project_name,select_ [...]
"refId": "A",
"select": [
[
@@ -3231,7 +3231,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM \n cicd_deployment_commits cdc\n
JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and
cdc.result = 'SUCCESS'\n and cdc [...]
+ "rawSql": "-- ***** 2023 report ***** --\n-- Metric 4: Failed
deployment recovery time\nwith _deployments as (\n SELECT\n
cdc.cicd_deployment_id as deployment_id,\n max(cdc.finished_date) as
deployment_finished_date\n FROM\n cicd_deployment_commits cdc\n JOIN
project_mapping pm on cdc.cicd_scope_id = pm.row_id\n and pm.`table` =
'cicd_scopes'\n WHERE\n pm.project_name in ($project)\n and cdc.result =
'SUCCESS'\n and cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -3369,7 +3369,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\tJOIN proje [...]
+ "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n -- When deploying multiple commits in one pipeline, GitLab and BitBucket
may generate more than one deployment. However, DevLake consider these
deployments as ONE production deployment and use the last one's finished_date
as the finished date.\n SELECT\n cdc.cicd_deployment_id as deployment_id,\n
max(cdc.finished_date) as deployment_finished_date\n FROM\n
cicd_deployment_commits cdc\n JOIN proj [...]
"refId": "A",
"select": [
[
@@ -3487,7 +3487,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "with _deployments as(\n select distinct \n
d.cicd_deployment_id as deployment_id,\n d.result,\n
d.environment,\n d.finished_date,\n d.cicd_scope_id,\n
pm.project_name\n from \n cicd_deployment_commits d\n join
project_mapping pm on d.cicd_scope_id = pm.row_id and pm.`table` =
'cicd_scopes'\n where \n -- only result needs to specified, not
envioronment\n d.result = 'SUCCESS'\n -- cho [...]
+ "rawSql": "with _deployments as(\n select\n distinct
d.cicd_deployment_id as deployment_id,\n d.result,\n d.environment,\n
d.finished_date,\n d.cicd_scope_id,\n pm.project_name\n from\n
cicd_deployment_commits d\n join project_mapping pm on d.cicd_scope_id =
pm.row_id\n and pm.`table` = 'cicd_scopes'\n where\n -- only result
needs to specified, not envioronment\n d.result = 'SUCCESS' -- choose your
project_name\n and pm.project_name in ($p [...]
"refId": "A",
"select": [
[
@@ -3736,7 +3736,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n -- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM\n cicd_deployment_commits cdc\n [...]
"refId": "A",
"select": [
[
