You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by ab...@apache.org on 2023/05/12 08:24:44 UTC
[incubator-devlake] branch release-v0.17 updated: fix: update DORA validation dashboard (#5172)
This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch release-v0.17
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
The following commit(s) were added to refs/heads/release-v0.17 by this push:
new 499db473e fix: update DORA validation dashboard (#5172)
499db473e is described below
commit 499db473e4f2d08a5a2654e3ab989812fcda051c
Author: Louis.z <lo...@gmail.com>
AuthorDate: Fri May 12 16:23:17 2023 +0800
fix: update DORA validation dashboard (#5172)
Co-authored-by: Startrekzky <ka...@merico.dev>
---
grafana/dashboards/DORA DEBUG.json | 1873 -----------------------
grafana/dashboards/DORA.json | 24 +-
grafana/dashboards/DORADebug.json | 2934 ++++++++++++++++++++++++++++++++++++
3 files changed, 2946 insertions(+), 1885 deletions(-)
diff --git a/grafana/dashboards/DORA DEBUG.json b/grafana/dashboards/DORA DEBUG.json
deleted file mode 100644
index 90dab7334..000000000
--- a/grafana/dashboards/DORA DEBUG.json
+++ /dev/null
@@ -1,1873 +0,0 @@
-{
- "annotations": {
- "list": [
- {
- "builtIn": 1,
- "datasource": "-- Grafana --",
- "enable": true,
- "hide": true,
- "iconColor": "rgba(0, 211, 255, 1)",
- "name": "Annotations & Alerts",
- "type": "dashboard"
- }
- ]
- },
- "editable": true,
- "gnetId": null,
- "graphTooltip": 0,
- "id": 34,
- "iteration": 1678442002166,
- "links": [],
- "panels": [
- {
- "collapsed": true,
- "datasource": null,
- "gridPos": {
- "h": 1,
- "w": 24,
- "x": 0,
- "y": 0
- },
- "id": 22,
- "panels": [
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "blue",
- "mode": "thresholds"
- },
- "custom": {
- "align": "auto",
- "displayMode": "auto",
- "filterable": false
- },
- "mappings": [],
- "noValue": "-",
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "text",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "low"
- },
- "properties": [
- {
- "id": "custom.displayMode",
- "value": "color-text"
- },
- {
- "id": "color",
- "value": {
- "fixedColor": "red",
- "mode": "fixed"
- }
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "medium"
- },
- "properties": [
- {
- "id": "custom.displayMode",
- "value": "color-text"
- },
- {
- "id": "color",
- "value": {
- "fixedColor": "yellow",
- "mode": "fixed"
- }
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "high"
- },
- "properties": [
- {
- "id": "custom.displayMode",
- "value": "color-text"
- },
- {
- "id": "color",
- "value": {
- "fixedColor": "green",
- "mode": "fixed"
- }
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "elite"
- },
- "properties": [
- {
- "id": "custom.displayMode",
- "value": "color-text"
- },
- {
- "id": "color",
- "value": {
- "fixedColor": "purple",
- "mode": "fixed"
- }
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 1
- },
- "id": 8,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-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 50 UNION ALL [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_blueprints",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Overall DORA Metrics",
- "type": "table"
- }
- ],
- "title": "Overall DORA Metrics",
- "type": "row"
- },
- {
- "collapsed": false,
- "datasource": null,
- "gridPos": {
- "h": 1,
- "w": 24,
- "x": 0,
- "y": 1
- },
- "id": 20,
- "panels": [],
- "title": "Deployment Frequency",
- "type": "row"
- },
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 255, 255, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "left",
- "displayMode": "color-background-solid"
- },
- "mappings": [
- {
- "options": {
- "DEPLOYMENT": {
- "color": "green",
- "index": 1
- },
- "PRODUCTION": {
- "color": "green",
- "index": 0
- },
- "SUCCESS": {
- "color": "green",
- "index": 2
- },
- "this project": {
- "color": "green",
- "index": 3
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "type"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 103
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "result"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 107
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "environment"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 132
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "count(1)"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 96
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "IF(pm.project_name in ('all_6'),'this project',pm.project_name)"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 104
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 8,
- "w": 12,
- "x": 0,
- "y": 2
- },
- "id": 16,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n\tct._raw_data_table,IF(pm.project_name in ($project),'this project',pm.project_name) as project_name,`type`,result,environment,count(1)\nFROM cicd_tasks ct\nLEFT join project_mapping pm on ct.cicd_scope_id = pm.row_id\nGROUP BY pm.project_name, ct._raw_data_table,`type`,result,environment",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 1. all cicd_tasks (the rows with 4 green columns will be used in next table)",
- "type": "table"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "custom": {
- "align": "auto",
- "displayMode": "auto"
- },
- "mappings": [
- {
- "options": {
- "Between once per month and once every 6 months": {
- "color": "yellow",
- "index": 1
- },
- "Between once per week and once per month": {
- "color": "green",
- "index": 2
- },
- "Fewer than once per six months": {
- "color": "red",
- "index": 0
- },
- "On-demand": {
- "color": "purple",
- "index": 3
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "day"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": null
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 5,
- "w": 7,
- "x": 12,
- "y": 2
- },
- "id": 11,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n\tdistinct DATE(finished_date) AS day,\n\tcount(1)\nFROM cicd_tasks ct\njoin project_mapping pm on ct.cicd_scope_id = pm.row_id\nWHERE\n pm.project_name in ($project)\n\tand type = 'DEPLOYMENT'\n\tand result = 'SUCCESS'\n\tand environment = 'PRODUCTION'\nGROUP BY 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 3. Deployment Checker for 「Deployment Frequency」",
- "type": "table"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [
- {
- "options": {
- "Between once per month and once every 6 months": {
- "color": "yellow",
- "index": 1
- },
- "Between once per week and once per month": {
- "color": "green",
- "index": 2
- },
- "Fewer than once per six months": {
- "color": "red",
- "index": 0
- },
- "On-demand": {
- "color": "purple",
- "index": 3
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 5,
- "w": 5,
- "x": 19,
- "y": 2
- },
- "id": 15,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "/^Deployment Frequency$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith last_few_calendar_months as(\n-- get the last few months within the selected time period in the top-right corner\n\tSELECT CAST((SYSDATE()-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 50 UNION ALL SELE [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Deployment Frequency",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 8,
- "w": 12,
- "x": 12,
- "y": 7
- },
- "id": 2,
- "options": {
- "barWidth": 0.6,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 1: Number of deployments per month\nwith _deployments as (\n-- get the deployment count each month\n\tSELECT\n\t\tdate_format(finished_date,'%y/%m') as month,\n\t\tCOUNT(distinct id) AS deployment_count\n\tFROM\n\t\tcicd_tasks ct\n\t\tjoin project_mapping pm on ct.cicd_scope_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand type = 'DEPLOYMENT'\n\t\tand result = 'SUCCESS'\n\t\tand environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_calendar_mo [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_blueprints",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Number of deployments",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 255, 255, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "left",
- "displayMode": "color-background-solid"
- },
- "mappings": [
- {
- "options": {
- "DEPLOYMENT": {
- "color": "green",
- "index": 1
- },
- "PRODUCTION": {
- "color": "green",
- "index": 0
- },
- "SUCCESS": {
- "color": "green",
- "index": 2
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "type"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 112
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "result"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 107
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "environment"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 132
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "count(1)"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 96
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 5,
- "w": 12,
- "x": 0,
- "y": 10
- },
- "id": 29,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n\tct._raw_data_table,`type`,result,environment,count(1)\nFROM cicd_tasks ct\njoin project_mapping pm on ct.cicd_scope_id = pm.row_id\nWHERE\n pm.project_name in ($project)\n\tand type = 'DEPLOYMENT'\n\tand result = 'SUCCESS'\n\tand environment = 'PRODUCTION'\nGROUP BY ct._raw_data_table,`type`,result,environment",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 2. cicd_tasks in this project",
- "type": "table"
- },
- {
- "collapsed": false,
- "datasource": null,
- "gridPos": {
- "h": 1,
- "w": 24,
- "x": 0,
- "y": 15
- },
- "id": 28,
- "panels": [],
- "title": "Median Lead Time for Changes",
- "type": "row"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 254, 254, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "auto",
- "displayMode": "color-background"
- },
- "mappings": [
- {
- "options": {
- "from": 1,
- "result": {
- "color": "green",
- "index": 0
- },
- "to": 10000000
- },
- "type": "range"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 7,
- "w": 12,
- "x": 0,
- "y": 16
- },
- "id": 12,
- "options": {
- "showHeader": true
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n\tpr._raw_data_table,count(1)\nFROM\n\tpull_requests pr \n-- \tjoin project_pr_metrics prm on prm.id = pr.id\n\tjoin project_mapping pm on pr.base_repo_id = pm.row_id AND `table`='repos'\nWHERE\n pm.project_name in ($project)\n\tand pr.merged_date is not null\n-- \tand prm.pr_cycle_time is not null\n\tand $__timeFilter(pr.merged_date)\nGROUP BY pr._raw_data_table\n",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 1. PR Checker for 「Median Lead Time for Changes」",
- "type": "table"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [
- {
- "options": {
- "Between one week and six months": {
- "color": "yellow",
- "index": 1
- },
- "Less than one hour": {
- "color": "purple",
- "index": 3
- },
- "Less than one week": {
- "color": "green",
- "index": 2
- },
- "More than six months": {
- "color": "red",
- "index": 0
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 5,
- "w": 6,
- "x": 15,
- "y": 16
- },
- "id": 17,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "/^median_change_lead_time$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 2: median change lead time\nwith _pr_stats as (\n-- get PRs' cycle time in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project) \n\t\tand pr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_date)\n),\n\n_med [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Median Lead Time for Changes",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Hours",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 8,
- "w": 12,
- "x": 12,
- "y": 21
- },
- "id": 6,
- "options": {
- "barWidth": 0.7,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "hide": false,
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get PRs' cycle lead time in each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(pr.merged_date,'%y/%m') as month,\n\t\tprm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics prm on prm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\tWHERE\n\t\tpr.merged_date is not null\n\t\tand prm.pr_cycle_time is not null\n\t\tand $__timeFilter(pr.merged_d [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ae_projects",
- "timeColumn": "ae_create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Median Lead Time for Changes",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 254, 254, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "auto",
- "displayMode": "color-background"
- },
- "mappings": [
- {
- "options": {
- "from": 1,
- "result": {
- "color": "green",
- "index": 0
- },
- "to": 10000000
- },
- "type": "range"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 0,
- "y": 23
- },
- "id": 18,
- "options": {
- "showHeader": true
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n\tpr._raw_data_table,count(1)\nFROM\n\tpull_requests pr \n\tjoin project_pr_metrics prm on prm.id = pr.id\n\tjoin project_mapping pm on pr.base_repo_id = pm.row_id AND `table`='repos'\nWHERE\n pm.project_name in ($project)\n\tand pr.merged_date is not null\n\tand prm.pr_cycle_time is not null\n\tand $__timeFilter(pr.merged_date)\nGROUP BY pr._raw_data_table\n",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 2. pr_metrics Checker for 「Median Lead Time for Changes」",
- "type": "table"
- },
- {
- "collapsed": false,
- "datasource": null,
- "gridPos": {
- "h": 1,
- "w": 24,
- "x": 0,
- "y": 29
- },
- "id": 26,
- "panels": [],
- "title": "Change Failure Rate & Median Time to Restore Service",
- "type": "row"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 255, 255, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "auto",
- "displayMode": "color-background"
- },
- "mappings": [
- {
- "options": {
- "INCIDENT": {
- "color": "green",
- "index": 0
- },
- "this project": {
- "color": "green",
- "index": 1
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "type"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 122
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "project_name"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 119
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 7,
- "w": 12,
- "x": 0,
- "y": 30
- },
- "id": 31,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- get the incident created within the selected time period in the top-right corner\nSELECT\n\ti._raw_data_table, IF(pm.project_name in ($project),'this project',pm.project_name) as project_name,i.type,count(1)\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\nWHERE\n pm.project_name in ($project)\n-- \tand i.type = 'INCIDENT'\n\tand $__timeFilter(i.created_date)\nGR [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 1. all issue (Change Failure Rate)",
- "type": "table"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [
- {
- "options": {
- "0-15%": {
- "color": "purple",
- "index": 3
- },
- "16%-20%": {
- "color": "green",
- "index": 2
- },
- "21%-30%": {
- "color": "yellow",
- "index": 1
- },
- "> 30%": {
- "color": "red",
- "index": 0
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 5,
- "w": 6,
- "x": 12,
- "y": 30
- },
- "id": 30,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "/^change_failure_rate$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployment_count as (\n-- get the deployment deployed within the selected time period in the top-right corner\n\tSELECT\n\t\tCOUNT(distinct id) AS deployment_count\n\tFROM\n\t\tcicd_tasks ct\n\t\tjoin project_mapping pm on ct.cicd_scope_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand type = 'DEPLOYMENT'\n\t\tand result = 'SUCCESS'\n\t\tand environment = 'PRODUCTION'\n and $__timeFilter(finished_date)\n),\ [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Change Failure Rate",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [
- {
- "options": {
- "Between one day and one week": {
- "color": "yellow",
- "index": 1
- },
- "Less than one day": {
- "color": "green",
- "index": 2
- },
- "Less than one hour": {
- "color": "purple",
- "index": 3
- },
- "More than one week": {
- "color": "red",
- "index": 0
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 5,
- "w": 6,
- "x": 18,
- "y": 30
- },
- "id": 32,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "/^med_time_to_resolve$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCID [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Median Time to Restore Service",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "percentunit"
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "change_failure_rate"
- },
- "properties": [
- {
- "id": "color",
- "value": {
- "fixedColor": "blue",
- "mode": "fixed"
- }
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 8,
- "w": 12,
- "x": 12,
- "y": 35
- },
- "id": 5,
- "options": {
- "barWidth": 0.6,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "hide": false,
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _deployments as (\n-- get the deployment count each month\n\tSELECT\n\t\tdate_format(finished_date,'%y/%m') as month,\n\t\tCOUNT(distinct id) AS deployment_count\n\tFROM\n\t\tcicd_tasks ct\n\t\tjoin project_mapping pm on ct.cicd_scope_id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand type = 'DEPLOYMENT'\n\t\tand result = 'SUCCESS'\n\t\tand environment = 'PRODUCTION'\n\tGROUP BY 1\n),\n\n_incidents as (\n-- get the incident count each month\n\tS [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ae_projects",
- "timeColumn": "ae_create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Change Failure Rate",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "fieldConfig": {
- "defaults": {
- "color": {
- "fixedColor": "rgba(255, 255, 255, 1)",
- "mode": "fixed"
- },
- "custom": {
- "align": "auto",
- "displayMode": "color-background"
- },
- "mappings": [
- {
- "options": {
- "INCIDENT": {
- "color": "green",
- "index": 0
- },
- "this project": {
- "color": "green",
- "index": 1
- }
- },
- "type": "value"
- }
- ],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "type"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 122
- }
- ]
- },
- {
- "matcher": {
- "id": "byName",
- "options": "project_name"
- },
- "properties": [
- {
- "id": "custom.width",
- "value": 119
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 7,
- "w": 12,
- "x": 0,
- "y": 37
- },
- "id": 14,
- "options": {
- "showHeader": true,
- "sortBy": []
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "-- get the incident created within the selected time period in the top-right corner\nSELECT\n\ti._raw_data_table, IF(pm.project_name in ($project),'this project',pm.project_name) as project_name,i.type, count(1)\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\nWHERE\n pm.project_name in ($project)\n\tand i.type = 'INCIDENT'\n\tand $__timeFilter(i.created_date)\nGROU [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "_devlake_tasks",
- "timeColumn": "created_at",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Step 2. issue Checker (Change Failure Rate)",
- "type": "table"
- },
- {
- "datasource": "mysql",
- "description": "",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Hours",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "none"
- },
- "overrides": [
- {
- "matcher": {
- "id": "byName",
- "options": "med_time_to_resolve_in_hour"
- },
- "properties": [
- {
- "id": "color",
- "value": {
- "fixedColor": "blue",
- "mode": "fixed"
- }
- }
- ]
- }
- ]
- },
- "gridPos": {
- "h": 8,
- "w": 12,
- "x": 12,
- "y": 43
- },
- "id": 9,
- "options": {
- "barWidth": 0.6,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "hide": false,
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the incident count each month\n\tSELECT\n\t distinct i.id,\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type [...]
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "id"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ae_projects",
- "timeColumn": "ae_create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Median Time to Restore Service",
- "type": "barchart"
- }
- ],
- "refresh": "",
- "schemaVersion": 30,
- "style": "dark",
- "tags": [],
- "templating": {
- "list": [
- {
- "allValue": null,
- "current": {
- "selected": false,
- "text": [
- "all_6"
- ],
- "value": [
- "all_6"
- ]
- },
- "datasource": "mysql",
- "definition": "select distinct name from projects",
- "description": null,
- "error": null,
- "hide": 0,
- "includeAll": true,
- "label": "Project",
- "multi": true,
- "name": "project",
- "options": [],
- "query": "select distinct name from projects",
- "refresh": 1,
- "regex": "",
- "skipUrlSync": false,
- "sort": 0,
- "type": "query"
- }
- ]
- },
- "time": {
- "from": "now-6M",
- "to": "now"
- },
- "timepicker": {},
- "timezone": "",
- "title": "DORA Flow",
- "uid": "KGkUnV-Vz",
- "version": 15
-}
\ No newline at end of file
diff --git a/grafana/dashboards/DORA.json b/grafana/dashboards/DORA.json
index 1a034df49..eb36a0ca0 100644
--- a/grafana/dashboards/DORA.json
+++ b/grafana/dashboards/DORA.json
@@ -15,8 +15,8 @@
"editable": true,
"gnetId": null,
"graphTooltip": 0,
- "id": 7,
- "iteration": 1682586827776,
+ "id": 21,
+ "iteration": 1683877262059,
"links": [],
"panels": [
{
@@ -143,7 +143,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((SYSDATE()-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 50 [...]
+ "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((SYSDATE()-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 50 [...]
"refId": "A",
"select": [
[
@@ -242,7 +242,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((SYSDATE()-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 50 [...]
+ "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((SYSDATE()-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 50 [...]
"refId": "A",
"select": [
[
@@ -341,7 +341,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.project_name in ($project) \n\ [...]
+ "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.proje [...]
"refId": "A",
"select": [
[
@@ -444,7 +444,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\t\tand i.type = 'INCID [...]
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in ($project [...]
"refId": "A",
"select": [
[
@@ -543,7 +543,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"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\tJOIN proje [...]
+ "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\tJOIN proje [...]
"refId": "A",
"select": [
[
@@ -636,7 +636,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Metric 1: Number of deployments 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\tdate_format(deployment_finished_date,'%y/%m') as month,\n\t\tcount(cicd_deployment_id) as deployment_count\n\tFROM (\n\t\tSELECT\n\t\t\tcdc.ci [...]
+ "rawSql": "-- Metric 1: Number of deployments 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\tdate_format(deployment_finished_date,'%y/%m') as month,\n\t\tcount(cicd_deployment_id) as deployment_count\n\tFROM (\n\t\tSELECT\n\t\t\tcdc.ci [...]
"refId": "A",
"select": [
[
@@ -727,7 +727,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(cdc.finished_date,'%y/%m') as month,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n [...]
+ "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(cdc.finished_date,'%y/%m') as month,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deploy [...]
"refId": "A",
"select": [
[
@@ -841,7 +841,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the number of incidents created each month\n\tSELECT\n\t distinct i.id,\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n\tWHERE\n\t pm.project_name in ($project)\n\ [...]
+ "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the number of incidents created each month\n\tSELECT\n\t distinct i.id,\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.pro [...]
"refId": "A",
"select": [
[
@@ -953,7 +953,7 @@
"hide": false,
"metricColumn": "none",
"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\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 [...]
"refId": "A",
"select": [
[
@@ -1027,5 +1027,5 @@
"timezone": "",
"title": "DORA",
"uid": "qNo8_0M4z",
- "version": 12
+ "version": 2
}
\ No newline at end of file
diff --git a/grafana/dashboards/DORADebug.json b/grafana/dashboards/DORADebug.json
new file mode 100644
index 000000000..ba74081c6
--- /dev/null
+++ b/grafana/dashboards/DORADebug.json
@@ -0,0 +1,2934 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": "-- Grafana --",
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "gnetId": null,
+ "graphTooltip": 0,
+ "id": 11,
+ "iteration": 1683808440856,
+ "links": [],
+ "panels": [
+ {
+ "collapsed": false,
+ "datasource": null,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 22,
+ "panels": [],
+ "title": "Overall DORA Metrics",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "blue",
+ "mode": "thresholds"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "auto",
+ "filterable": false
+ },
+ "mappings": [],
+ "noValue": "-",
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "text",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "low"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-text"
+ },
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "red",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "medium"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-text"
+ },
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "yellow",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "high"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-text"
+ },
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "green",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "elite"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-text"
+ },
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "purple",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 1
+ },
+ "id": 36,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "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((SYSDATE()-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 50 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprints",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Overall DORA Metrics",
+ "type": "table"
+ },
+ {
+ "collapsed": false,
+ "datasource": null,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 7
+ },
+ "id": 20,
+ "panels": [],
+ "title": "Check Deployment Frequency",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "left",
+ "displayMode": "color-background-solid"
+ },
+ "mappings": [
+ {
+ "options": {
+ "DEPLOYMENT": {
+ "color": "green",
+ "index": 1
+ },
+ "PRODUCTION": {
+ "color": "green",
+ "index": 0
+ },
+ "SUCCESS": {
+ "color": "green",
+ "index": 2
+ },
+ "This project is selected": {
+ "color": "green",
+ "index": 3
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 16,
+ "x": 0,
+ "y": 8
+ },
+ "id": 16,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\n\tpm.project_name,\n\tIF(pm.project_name in ($project),'This project is selected','Not Selected') as select_status,\n\tIF(cdc._raw_data_table != '', cdc._raw_data_table, cdc.cicd_scope_id) as _raw_data_table,\n\tresult,\n\tenvironment,\n\tcount(distinct cdc.id) as deployment_commit_count, \n\tcount(distinct cdc.cicd_deployment_id) as deployment_count\nFROM cicd_deployment_commits cdc\nLEFT join project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 1. All cicd_deployment_commits (the rows with 3 green columns will be used in the following steps)",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "Between once per month and once every 6 months": {
+ "color": "yellow",
+ "index": 1
+ },
+ "Between once per week and once per month": {
+ "color": "green",
+ "index": 2
+ },
+ "Fewer than once per six months": {
+ "color": "red",
+ "index": 0
+ },
+ "On-demand": {
+ "color": "purple",
+ "index": 3
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 9,
+ "w": 8,
+ "x": 16,
+ "y": 8
+ },
+ "id": 15,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^Deployment Frequency$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "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((SYSDATE()-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 50 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Figure 1 - Deployment Frequency",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "left",
+ "displayMode": "color-background-solid"
+ },
+ "mappings": [
+ {
+ "options": {
+ "DEPLOYMENT": {
+ "color": "green",
+ "index": 1
+ },
+ "PRODUCTION": {
+ "color": "green",
+ "index": 0
+ },
+ "SUCCESS": {
+ "color": "green",
+ "index": 2
+ },
+ "This project is selected": {
+ "color": "green",
+ "index": 3
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 16,
+ "x": 0,
+ "y": 13
+ },
+ "id": 29,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\n\tpm.project_name,\n\tIF(pm.project_name in ($project),'This project is selected','Not Selected') as select_status,\n\tIF(cdc._raw_data_table != '', cdc._raw_data_table, cdc.cicd_scope_id) as _raw_data_table,\n\tresult,\n\tenvironment,\n\tcount(distinct cdc.id) as deployment_commit_count, \n\tcount(distinct cdc.cicd_deployment_id) as deployment_count\nFROM cicd_deployment_commits cdc\njoin project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = ' [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 2. Find the number of successful production deployments in this project (Last column)",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 11,
+ "w": 8,
+ "x": 16,
+ "y": 17
+ },
+ "id": 34,
+ "options": {
+ "barWidth": 0.6,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {},
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Metric 1: Number of deployments 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\tdate_format(deployment_finished_date,'%y/%m') as month,\n\t\tcount(cicd_deployment_id) as deployment_count\n\tFROM (\n\t\tSELECT\n\t\t\tcdc.ci [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprints",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Figure 2 - Monthly deployments",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "left",
+ "displayMode": "color-background-solid"
+ },
+ "mappings": [
+ {
+ "options": {
+ "DEPLOYMENT": {
+ "color": "green",
+ "index": 1
+ },
+ "PRODUCTION": {
+ "color": "green",
+ "index": 0
+ },
+ "SUCCESS": {
+ "color": "green",
+ "index": 2
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "deployment_id"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": null
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 16,
+ "x": 0,
+ "y": 18
+ },
+ "id": 49,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _deployment_commit_rank as(\n SELECT\n \tpm.project_name,\n \tIF(cdc._raw_data_table != '', cdc._raw_data_table, cdc.cicd_scope_id) as _raw_data_table,\n \tcdc.id,\n \tcdc.cicd_deployment_id,\n \tcdc.cicd_scope_id,\n \tresult,\n \tenvironment,\n finished_date,\n row_number() over(partition by cdc.cicd_deployment_id order by finished_date desc) as _deployment_commit_rank\n FROM cicd_deployment_commits cdc\n left join project_mapping pm on cdc.cicd [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 3. Use the last finished_date of deployment commits as the finished date of deployments in this project",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "auto"
+ },
+ "mappings": [
+ {
+ "options": {
+ "Between once per month and once every 6 months": {
+ "color": "yellow",
+ "index": 1
+ },
+ "Between once per week and once per month": {
+ "color": "green",
+ "index": 2
+ },
+ "Fewer than once per six months": {
+ "color": "red",
+ "index": 0
+ },
+ "On-demand": {
+ "color": "purple",
+ "index": 3
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "day"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": null
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 0,
+ "y": 23
+ },
+ "id": 11,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _deployment_commit_rank as(\n SELECT\n \tpm.project_name,\n \tIF(cdc._raw_data_table != '', cdc._raw_data_table, cdc.cicd_scope_id) as _raw_data_table,\n \tcdc.id,\n \tcdc.cicd_deployment_id,\n \tcdc.cicd_scope_id,\n \tresult,\n \tenvironment,\n finished_date,\n row_number() over(partition by cdc.cicd_deployment_id order by finished_date desc) as _deployment_commit_rank\n FROM cicd_deployment_commits cdc\n left join project_mapping pm on cdc.cicd [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 4. Daily deployments in this project [To check Figure 1]",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "auto"
+ },
+ "mappings": [
+ {
+ "options": {
+ "Between once per month and once every 6 months": {
+ "color": "yellow",
+ "index": 1
+ },
+ "Between once per week and once per month": {
+ "color": "green",
+ "index": 2
+ },
+ "Fewer than once per six months": {
+ "color": "red",
+ "index": 0
+ },
+ "On-demand": {
+ "color": "purple",
+ "index": 3
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "day"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": null
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 23
+ },
+ "id": 50,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _deployment_commit_rank as(\n SELECT\n \tpm.project_name,\n \tIF(cdc._raw_data_table != '', cdc._raw_data_table, cdc.cicd_scope_id) as _raw_data_table,\n \tcdc.id,\n \tcdc.cicd_deployment_id,\n \tcdc.cicd_scope_id,\n \tresult,\n \tenvironment,\n finished_date,\n row_number() over(partition by cdc.cicd_deployment_id order by finished_date desc) as _deployment_commit_rank\n FROM cicd_deployment_commits cdc\n left join project_mapping pm on cdc.cicd [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 5. Monthly deployments in this project [To check Figure 2]",
+ "type": "table"
+ },
+ {
+ "collapsed": false,
+ "datasource": null,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 28
+ },
+ "id": 28,
+ "panels": [],
+ "title": "Check \"Median Lead Time for Changes\"",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background-solid",
+ "filterable": false
+ },
+ "mappings": [
+ {
+ "options": {
+ "This Project": {
+ "color": "green",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "merged_date"
+ },
+ "properties": [
+ {
+ "id": "color"
+ },
+ {
+ "id": "custom.displayMode",
+ "value": "color-background-solid"
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byType",
+ "options": "time"
+ },
+ "properties": [
+ {
+ "id": "thresholds",
+ "value": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 0
+ }
+ ]
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "project_name"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-background-solid"
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 3,
+ "w": 8,
+ "x": 0,
+ "y": 29
+ },
+ "id": 53,
+ "options": {
+ "showHeader": true
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\n IF(pm.project_name in ($project),'This project',pm.project_name) as project_name,\n\t-- pr.status,\n\tpr.title,\n\tpr.author_name,\n\tpr.url,\n\tpr.merged_date,\n\tpr.created_date\nFROM\n\tpull_requests pr \n\tjoin project_mapping pm on pr.base_repo_id = pm.row_id AND pm.`table`='repos'\nWHERE\n -- pm.project_name in ($project)\n\tpr.id = '$pr_id'\n\tand $__timeFilter(pr.created_date)\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Details of the selected pull request (ONLY Adopt the Pull Request URL filter above)",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 254, 254, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background"
+ },
+ "mappings": [
+ {
+ "options": {
+ "from": 1,
+ "result": {
+ "color": "green",
+ "index": 0
+ },
+ "to": 10000000
+ },
+ "type": "range"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 3,
+ "w": 8,
+ "x": 8,
+ "y": 29
+ },
+ "id": 18,
+ "options": {
+ "showHeader": true
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\n\tpr._raw_data_table,count(1) as total_number_of_PRs\nFROM\n\tpull_requests pr \n-- \tjoin project_pr_metrics prm on prm.id = pr.id\n\tjoin project_mapping pm on pr.base_repo_id = pm.row_id AND pm.`table`='repos'\nWHERE\n pm.project_name in ($project)\n-- \tand pr.merged_date is not null\n-- \tand prm.pr_cycle_time is not null\n\tand $__timeFilter(pr.created_date)\nGROUP BY pr._raw_data_table\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 1. Check if the total PR number in this project is correct",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "Between one week and six months": {
+ "color": "yellow",
+ "index": 1
+ },
+ "Less than one hour": {
+ "color": "purple",
+ "index": 3
+ },
+ "Less than one week": {
+ "color": "green",
+ "index": 2
+ },
+ "More than six months": {
+ "color": "red",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 13,
+ "w": 8,
+ "x": 16,
+ "y": 29
+ },
+ "id": 40,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^median_change_lead_time$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Metric 2: median lead time for changes\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished in the selected period\n\tSELECT\n\t\tdistinct pr.id,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr \n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n\tWHERE\n\t pm.proje [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Figure 3 - Median Lead Time for Changes",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background-solid",
+ "filterable": true
+ },
+ "mappings": [
+ {
+ "options": {
+ "This Project": {
+ "color": "green",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "merged_date"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-background-solid"
+ },
+ {
+ "id": "color"
+ },
+ {
+ "id": "thresholds",
+ "value": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 0
+ }
+ ]
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "select_status"
+ },
+ "properties": [
+ {
+ "id": "custom.displayMode",
+ "value": "color-background-solid"
+ },
+ {
+ "id": "mappings",
+ "value": [
+ {
+ "options": {
+ "This project is selected": {
+ "color": "green",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ]
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 20,
+ "w": 8,
+ "x": 0,
+ "y": 32
+ },
+ "id": 51,
+ "options": {
+ "showHeader": true
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\n pm.project_name,\n\tIF(pm.project_name in ($project),'This project is selected','Not Selected') as select_status,\n\t-- pr.status,\n\tpr.title,\n -- \tpr.author_name,\n\tpr.url,\n\tpr.merged_date,\n\tpr.created_date\nFROM\n\tpull_requests pr \n\tjoin project_mapping pm on pr.base_repo_id = pm.row_id AND pm.`table`='repos'\nWHERE\n -- pm.project_name in ($project)\n\t$__timeFilter(pr.created_date)\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "All PRs in this project (Only the rows with 2 green columns should appear in project_pr_metrics)",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 254, 254, 1)",
+ "mode": "fixed"
+ },
+ "mappings": [
+ {
+ "options": {
+ "from": 1,
+ "result": {
+ "color": "green",
+ "index": 0
+ },
+ "to": 10000000
+ },
+ "type": "range"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 32
+ },
+ "id": 12,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "select \n count(distinct pr.id) as 'No. of merged PRs in table.pull_requests'\nfrom \n pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\nwhere \n pm.project_name in ($project)\n and pr.merged_date is not null\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ },
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select \n count(distinct id) as 'No. of PRs in table.project_pr_metrics'\nfrom \n project_pr_metrics \nwhere \n project_name in ($project)",
+ "refId": "B",
+ "select": [
+ [
+ {
+ "params": [
+ "blueprint_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprint_labels",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 2. Check if the number of MERGED PRs of this project are the same between table pull_requests and proejct_pr_metrics",
+ "type": "gauge"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 37
+ },
+ "id": 52,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _pr_commit_ranks as(\n select \n pr.id,\n pr.created_date as pr_created_date,\n prc.commit_sha,\n prc.commit_authored_date,\n row_number() over(partition by pr.id order by prc.commit_authored_date asc) as commit_rank\n from \n pull_requests pr\n left join pull_request_commits prc on pr.id = prc.pull_request_id\n where pr.id = '$pr_id'\n)\n\nselect \n id,\n -- commit_sha as first_commit_sha,\n -- [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ },
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select \n id, \n -- first_commit_sha,\n pr_coding_time as 'PR coding time from project_pr_metrics'\nfrom project_pr_metrics\nwhere id = '$pr_id'",
+ "refId": "B",
+ "select": [
+ [
+ {
+ "params": [
+ "blueprint_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprint_labels",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 3. Check if PR coding time in table.project_pr_metrics is accurate (Adopt the Pull Request filter above)",
+ "type": "gauge"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 42
+ },
+ "id": 54,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _pr_comment_ranks as(\n select \n pr.id as pr_id,\n pr.created_date as pr_created_date,\n prc.id as review_id,\n prc.created_date as review_created_date,\n row_number() over(partition by pr.id order by prc.created_date asc) as comment_rank\n from \n pull_requests pr\n left join pull_request_comments prc on pr.id = prc.pull_request_id\n where \n pr.id = '$pr_id'\n and prc.account_id!=pr.autho [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ },
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select \n id, \n -- first_review_id,\n pr_pickup_time as 'PR pickup time from project_pr_metrics'\nfrom project_pr_metrics\nwhere \n id = '$pr_id'\n and project_name in ($project)\n",
+ "refId": "B",
+ "select": [
+ [
+ {
+ "params": [
+ "blueprint_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprint_labels",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 4. Check if PR pickup time in table.project_pr_metrics is accurate (Adopt the Pull Request filter above)",
+ "type": "gauge"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Hours",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 15,
+ "w": 8,
+ "x": 16,
+ "y": 42
+ },
+ "id": 38,
+ "options": {
+ "barWidth": 0.7,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {},
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "-- Metric 2: median change lead time per month\nwith _pr_stats as (\n-- get the cycle time of PRs deployed by the deployments finished each month\n\tSELECT\n\t\tdistinct pr.id,\n\t\tdate_format(cdc.finished_date,'%y/%m') as month,\n\t\tppm.pr_cycle_time\n\tFROM\n\t\tpull_requests pr\n\t\tjoin project_pr_metrics ppm on ppm.id = pr.id\n\t\tjoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.`table` = 'repos'\n\t\tjoin cicd_deployment_commits cdc on ppm.deploy [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "Figure 4 - Median Lead Time for Changes",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 47
+ },
+ "id": 55,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "last"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "with _pr_comment_ranks as(\n select \n pr.id as pr_id,\n pr.merged_date as pr_merged_date,\n prc.id as review_id,\n prc.created_date as review_created_date,\n row_number() over(partition by pr.id order by prc.created_date asc) as comment_rank_asc\n from \n pull_requests pr\n left join pull_request_comments prc on pr.id = prc.pull_request_id\n where \n pr.id = '$pr_id'\n and prc.account_id!=pr.aut [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ },
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select \n id, \n -- first_review_id,\n pr_review_time as 'PR review time from project_pr_metrics'\nfrom project_pr_metrics\nwhere \n id = '$pr_id'\n and project_name in ($project)\n",
+ "refId": "B",
+ "select": [
+ [
+ {
+ "params": [
+ "blueprint_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprint_labels",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 5. Check if PR review time in table.project_pr_metrics is accurate (Adopt the Pull Request filter above)",
+ "type": "gauge"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 0,
+ "y": 52
+ },
+ "id": 56,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "last"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "select \n ppm.id as pr_id,\n ppm.deployment_commit_id,\n CEILING(TIMESTAMPDIFF(second,pr.merged_date,cdc.finished_date)/60) as 'PR deploy time from cicd_deployment_commits'\nfrom \n project_pr_metrics ppm\n left join cicd_deployment_commits cdc on ppm.deployment_commit_id = cdc.id\n left join pull_requests pr on ppm.id = pr.id\nwhere \n project_name in ($project)\n and cdc.result = 'SUCCESS'\n and cdc.`environment` = 'PRODUCTION'\n and ppm. [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ },
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select \n id, \n pr_deploy_time as 'PR deploy time from project_pr_metrics'\nfrom project_pr_metrics\nwhere \n id = '$pr_id'\n and project_name in ($project)\n",
+ "refId": "B",
+ "select": [
+ [
+ {
+ "params": [
+ "blueprint_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_blueprint_labels",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 6. Check if PR deploy time in table.project_pr_metrics is accurate (Adopt the Pull Request filter above)",
+ "type": "gauge"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 8,
+ "x": 8,
+ "y": 52
+ },
+ "id": 57,
+ "options": {
+ "reduceOptions": {
+ "calcs": [
+ "last"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "select \n ppm.id,\n (pr_coding_time + CEILING(TIMESTAMPDIFF(second,pr.created_date,pr.merged_date)/60) + pr_deploy_time) as 'PR cycle time from lower-level metrics',\n ppm.`pr_cycle_time` as 'PR cycle time from project_pr_metrics'\nfrom project_pr_metrics ppm\nleft join pull_requests pr on ppm.id = pr.id\nwhere \n project_name in ($project)\n and pr.id = '$pr_id'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 7. Check if PR cycle time in table.project_pr_metrics is accurate (Adopt the Pull Request filter above)",
+ "type": "gauge"
+ },
+ {
+ "collapsed": false,
+ "datasource": null,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 57
+ },
+ "id": 26,
+ "panels": [],
+ "title": "Change Failure Rate & Median Time to Restore Service",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background"
+ },
+ "mappings": [
+ {
+ "options": {
+ "INCIDENT": {
+ "color": "green",
+ "index": 0
+ },
+ "This project is selected": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 0,
+ "y": 58
+ },
+ "id": 31,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "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 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 1. All types of issues in table.issues (rows with 2 green columns will be used to construct project_issue_metrics)",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "Between one day and one week": {
+ "color": "yellow",
+ "index": 1
+ },
+ "Less than one day": {
+ "color": "green",
+ "index": 2
+ },
+ "Less than one hour": {
+ "color": "purple",
+ "index": 3
+ },
+ "More than one week": {
+ "color": "red",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 10,
+ "w": 8,
+ "x": 16,
+ "y": 58
+ },
+ "id": 42,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^median_time_to_resolve$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: Median time to restore service \nwith _incidents as (\n-- get the incidents created within the selected time period in the top-right corner\n\tSELECT\n\t distinct i.id,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name in ($project [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Figure 5 - Median Time to Restore Service",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background"
+ },
+ "mappings": [
+ {
+ "options": {
+ "INCIDENT": {
+ "color": "green",
+ "index": 0
+ },
+ "This project is selected": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 4,
+ "w": 16,
+ "x": 0,
+ "y": 64
+ },
+ "id": 14,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "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 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 2. Number of Incidents in the selected project(s)",
+ "type": "table"
+ },
+ {
+ "datasource": null,
+ "gridPos": {
+ "h": 15,
+ "w": 16,
+ "x": 0,
+ "y": 68
+ },
+ "id": 61,
+ "options": {
+ "content": "<img src = 'https://devlake.apache.org/assets/images/cfr-definition-94d92cc75f857f183443ad5390d31d65.png' />\n\nIn this case:\n\n- Deployment-1 maps to Incident-1\n- Deployment-3 maps to Incident-2 and Incident-3\n- Deployment-2,4,5 doesn't map to any Incident",
+ "mode": "markdown"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "queryType": "randomWalk",
+ "refId": "A"
+ }
+ ],
+ "title": "Deployment - Incident Mapping and CFR calculation logic",
+ "type": "text"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Hours",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "median_time_to_resolve_in_hour"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "blue",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 10,
+ "w": 8,
+ "x": 16,
+ "y": 68
+ },
+ "id": 46,
+ "options": {
+ "barWidth": 0.6,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {},
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: median time to restore service - MTTR\nwith _incidents as (\n-- get the number of incidents created each month\n\tSELECT\n\t distinct i.id,\n\t\tdate_format(i.created_date,'%y/%m') as month,\n\t\tcast(lead_time_minutes as signed) as lead_time_minutes\n\tFROM\n\t\tissues i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id and pm.`table` = 'boards'\n\tWHERE\n\t pm.pro [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "Figure 6 - Median Time to Restore Service",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "0-15%": {
+ "color": "purple",
+ "index": 3
+ },
+ "16%-20%": {
+ "color": "green",
+ "index": 2
+ },
+ "21%-30%": {
+ "color": "yellow",
+ "index": 1
+ },
+ "> 30%": {
+ "color": "red",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 8,
+ "x": 16,
+ "y": 78
+ },
+ "id": 44,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^change_failure_rate$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "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\tJOIN proje [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Figure 7 - Change Failure Rate",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background-solid",
+ "filterable": true
+ },
+ "mappings": [
+ {
+ "options": {
+ "DEPLOYMENT": {
+ "color": "green",
+ "index": 0
+ },
+ "INCIDENT": {
+ "color": "red",
+ "index": 1
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 13,
+ "w": 8,
+ "x": 0,
+ "y": 83
+ },
+ "id": 58,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "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 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 3. The sequence of DEPLOYMENTS and INCIDENTS",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "fixedColor": "rgba(255, 255, 255, 1)",
+ "mode": "fixed"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "color-background-solid",
+ "filterable": true
+ },
+ "mappings": [
+ {
+ "options": {
+ "FALSE": {
+ "color": "green",
+ "index": 1
+ },
+ "TRUE": {
+ "color": "red",
+ "index": 0
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 13,
+ "w": 8,
+ "x": 8,
+ "y": 83
+ },
+ "id": 59,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "select \n -- in CFR we use deployment_commit_id as the deployment_id in a specific repo\n cdc.id as deployment_id,\n cdc.finished_date,\n pim.id as incident_id,\n if (pim.id is not null, 'TRUE', 'FALSE') as has_failure\nfrom \n cicd_deployment_commits cdc\n left join project_issue_metrics pim on cdc.id = pim.deployment_id\n left join project_mapping pm on cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\nwhere \n pm.project_name in ($project)\n [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Step 4. Check if the DEPLOYMENT and INCIDENT mapping results are consistent with them in step 3 and figure 7&8",
+ "type": "table"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "max": 1,
+ "min": 0,
+ "thresholds": {
+ "mode": "percentage",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "change_failure_rate"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "blue",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 10,
+ "w": 8,
+ "x": 16,
+ "y": 86
+ },
+ "id": 48,
+ "options": {
+ "barWidth": 0.6,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {},
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "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 [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "Figure 8 - Change Failure Rate",
+ "type": "barchart"
+ }
+ ],
+ "refresh": "",
+ "schemaVersion": 30,
+ "style": "dark",
+ "tags": [],
+ "templating": {
+ "list": [
+ {
+ "allValue": null,
+ "current": {
+ "selected": false,
+ "text": "All",
+ "value": "$__all"
+ },
+ "datasource": "mysql",
+ "definition": "select distinct name from projects",
+ "description": null,
+ "error": null,
+ "hide": 0,
+ "includeAll": true,
+ "label": "Project",
+ "multi": true,
+ "name": "project",
+ "options": [],
+ "query": "select distinct name from projects",
+ "refresh": 1,
+ "regex": "",
+ "skipUrlSync": false,
+ "sort": 0,
+ "type": "query"
+ },
+ {
+ "allValue": null,
+ "current": {
+ "selected": false,
+ "text": "https://bitbucket.org/zhenmianws/helloworldrepo/pull-requests/1",
+ "value": "bitbucket:BitbucketPullRequest:1:zhenmianws/helloworldrepo:1"
+ },
+ "datasource": "mysql",
+ "definition": "select concat(Url, '--', id) from pull_requests",
+ "description": null,
+ "error": null,
+ "hide": 0,
+ "includeAll": false,
+ "label": "Pull Request Url",
+ "multi": false,
+ "name": "pr_id",
+ "options": [],
+ "query": "select concat(Url, '--', id) from pull_requests",
+ "refresh": 1,
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
+ "skipUrlSync": false,
+ "sort": 0,
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {},
+ "timezone": "",
+ "title": "DORA Dashboard Validation",
+ "uid": "KGkUnV-Vz",
+ "version": 45
+}
\ No newline at end of file