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 <[email protected]>
AuthorDate: Fri May 12 16:23:17 2023 +0800
fix: update DORA validation dashboard (#5172)
Co-authored-by: Startrekzky <[email protected]>
---
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
