This is an automated email from the ASF dual-hosted git repository. lynwee pushed a commit to branch dev-1 in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
commit 37f675e13d1c9a74b83f8346bf3843c58f6750f7 Author: d4x1 <[email protected]> AuthorDate: Thu Aug 1 11:23:03 2024 +0800 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": [ [
