This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch add-recovery-time-metric2
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
The following commit(s) were added to refs/heads/add-recovery-time-metric2 by
this push:
new f7f9a759f feat: add failed deployment recovery time metric
f7f9a759f is described below
commit f7f9a759f853d26a64d532504465bb7badd9e1cc
Author: abeizn <[email protected]>
AuthorDate: Mon Feb 19 16:47:54 2024 +0800
feat: add failed deployment recovery time metric
---
...mark.go => 20240219_add_dora_2023_benchmark.go} | 36 +-
grafana/dashboards/DORA.json | 399 +++++++++++++++++++--
grafana/dashboards/DORAByTeam.json | 317 ++++++++++++++--
3 files changed, 670 insertions(+), 82 deletions(-)
diff --git
a/backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
b/backend/plugins/dora/models/migrationscripts/20240219_add_dora_2023_benchmark.go
similarity index 92%
rename from
backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
rename to
backend/plugins/dora/models/migrationscripts/20240219_add_dora_2023_benchmark.go
index adaece2ba..1e3ac263b 100644
---
a/backend/plugins/dora/models/migrationscripts/20240207_add_dora_2023_benchmark.go
+++
b/backend/plugins/dora/models/migrationscripts/20240219_add_dora_2023_benchmark.go
@@ -149,10 +149,26 @@ func (u *adddoraBenchmark2023) Up(baseRes
context.BasicRes) errors.Error {
return errors.Convert(err)
}
- doraBenchmark2023TTS := &doraBenchmark2023{
+ doraBenchmark2023CFR := &doraBenchmark2023{
Model: archived.Model{
ID: 7,
},
+ Metric: "Change failure rate",
+ Low: "> 15%(low)",
+ Medium: "10%-15%(medium)",
+ High: "5%-10%(high)",
+ Elite: "0-5%(elite)",
+ Benchmarks: "2023 report",
+ }
+ err = db.Create(doraBenchmark2023CFR)
+ if err != nil {
+ return errors.Convert(err)
+ }
+
+ doraBenchmark2023TTS := &doraBenchmark2023{
+ Model: archived.Model{
+ ID: 8,
+ },
Metric: "Time to restore service",
Low: "More than one week(low)",
Medium: "Between one day and one week(medium)",
@@ -165,18 +181,18 @@ func (u *adddoraBenchmark2023) Up(baseRes
context.BasicRes) errors.Error {
return errors.Convert(err)
}
- doraBenchmark2023CFR := &doraBenchmark2023{
+ doraBenchmark2023FDRT := &doraBenchmark2023{
Model: archived.Model{
- ID: 8,
+ ID: 9,
},
- Metric: "Change failure rate",
- Low: "> 15%(low)",
- Medium: "10%-15%(medium)",
- High: "5%-10%(high)",
- Elite: "0-5%(elite)",
+ Metric: "Failed deployment recovery time",
+ Low: "More than one week(low)",
+ Medium: "Between one day and one week(medium)",
+ High: "Less than one day(high)",
+ Elite: "Less than one hour(elite)",
Benchmarks: "2023 report",
}
- err = db.Create(doraBenchmark2023CFR)
+ err = db.Create(doraBenchmark2023FDRT)
if err != nil {
return errors.Convert(err)
}
@@ -185,7 +201,7 @@ func (u *adddoraBenchmark2023) Up(baseRes context.BasicRes)
errors.Error {
}
func (*adddoraBenchmark2023) Version() uint64 {
- return 20240207000002
+ return 20240219000002
}
func (*adddoraBenchmark2023) Name() string {
diff --git a/grafana/dashboards/DORA.json b/grafana/dashboards/DORA.json
index 0c77ec165..c1939fd0d 100644
--- a/grafana/dashboards/DORA.json
+++ b/grafana/dashboards/DORA.json
@@ -18,7 +18,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 34,
+ "id": 35,
"links": [],
"liveNow": false,
"panels": [
@@ -175,7 +175,7 @@
]
},
"gridPos": {
- "h": 6,
+ "h": 7,
"w": 24,
"x": 0,
"y": 6
@@ -205,7 +205,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": [
[
@@ -312,9 +312,9 @@
},
"gridPos": {
"h": 5,
- "w": 6,
+ "w": 4,
"x": 0,
- "y": 12
+ "y": 13
},
"id": 11,
"links": [],
@@ -450,9 +450,9 @@
},
"gridPos": {
"h": 5,
- "w": 6,
- "x": 6,
- "y": 12
+ "w": 4,
+ "x": 4,
+ "y": 13
},
"id": 12,
"links": [],
@@ -525,6 +525,144 @@
"title": "Median Lead Time for Changes",
"type": "stat"
},
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "pattern": ".*elite.*",
+ "result": {
+ "color": "purple",
+ "index": 0
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
+ "index": 3
+ }
+ },
+ "type": "regex"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 4,
+ "x": 8,
+ "y": 13
+ },
+ "id": 14,
+ "links": [],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^change_failure_rate$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\tJOIN proje [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "_devlake_tasks",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Change Failure Rate",
+ "type": "stat"
+ },
{
"datasource": "mysql",
"fieldConfig": {
@@ -594,9 +732,9 @@
"h": 5,
"w": 6,
"x": 12,
- "y": 12
+ "y": 13
},
- "id": 13,
+ "id": 17,
"links": [],
"options": {
"colorMode": "value",
@@ -623,7 +761,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 and pm.`table` = 'boards'\n\tWHERE\n\t pm.project_name
in (${projec [...]
+ "rawSql": "-- Metric 4: 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 (${projec [...]
"refId": "A",
"select": [
[
@@ -722,6 +860,10 @@
{
"color": "green",
"value": null
+ },
+ {
+ "color": "red",
+ "value": 80
}
]
}
@@ -732,9 +874,9 @@
"h": 5,
"w": 6,
"x": 18,
- "y": 12
+ "y": 13
},
- "id": 14,
+ "id": 13,
"links": [],
"options": {
"colorMode": "value",
@@ -745,7 +887,7 @@
"calcs": [
"lastNotNull"
],
- "fields": "/^change_failure_rate$/",
+ "fields": "/^median_recovery_time$/",
"values": false
},
"text": {},
@@ -761,7 +903,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 5: Failed deployment recovery time\nwith
_deployments as (\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM \n cicd_deployment_commits cdc\n JOIN project_mapping pm on
cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n WHERE\n
pm.project_name in ($project)\n and cdc.result = 'SUCCESS'\n and
cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -802,7 +944,7 @@
]
}
],
- "title": "Change Failure Rate",
+ "title": "Failed Deployment Recovery Time",
"type": "stat"
},
{
@@ -852,9 +994,9 @@
},
"gridPos": {
"h": 8,
- "w": 12,
+ "w": 8,
"x": 0,
- "y": 17
+ "y": 18
},
"id": 2,
"links": [],
@@ -959,9 +1101,9 @@
},
"gridPos": {
"h": 8,
- "w": 12,
- "x": 12,
- "y": 17
+ "w": 8,
+ "x": 8,
+ "y": 18
},
"id": 6,
"links": [],
@@ -1023,6 +1165,153 @@
"title": "Median Lead Time for Changes",
"type": "barchart"
},
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "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": 8,
+ "w": 8,
+ "x": 16,
+ "y": 18
+ },
+ "id": 5,
+ "links": [],
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.6,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "none",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": 0,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Change Failure Rate",
+ "type": "barchart"
+ },
{
"datasource": "mysql",
"description": "",
@@ -1089,7 +1378,7 @@
"h": 8,
"w": 12,
"x": 0,
- "y": 25
+ "y": 26
},
"id": 9,
"links": [],
@@ -1119,12 +1408,13 @@
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"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 [...]
+ "rawSql": "-- Metric 4: 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": [
[
@@ -1136,6 +1426,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "ae_projects",
"timeColumn": "ae_create_time",
"timeColumnType": "timestamp",
@@ -1162,7 +1469,7 @@
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
- "axisLabel": "",
+ "axisLabel": "Hours",
"axisPlacement": "auto",
"axisSoftMin": 0,
"fillOpacity": 80,
@@ -1181,23 +1488,25 @@
}
},
"mappings": [],
- "max": 1,
- "min": 0,
"thresholds": {
- "mode": "percentage",
+ "mode": "absolute",
"steps": [
{
"color": "green"
+ },
+ {
+ "color": "red",
+ "value": 80
}
]
},
- "unit": "percentunit"
+ "unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
- "options": "change_failure_rate"
+ "options": "median_recovery_time_in_hour"
},
"properties": [
{
@@ -1215,9 +1524,9 @@
"h": 8,
"w": 12,
"x": 12,
- "y": 25
+ "y": 26
},
- "id": 5,
+ "id": 18,
"links": [],
"options": {
"barRadius": 0,
@@ -1233,9 +1542,7 @@
"orientation": "auto",
"showValue": "auto",
"stacking": "none",
- "text": {
- "valueSize": 12
- },
+ "text": {},
"tooltip": {
"mode": "single",
"sort": "none"
@@ -1247,12 +1554,13 @@
"targets": [
{
"datasource": "mysql",
+ "editorMode": "code",
"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 [...]
+ "rawSql": "-- Metric 5: Failed deployment recovery time\nwith
_deployments as (\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM \n cicd_deployment_commits cdc\n JOIN project_mapping pm on
cdc.cicd_scope_id = pm.row_id and pm.`table` = 'cicd_scopes'\n WHERE\n
pm.project_name in ($project)\n and cdc.result = 'SUCCESS'\n and
cdc.environment = 'PRODUCTION'\n [...]
"refId": "A",
"select": [
[
@@ -1264,6 +1572,23 @@
}
]
],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
"table": "ae_projects",
"timeColumn": "ae_create_time",
"timeColumnType": "timestamp",
@@ -1276,7 +1601,7 @@
]
}
],
- "title": "Change Failure Rate",
+ "title": "Failed Deployment Recovery Time",
"type": "barchart"
}
],
@@ -1345,6 +1670,6 @@
"timezone": "",
"title": "DORA",
"uid": "qNo8_0M4z",
- "version": 4,
+ "version": 8,
"weekStart": ""
}
\ No newline at end of file
diff --git a/grafana/dashboards/DORAByTeam.json
b/grafana/dashboards/DORAByTeam.json
index 87143c079..be40256d6 100644
--- a/grafana/dashboards/DORAByTeam.json
+++ b/grafana/dashboards/DORAByTeam.json
@@ -24,7 +24,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 35,
+ "id": 14,
"links": [],
"liveNow": false,
"panels": [
@@ -180,7 +180,7 @@
]
},
"gridPos": {
- "h": 6,
+ "h": 7,
"w": 24,
"x": 0,
"y": 7
@@ -208,7 +208,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 1: Deployment Frequency\nwith
last_few_calendar_months as(\n-- construct the last few calendar months within
the selected time period in the top-right corner\n\tSELECT
CAST((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",
"sql": {
"columns": [
@@ -295,9 +295,9 @@
},
"gridPos": {
"h": 5,
- "w": 6,
+ "w": 4,
"x": 0,
- "y": 13
+ "y": 14
},
"id": 11,
"links": [],
@@ -433,9 +433,9 @@
},
"gridPos": {
"h": 5,
- "w": 6,
- "x": 6,
- "y": 13
+ "w": 4,
+ "x": 4,
+ "y": 14
},
"id": 12,
"links": [],
@@ -486,6 +486,122 @@
"title": "Median Lead Time for Changes",
"type": "stat"
},
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [
+ {
+ "options": {
+ "pattern": ".*elite.*",
+ "result": {
+ "color": "purple",
+ "index": 0
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*high.*",
+ "result": {
+ "color": "green",
+ "index": 1
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*medium.*",
+ "result": {
+ "color": "yellow",
+ "index": 2
+ }
+ },
+ "type": "regex"
+ },
+ {
+ "options": {
+ "pattern": ".*low.*",
+ "result": {
+ "color": "red",
+ "index": 3
+ }
+ },
+ "type": "regex"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 4,
+ "x": 8,
+ "y": 14
+ },
+ "id": 14,
+ "links": [],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "/^change_failure_rate$/",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "hide": false,
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t JOIN com [...]
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Change Failure Rate",
+ "type": "stat"
+ },
{
"datasource": "mysql",
"fieldConfig": {
@@ -555,7 +671,7 @@
"h": 5,
"w": 6,
"x": 12,
- "y": 13
+ "y": 14
},
"id": 13,
"links": [],
@@ -582,7 +698,7 @@
"format": "table",
"hide": false,
"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\t join user_accounts ua on
i.assignee_i [...]
+ "rawSql": "-- Metric 4: 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\t join user_accounts ua on
i.assignee_i [...]
"refId": "A",
"sql": {
"columns": [
@@ -661,6 +777,10 @@
{
"color": "green",
"value": null
+ },
+ {
+ "color": "red",
+ "value": 80
}
]
}
@@ -671,9 +791,9 @@
"h": 5,
"w": 6,
"x": 18,
- "y": 13
+ "y": 14
},
- "id": 14,
+ "id": 17,
"links": [],
"options": {
"colorMode": "value",
@@ -684,7 +804,7 @@
"calcs": [
"lastNotNull"
],
- "fields": "/^change_failure_rate$/",
+ "fields": "/^median_recovery_time$/",
"values": false
},
"text": {},
@@ -698,7 +818,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate\nwith _deployments as
(\n-- When deploying multiple commits in one pipeline, GitLab and BitBucket may
generate more than one deployment. However, DevLake consider these deployments
as ONE production deployment and use the last one's finished_date as the
finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t JOIN com [...]
+ "rawSql": "-- Metric 5: Failed deployment recovery time\nwith
_deployments as (\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM \n cicd_deployment_commits cdc\n JOIN commits c on
cdc.commit_sha = c.sha\n join user_accounts ua on c.author_id =
ua.account_id\n join users u on ua.user_id = u.id\n join
team_users tu on u.id = tu.user_id\n joi [...]
"refId": "A",
"sql": {
"columns": [
@@ -719,7 +839,7 @@
}
}
],
- "title": "Change Failure Rate",
+ "title": "Failed Deployment Recovery Time",
"type": "stat"
},
{
@@ -769,9 +889,9 @@
},
"gridPos": {
"h": 8,
- "w": 12,
+ "w": 8,
"x": 0,
- "y": 18
+ "y": 19
},
"id": 2,
"links": [],
@@ -872,9 +992,9 @@
},
"gridPos": {
"h": 8,
- "w": 12,
- "x": 12,
- "y": 18
+ "w": 8,
+ "x": 8,
+ "y": 19
},
"id": 6,
"links": [],
@@ -932,6 +1052,129 @@
"title": "Median Lead Time for Changes",
"type": "barchart"
},
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "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": 8,
+ "w": 8,
+ "x": 16,
+ "y": 19
+ },
+ "id": 5,
+ "links": [],
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.6,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "none",
+ "text": {},
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": 0,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "hide": false,
+ "rawQuery": true,
+ "rawSql": "-- Metric 3: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Change Failure Rate",
+ "type": "barchart"
+ },
{
"datasource": "mysql",
"description": "",
@@ -966,7 +1209,8 @@
"mode": "absolute",
"steps": [
{
- "color": "green"
+ "color": "green",
+ "value": null
},
{
"color": "red",
@@ -998,7 +1242,7 @@
"h": 8,
"w": 12,
"x": 0,
- "y": 26
+ "y": 27
},
"id": 9,
"links": [],
@@ -1032,7 +1276,7 @@
"format": "table",
"hide": false,
"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\t join user_accou [...]
+ "rawSql": "-- Metric 4: 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\t join user_accou [...]
"refId": "A",
"sql": {
"columns": [
@@ -1067,7 +1311,7 @@
"custom": {
"axisCenteredZero": false,
"axisColorMode": "text",
- "axisLabel": "",
+ "axisLabel": "Hours",
"axisPlacement": "auto",
"axisSoftMin": 0,
"fillOpacity": 80,
@@ -1086,23 +1330,26 @@
}
},
"mappings": [],
- "max": 1,
- "min": 0,
"thresholds": {
- "mode": "percentage",
+ "mode": "absolute",
"steps": [
{
- "color": "green"
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
}
]
},
- "unit": "percentunit"
+ "unit": "none"
},
"overrides": [
{
"matcher": {
"id": "byName",
- "options": "change_failure_rate"
+ "options": "median_recovery_time_in_hour"
},
"properties": [
{
@@ -1120,9 +1367,9 @@
"h": 8,
"w": 12,
"x": 12,
- "y": 26
+ "y": 27
},
- "id": 5,
+ "id": 18,
"links": [],
"options": {
"barRadius": 0,
@@ -1154,7 +1401,7 @@
"format": "table",
"hide": false,
"rawQuery": true,
- "rawSql": "-- Metric 4: change failure rate per month\nwith
_deployments as (\n-- When deploying multiple commits in one pipeline, GitLab
and BitBucket may generate more than one deployment. However, DevLake consider
these deployments as ONE production deployment and use the last one's
finished_date as the finished date.\n\tSELECT\n\t\tcdc.cicd_deployment_id as
deployment_id,\n\t\tmax(cdc.finished_date) as deployment_finished_date\n\tFROM
\n\t\tcicd_deployment_commits cdc\n\t\t [...]
+ "rawSql": "-- Metric 5: Failed deployment recovery time\nwith
_deployments as (\n SELECT\n cdc.cicd_deployment_id as
deployment_id,\n max(cdc.finished_date) as deployment_finished_date\n
FROM \n cicd_deployment_commits cdc\n JOIN commits c on
cdc.commit_sha = c.sha\n join user_accounts ua on c.author_id =
ua.account_id\n join users u on ua.user_id = u.id\n join
team_users tu on u.id = tu.user_id\n joi [...]
"refId": "A",
"sql": {
"columns": [
@@ -1175,7 +1422,7 @@
}
}
],
- "title": "Change Failure Rate",
+ "title": "Failed Deployment Recovery Time",
"type": "barchart"
}
],
@@ -1239,6 +1486,6 @@
"timezone": "",
"title": "DORA (by Team)",
"uid": "66YkL8y4z",
- "version": 3,
+ "version": 7,
"weekStart": ""
}
\ No newline at end of file
