You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@devlake.apache.org by "Bhoopalan1999 (via GitHub)" <gi...@apache.org> on 2025/09/13 08:10:03 UTC
[PR] pr-type/feature-development: Jira Sprint Board (Grafana Dashboard) [incubator-devlake]
Bhoopalan1999 opened a new pull request, #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574
Adds a new Grafana dashboard for Jira that is sprint-based rather than time-based. Some of the features:
Hides the global time picker
Introduces a sprint selection dropdown to scope all panels Does not modify the existing Kanban (time-based) dashboard
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
### ⚠️ Pre Checklist
> Please complete _ALL_ items in this checklist, and remove before submitting
- [ ] I have read through the [Contributing Documentation](https://devlake.apache.org/community/).
- [ ] I have added relevant tests.
- [ ] I have added relevant documentation.
- [ ] I will add labels to the PR, such as `pr-type/bug-fix`, `pr-type/feature-development`, etc.
<!--
Thanks for submitting a pull request!
We appreciate you spending the time to work on these changes.
Please fill out as many sections below as possible.
-->
### Summary
What does this PR do?
The PR adds a new Grafana dashboard for Jira. While the existing Jira dashboard is used for time based Kanban boards, this PR contributes an additional Jira board which is used for sprint boards. The board doesn't have time picker. Instead, I have added sprint dropdown. All tiles correspond to the sprint dropdown.
### Screenshots
**Sprint dropdown with 'Multiple selection' and 'All' option. Time picker is removed**
<img width="958" height="46" alt="image" src="https://github.com/user-attachments/assets/1d965ff5-83eb-4f89-a402-c78eaf684aa8" />
**Entire Dashboard screenshot**
<img width="941" height="356" alt="image" src="https://github.com/user-attachments/assets/85039e58-3c15-459d-b5c3-079e556b4235" />
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat(grafana): jira sprint based dashboards [incubator-devlake]
Posted by "Startrekzky (via GitHub)" <gi...@apache.org>.
Startrekzky commented on code in PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#discussion_r2435959394
##########
grafana/JiraSprintBoard.json:
##########
@@ -0,0 +1,1058 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 9,
+ "graphTooltip": 0,
+ "id": 153,
+ "links": [
+ {
+ "asDropdown": false,
+ "icon": "external link",
+ "includeVars": false,
+ "keepTime": true,
+ "tags": [
+ "Data Source Specific Dashboard"
+ ],
+ "targetBlank": false,
+ "title": "Metric dashboards",
+ "tooltip": "",
+ "type": "dashboards",
+ "url": ""
+ }
+ ],
+ "panels": [
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 126,
+ "panels": [],
+ "title": "1. Issue Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": {
Review Comment:
You should replace all occurences of
```
"datasource": {
"type": "mysql",
"uid": "cey4ufm78rw8wf"
},
```
with
```
"datasource": "mysql",
```
Otherwise, the dashboard can not be displayed properly.
##########
grafana/JiraSprintBoard.json:
##########
@@ -0,0 +1,1058 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 9,
+ "graphTooltip": 0,
+ "id": 153,
+ "links": [
+ {
+ "asDropdown": false,
+ "icon": "external link",
+ "includeVars": false,
+ "keepTime": true,
+ "tags": [
+ "Data Source Specific Dashboard"
+ ],
+ "targetBlank": false,
+ "title": "Metric dashboards",
+ "tooltip": "",
+ "type": "dashboards",
+ "url": ""
+ }
+ ],
+ "panels": [
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 126,
+ "panels": [],
+ "title": "1. Issue Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "1. Total number of issues created.\n2. The requirements being calculated are filtered by \"requirement creation time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 1
+ },
+ "id": 140,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Issues [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 1
+ },
+ "id": 141,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Delivered Issue [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 1
+ },
+ "id": 145,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tCOUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END) AS number_of_delivered_issues,\r\n\tCOUNT(DISTINCT CASE WHEN i.status != 'DONE' THEN i.id END) AS number_of_open_issues,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issue Status Distribution over Sprint",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "Issue Delivery Rate = count(Delivered Issues)/count(Issues)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "max": 1,
+ "min": 0,
+ "thresholds": {
+ "mode": "percentage",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 50
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 8,
+ "x": 0,
+ "y": 7
+ },
+ "id": 117,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Requirement Delivery Rate",
+ "url": "https://devlake.apache.org/docs/Metrics/RequirementDeliveryRate"
+ }
+ ],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n NOW() AS time,\r\n (COUNT(DISTINCT(CASE WHEN i.status = 'Done' THEN i.id END))) / (COUNT(DISTINCT(i.id)))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
Review Comment:
The `Done` should be `DONE` as it's case sensitive, which explained why this stat was always 0%.
<img width="1791" height="1052" alt="image" src="https://github.com/user-attachments/assets/d902cd38-6e02-47ed-aed5-21a70db385e7" />
##########
grafana/JiraSprintBoard.json:
##########
@@ -0,0 +1,1058 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 9,
+ "graphTooltip": 0,
+ "id": 153,
+ "links": [
+ {
+ "asDropdown": false,
+ "icon": "external link",
+ "includeVars": false,
+ "keepTime": true,
+ "tags": [
+ "Data Source Specific Dashboard"
+ ],
+ "targetBlank": false,
+ "title": "Metric dashboards",
+ "tooltip": "",
+ "type": "dashboards",
+ "url": ""
+ }
+ ],
+ "panels": [
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 126,
+ "panels": [],
+ "title": "1. Issue Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "1. Total number of issues created.\n2. The requirements being calculated are filtered by \"requirement creation time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 1
+ },
+ "id": 140,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Issues [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 1
+ },
+ "id": 141,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Delivered Issue [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 1
+ },
+ "id": 145,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tCOUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END) AS number_of_delivered_issues,\r\n\tCOUNT(DISTINCT CASE WHEN i.status != 'DONE' THEN i.id END) AS number_of_open_issues,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issue Status Distribution over Sprint",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "Issue Delivery Rate = count(Delivered Issues)/count(Issues)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "max": 1,
+ "min": 0,
+ "thresholds": {
+ "mode": "percentage",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 50
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 8,
+ "x": 0,
+ "y": 7
+ },
+ "id": 117,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Requirement Delivery Rate",
+ "url": "https://devlake.apache.org/docs/Metrics/RequirementDeliveryRate"
+ }
+ ],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n NOW() AS time,\r\n (COUNT(DISTINCT(CASE WHEN i.status = 'Done' THEN i.id END))) / (COUNT(DISTINCT(i.id)))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Issue Delivery Rate [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 7
+ },
+ "id": 146,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t(COUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END))/(COUNT(DISTINCT i.id))*100 AS issues_delivered_percentage,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issues Delivered Over Sprint In Percentage",
+ "type": "barchart"
+ },
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 13
+ },
+ "id": 135,
+ "panels": [],
+ "title": "2. Issue Lead Time",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 14
+ },
+ "id": 137,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "mean"
+ ],
+ "fields": "/^value$/",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n avg(lead_time_minutes)/1440 AS value\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Mean Issue Lead Time in Days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 14
+ },
+ "id": 138,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\nAVG(CASE WHEN _rank <= 0.8 THEN lead_time_minutes END)/1440 AS value\r\nFROM (\r\n\tSELECT\r\n\t\tDISTINCT(issue_key),\r\n\t\tlead_time_minutes,\r\n\t\tPERCENT_RANK() OVER (ORDER BY lead_time_minutes) AS _rank\r\n\tFROM\r\n\t\tissues i\r\n\tJOIN\r\n\t\tsprint_issues si ON si.issue_id = i.id\r\n\tJOIN\t\r\n\t\tsprints sp ON si.sprint_id = sp.id\r\n\tWHERE\r\n\t\ti.type IN (${type})\r\n\t\tAND\r\n\t\tsp.id IN (${sprint_id})\r\n\t\tAND\r\n\t\ti.status = 'DONE'\r\n\t) mean_lead_time_der",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "80% Issues' Cycle Lead Time are less than # days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 14
+ },
+ "id": 148,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t\tDISTINCT(sp.name) AS sprint_name,\r\n avg(lead_time_minutes)/1440 AS mean_lead_time\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'\r\nGROUP BY sprint_name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Mean Cycle Lead Time Over Sprint",
+ "type": "barchart"
+ },
+ {
+ "fieldConfig": {
+ "defaults": {},
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 2,
+ "w": 24,
+ "x": 0,
+ "y": 20
+ },
+ "id": 130,
+ "options": {
+ "code": {
+ "language": "plaintext",
+ "showLineNumbers": false,
+ "showMiniMap": false
+ },
+ "content": "<br/>\n\nThis dashboard is created based on this [data schema](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema). Want to add more metrics? Please follow the [guide](https://devlake.apache.org/docs/Configuration/Dashboards/GrafanaUserGuide).",
+ "mode": "markdown"
+ },
+ "pluginVersion": "11.3.0",
+ "title": "",
+ "type": "text"
+ }
+ ],
+ "preload": false,
+ "refresh": "",
+ "schemaVersion": 40,
+ "tags": [],
+ "templating": {
+ "list": [
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "includeAll": true,
+ "label": "Board",
+ "multi": true,
+ "name": "board_id",
+ "options": [],
+ "query": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "refresh": 1,
+ "regex": "",
+ "sort": 1,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": "All",
+ "value": "$__all"
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "description": "",
+ "includeAll": true,
+ "label": "Sprint",
+ "multi": true,
+ "name": "sprint_id",
+ "options": [],
+ "query": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "refresh": 1,
+ "regex": "",
+ "sort": 3,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "definition": "select distinct type from issues",
+ "includeAll": true,
+ "label": "Issue Type",
+ "multi": true,
+ "name": "type",
+ "options": [],
+ "query": "select distinct type from issues",
+ "refresh": 1,
+ "regex": "",
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {
+ "hidden": true
+ },
+ "timezone": "Asia/Kolkata",
Review Comment:
Should be `utc`
<img width="324" height="229" alt="image" src="https://github.com/user-attachments/assets/3c52fd56-3039-4d33-b3d9-d7e91194a244" />
##########
grafana/JiraSprintBoard.json:
##########
@@ -0,0 +1,1058 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 9,
+ "graphTooltip": 0,
+ "id": 153,
+ "links": [
+ {
+ "asDropdown": false,
+ "icon": "external link",
+ "includeVars": false,
+ "keepTime": true,
+ "tags": [
+ "Data Source Specific Dashboard"
+ ],
+ "targetBlank": false,
+ "title": "Metric dashboards",
+ "tooltip": "",
+ "type": "dashboards",
+ "url": ""
+ }
+ ],
+ "panels": [
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 126,
+ "panels": [],
+ "title": "1. Issue Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "1. Total number of issues created.\n2. The requirements being calculated are filtered by \"requirement creation time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 1
+ },
+ "id": 140,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Issues [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 1
+ },
+ "id": 141,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Delivered Issue [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 1
+ },
+ "id": 145,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tCOUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END) AS number_of_delivered_issues,\r\n\tCOUNT(DISTINCT CASE WHEN i.status != 'DONE' THEN i.id END) AS number_of_open_issues,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issue Status Distribution over Sprint",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "Issue Delivery Rate = count(Delivered Issues)/count(Issues)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "max": 1,
+ "min": 0,
+ "thresholds": {
+ "mode": "percentage",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 50
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 8,
+ "x": 0,
+ "y": 7
+ },
+ "id": 117,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Requirement Delivery Rate",
+ "url": "https://devlake.apache.org/docs/Metrics/RequirementDeliveryRate"
+ }
+ ],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n NOW() AS time,\r\n (COUNT(DISTINCT(CASE WHEN i.status = 'Done' THEN i.id END))) / (COUNT(DISTINCT(i.id)))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Issue Delivery Rate [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 7
+ },
+ "id": 146,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t(COUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END))/(COUNT(DISTINCT i.id))*100 AS issues_delivered_percentage,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issues Delivered Over Sprint In Percentage",
+ "type": "barchart"
+ },
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 13
+ },
+ "id": 135,
+ "panels": [],
+ "title": "2. Issue Lead Time",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 14
+ },
+ "id": 137,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "mean"
+ ],
+ "fields": "/^value$/",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n avg(lead_time_minutes)/1440 AS value\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Mean Issue Lead Time in Days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 14
+ },
+ "id": 138,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\nAVG(CASE WHEN _rank <= 0.8 THEN lead_time_minutes END)/1440 AS value\r\nFROM (\r\n\tSELECT\r\n\t\tDISTINCT(issue_key),\r\n\t\tlead_time_minutes,\r\n\t\tPERCENT_RANK() OVER (ORDER BY lead_time_minutes) AS _rank\r\n\tFROM\r\n\t\tissues i\r\n\tJOIN\r\n\t\tsprint_issues si ON si.issue_id = i.id\r\n\tJOIN\t\r\n\t\tsprints sp ON si.sprint_id = sp.id\r\n\tWHERE\r\n\t\ti.type IN (${type})\r\n\t\tAND\r\n\t\tsp.id IN (${sprint_id})\r\n\t\tAND\r\n\t\ti.status = 'DONE'\r\n\t) mean_lead_time_der",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "80% Issues' Cycle Lead Time are less than # days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 14
+ },
+ "id": 148,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t\tDISTINCT(sp.name) AS sprint_name,\r\n avg(lead_time_minutes)/1440 AS mean_lead_time\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'\r\nGROUP BY sprint_name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Mean Cycle Lead Time Over Sprint",
+ "type": "barchart"
+ },
+ {
+ "fieldConfig": {
+ "defaults": {},
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 2,
+ "w": 24,
+ "x": 0,
+ "y": 20
+ },
+ "id": 130,
+ "options": {
+ "code": {
+ "language": "plaintext",
+ "showLineNumbers": false,
+ "showMiniMap": false
+ },
+ "content": "<br/>\n\nThis dashboard is created based on this [data schema](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema). Want to add more metrics? Please follow the [guide](https://devlake.apache.org/docs/Configuration/Dashboards/GrafanaUserGuide).",
+ "mode": "markdown"
+ },
+ "pluginVersion": "11.3.0",
+ "title": "",
+ "type": "text"
+ }
+ ],
+ "preload": false,
+ "refresh": "",
+ "schemaVersion": 40,
+ "tags": [],
+ "templating": {
+ "list": [
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "includeAll": true,
+ "label": "Board",
+ "multi": true,
+ "name": "board_id",
+ "options": [],
+ "query": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "refresh": 1,
+ "regex": "",
+ "sort": 1,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": "All",
+ "value": "$__all"
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "description": "",
+ "includeAll": true,
+ "label": "Sprint",
+ "multi": true,
+ "name": "sprint_id",
+ "options": [],
+ "query": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "refresh": 1,
+ "regex": "",
+ "sort": 3,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "definition": "select distinct type from issues",
+ "includeAll": true,
+ "label": "Issue Type",
+ "multi": true,
+ "name": "type",
+ "options": [],
+ "query": "select distinct type from issues",
+ "refresh": 1,
+ "regex": "",
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {
+ "hidden": true
Review Comment:
Did you hide the time filter on purpose?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat(grafana): jira sprint based dashboards [incubator-devlake]
Posted by "Bhoopalan1999 (via GitHub)" <gi...@apache.org>.
Bhoopalan1999 commented on code in PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#discussion_r2463876219
##########
grafana/JiraSprintBoard.json:
##########
@@ -0,0 +1,1058 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 9,
+ "graphTooltip": 0,
+ "id": 153,
+ "links": [
+ {
+ "asDropdown": false,
+ "icon": "external link",
+ "includeVars": false,
+ "keepTime": true,
+ "tags": [
+ "Data Source Specific Dashboard"
+ ],
+ "targetBlank": false,
+ "title": "Metric dashboards",
+ "tooltip": "",
+ "type": "dashboards",
+ "url": ""
+ }
+ ],
+ "panels": [
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 126,
+ "panels": [],
+ "title": "1. Issue Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "1. Total number of issues created.\n2. The requirements being calculated are filtered by \"requirement creation time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 1
+ },
+ "id": 140,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Issues [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 1
+ },
+ "id": 141,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tcount(distinct(i.id))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Number of Delivered Issue [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 1
+ },
+ "id": 145,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\tCOUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END) AS number_of_delivered_issues,\r\n\tCOUNT(DISTINCT CASE WHEN i.status != 'DONE' THEN i.id END) AS number_of_open_issues,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issue Status Distribution over Sprint",
+ "type": "barchart"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "Issue Delivery Rate = count(Delivered Issues)/count(Issues)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "max": 1,
+ "min": 0,
+ "thresholds": {
+ "mode": "percentage",
+ "steps": [
+ {
+ "color": "red",
+ "value": null
+ },
+ {
+ "color": "green",
+ "value": 50
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 8,
+ "x": 0,
+ "y": 7
+ },
+ "id": 117,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "Requirement Delivery Rate",
+ "url": "https://devlake.apache.org/docs/Metrics/RequirementDeliveryRate"
+ }
+ ],
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n NOW() AS time,\r\n (COUNT(DISTINCT(CASE WHEN i.status = 'Done' THEN i.id END))) / (COUNT(DISTINCT(i.id)))\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "value"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "timeColumn": "time",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Issue Delivery Rate [Issues Created in Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 7
+ },
+ "id": 146,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t(COUNT(DISTINCT CASE WHEN i.status = 'DONE' THEN i.id END))/(COUNT(DISTINCT i.id))*100 AS issues_delivered_percentage,\r\n sp.name AS sprint_name\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\nGROUP BY sp.name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Issues Delivered Over Sprint In Percentage",
+ "type": "barchart"
+ },
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 13
+ },
+ "id": 135,
+ "panels": [],
+ "title": "2. Issue Lead Time",
+ "type": "row"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 0,
+ "y": 14
+ },
+ "id": 137,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "mean"
+ ],
+ "fields": "/^value$/",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n avg(lead_time_minutes)/1440 AS value\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Mean Issue Lead Time in Days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "decimals": 1,
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 14
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 4,
+ "x": 4,
+ "y": 14
+ },
+ "id": 138,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "percentChangeColorMode": "standard",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showPercentChange": false,
+ "text": {},
+ "textMode": "auto",
+ "wideLayout": true
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\nAVG(CASE WHEN _rank <= 0.8 THEN lead_time_minutes END)/1440 AS value\r\nFROM (\r\n\tSELECT\r\n\t\tDISTINCT(issue_key),\r\n\t\tlead_time_minutes,\r\n\t\tPERCENT_RANK() OVER (ORDER BY lead_time_minutes) AS _rank\r\n\tFROM\r\n\t\tissues i\r\n\tJOIN\r\n\t\tsprint_issues si ON si.issue_id = i.id\r\n\tJOIN\t\r\n\t\tsprints sp ON si.sprint_id = sp.id\r\n\tWHERE\r\n\t\ti.type IN (${type})\r\n\t\tAND\r\n\t\tsp.id IN (${sprint_id})\r\n\t\tAND\r\n\t\ti.status = 'DONE'\r\n\t) mean_lead_time_der",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "80% Issues' Cycle Lead Time are less than # days [Issues Resolved in Select Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisBorderShow": false,
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 16,
+ "x": 8,
+ "y": 14
+ },
+ "id": 148,
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.97,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "normal",
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": -45,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "11.3.0",
+ "targets": [
+ {
+ "dataset": "devlakeschema",
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "SELECT\r\n\t\tDISTINCT(sp.name) AS sprint_name,\r\n avg(lead_time_minutes)/1440 AS mean_lead_time\r\nFROM\r\n\tissues i\r\nJOIN\r\n\tsprint_issues si ON si.issue_id = i.id\r\nJOIN\t\r\n\tsprints sp ON si.sprint_id = sp.id\r\nWHERE\r\n\ti.type IN (${type})\r\n\tAND\r\n\tsp.id IN (${sprint_id})\r\n\tAND\r\n\ti.status = 'DONE'\r\nGROUP BY sprint_name",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Mean Cycle Lead Time Over Sprint",
+ "type": "barchart"
+ },
+ {
+ "fieldConfig": {
+ "defaults": {},
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 2,
+ "w": 24,
+ "x": 0,
+ "y": 20
+ },
+ "id": 130,
+ "options": {
+ "code": {
+ "language": "plaintext",
+ "showLineNumbers": false,
+ "showMiniMap": false
+ },
+ "content": "<br/>\n\nThis dashboard is created based on this [data schema](https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema). Want to add more metrics? Please follow the [guide](https://devlake.apache.org/docs/Configuration/Dashboards/GrafanaUserGuide).",
+ "mode": "markdown"
+ },
+ "pluginVersion": "11.3.0",
+ "title": "",
+ "type": "text"
+ }
+ ],
+ "preload": false,
+ "refresh": "",
+ "schemaVersion": 40,
+ "tags": [],
+ "templating": {
+ "list": [
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "includeAll": true,
+ "label": "Board",
+ "multi": true,
+ "name": "board_id",
+ "options": [],
+ "query": "SELECT id AS __value, name AS __text\nFROM boards\nWHERE\nid like ('%jira%')",
+ "refresh": 1,
+ "regex": "",
+ "sort": 1,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": "All",
+ "value": "$__all"
+ },
+ "datasource": {
+ "type": "mysql",
+ "uid": "cey4ufm78rw8wf"
+ },
+ "definition": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "description": "",
+ "includeAll": true,
+ "label": "Sprint",
+ "multi": true,
+ "name": "sprint_id",
+ "options": [],
+ "query": "SELECT\ns.id AS __value,\ns.name AS __text\nFROM\nboards AS b JOIN\nboard_sprints AS bs JOIN\nsprints AS s\nON\nb.id = bs.board_id AND\ns.id = bs.sprint_id\nWHERE b.id IN (${board_id})",
+ "refresh": 1,
+ "regex": "",
+ "sort": 3,
+ "type": "query"
+ },
+ {
+ "current": {
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "definition": "select distinct type from issues",
+ "includeAll": true,
+ "label": "Issue Type",
+ "multi": true,
+ "name": "type",
+ "options": [],
+ "query": "select distinct type from issues",
+ "refresh": 1,
+ "regex": "",
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {
+ "hidden": true
Review Comment:
Yes, A time filter is not used in Sprint board. Hence removed it
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat(grafana): jira sprint based dashboards [incubator-devlake]
Posted by "Bhoopalan1999 (via GitHub)" <gi...@apache.org>.
Bhoopalan1999 commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-3448645388
Hello @Startrekzky
Thanks for the feedback. I've updated all the changes requested. Also, I've now moved the file. Previously, I've accidentally kept the file inside grafana folder directly instead of grafana/dashboards. That's fixed now. Please review.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat: Jira Sprint Board (Grafana Dashboard) [incubator-devlake]
Posted by "Startrekzky (via GitHub)" <gi...@apache.org>.
Startrekzky commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-3353065503
Hi @Bhoopalan1999 , I checked the dashboard and it's overall good.
1. My biggest question is why there is a `cycle time` when `Issue lead time` already exists? They're the same to me.
I checked the SQL and found you joined tables like `_tool_jira_issue_changelogs` and excluded specific issue types such as 'In Progress' and 'Developing'. This seems applicable only to your specific cases rather than a general approach. My suggestion is to remove this section if you want to get this merged to the main branch, which won't affect your ux, as you can still keep this section on your local dashboard.
<img width="2836" height="1094" alt="image" src="https://github.com/user-attachments/assets/46503f50-8092-44de-8b10-780e3987c29f" />
2. The number of overall issue delivery rate is not consistent with the rate by sprint
I didn't dig into it. Please take a look.
<img width="2868" height="1306" alt="image" src="https://github.com/user-attachments/assets/b710a9a9-eee8-4a77-8bf0-6104575824fb" />
3. Please remove all status/type/... filters only applicable to your own use cases.
Take 'delivery rate' as an example, I checked the SQL and found that you added the condition to exclude 'cancelled' status. This is not recommended if you want to share this with other users.
<img width="2264" height="994" alt="image" src="https://github.com/user-attachments/assets/54cccbbd-b561-41e2-bb93-2fba01914879" />
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat(grafana): jira sprint based dashboards [incubator-devlake]
Posted by "danielemoraschi (via GitHub)" <gi...@apache.org>.
danielemoraschi commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-4238108923
Hi,
Just recently came across this thread and wanted to share how I have been reporting Sprints in my instance.
A key difference in my approach is that I use `issue_changelogs` to reconstruct each issue's status and story points as they were at sprint end, rather than using `i.status` (the issue's current status). Without that, an issue completed after the sprint closed still shows as "delivered" for that sprint, which can skew delivery rates over time. I also track scope changes, issues added or removed mid-sprint, using the Sprint field changelog.
Full query below, if of any help.
You might have to adapt filters as needed.
```sql
WITH sprint_data AS (
-- Get sprint info and explicitly filter to the current Board loop
SELECT DISTINCT
s.id AS sprint_id,
s.name AS sprint_name,
s.started_date,
s.completed_date
FROM sprints s
JOIN boards b ON s.original_board_id = b.id
WHERE
s.name REGEXP CONCAT('^', '${Sprints:regex}')
AND $__timeFilter(s.started_date)
-- This restricts the sprints to the board currently being rendered
AND (b.name IN ("${Boards}") OR "${Boards}" = '')
),
sprint_issues_base AS (
SELECT
j.id AS issue_id,
j.issue_key,
j.status AS current_status,
j.story_point AS current_story_points,
j.original_type,
j.assignee_name,
j.original_project,
sd.sprint_id,
sd.sprint_name,
sd.started_date,
sd.completed_date
FROM issues j
JOIN sprint_issues spi ON spi.issue_id = j.id
JOIN sprint_data sd ON spi.sprint_id = sd.sprint_id
LEFT JOIN board_issues boi ON boi.issue_id = j.id
LEFT JOIN boards bo ON bo.id = boi.board_id
WHERE j.original_type NOT IN ('Epic', 'Sub-task')
AND (j.assignee_name IN ($Person) OR $Person = '')
AND (bo.name IN ("${Boards}") OR "${Boards}" = '')
AND j.original_project = "${JiraProjects}"
),
added_to_sprint AS (
SELECT
sib.issue_id,
sib.sprint_id,
sib.issue_key,
sib.current_status,
sib.current_story_points,
TIMESTAMP(sib.started_date) as time,
sib.sprint_name,
sib.started_date,
sib.completed_date,
-- Find the first changelog entry where this sprint was added to the issue
(SELECT MIN(icl.created_date)
FROM issue_changelogs icl
WHERE icl.issue_id = sib.issue_id
AND icl.field_name = 'Sprint'
AND icl.original_to_value LIKE CONCAT('%', sib.sprint_id, '%')
-- And the sprint was NOT in the "from" value (i.e., it was actually added, not just moved)
AND (icl.original_from_value NOT LIKE CONCAT('%', sib.sprint_id, '%') OR icl.original_from_value IS NULL)
) AS added_date,
NULL as removed_date
FROM sprint_issues_base sib
),
removed_from_sprint AS (
SELECT
j.id AS issue_id,
sd.sprint_id,
j.issue_key,
j.status AS current_status,
j.story_point AS current_story_points,
TIMESTAMP(sd.started_date) as time,
sd.sprint_name,
sd.started_date,
sd.completed_date,
NULL as added_date,
icl.created_date AS removed_date
FROM issues j
JOIN issue_changelogs icl ON icl.issue_id = j.id
JOIN sprint_data sd ON 1=1
LEFT JOIN board_issues boi ON boi.issue_id = j.id
LEFT JOIN boards bo ON bo.id = boi.board_id
WHERE
-- Sprint field change
icl.field_name = 'Sprint'
-- Was in this sprint (from value contains sprint ID)
AND (icl.original_from_value LIKE CONCAT('%', sd.sprint_id, '%') OR icl.original_to_value IS NULL)
-- Was removed from this sprint (to value does NOT contain sprint ID or is null)
AND (icl.original_to_value NOT LIKE CONCAT('%', sd.sprint_id, '%') OR icl.original_to_value IS NULL)
-- Removal happened during the sprint window
AND icl.created_date >= sd.started_date
AND (icl.created_date <= sd.completed_date OR sd.completed_date IS NULL)
-- Filters
AND j.original_type NOT IN ('Epic', 'Sub-task')
AND (bo.name IN ("${Boards}") OR "${Boards}" = '')
AND j.original_project = "${JiraProjects}"
-- Ensure issue is NOT currently in the sprint (wasn't re-added)
AND NOT EXISTS (
SELECT 1 FROM sprint_issues spi
WHERE spi.issue_id = j.id AND spi.sprint_id = sd.sprint_id
)
),
all_sprint_issues AS (
SELECT
asp.* FROM added_to_sprint asp
UNION ALL
SELECT
rsp.* FROM removed_from_sprint rsp
),
status_at_sprint_end AS (
SELECT
asi.*,
-- Get the most recent status BEFORE or AT the reference date
(SELECT icl.to_value
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'status'
AND icl.created_date <= asi.completed_date
ORDER BY icl.created_date DESC
LIMIT 1
) AS last_status_before_sprint,
-- Get the FIRST status change ever (to determine initial state)
(SELECT icl.from_value
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'status'
ORDER BY icl.created_date ASC
LIMIT 1
) AS initial_status
FROM all_sprint_issues asi
),
status_at_sprint_end_final AS (
SELECT
sase.*,
COALESCE(last_status_before_sprint, initial_status, "TODO") as status_in_sprint
FROM status_at_sprint_end sase
),
story_points_at_sprint_end AS (
SELECT
asi.*,
COALESCE(
-- If there was a change AFTER reference date, get the "from" value of the earliest change
(SELECT CAST(icl.original_to_value AS DECIMAL(10,1))
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'Story Points'
AND icl.created_date <= asi.completed_date
ORDER BY icl.created_date DESC
LIMIT 1),
(SELECT
CASE
WHEN icl.original_from_value IS NULL OR TRIM(icl.original_from_value) = ''
THEN 0 -- Story points were NULL at sprint end (ticket had no points initially)
ELSE CAST(icl.original_from_value AS DECIMAL(10,1)) -- Use the from_value as initial
END
FROM issue_changelogs icl
WHERE icl.issue_id = asi.issue_id
AND icl.field_name = 'Story Points'
ORDER BY icl.created_date ASC
LIMIT 1),
asi.current_story_points
) AS historical_story_points
FROM status_at_sprint_end_final asi
)
SELECT * FROM story_points_at_sprint_end ORDER BY started_date ASC
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat: Jira Sprint Board (Grafana Dashboard) [incubator-devlake]
Posted by "Bhoopalan1999 (via GitHub)" <gi...@apache.org>.
Bhoopalan1999 commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-3374750949
Hello @Startrekzky
Thanks for the feedback.
**1. Difference between Cycle Lead Time & Issue Lead Time:**
**Cycle time** calculates how long has it taken for an issue to be resolved from the time it was moved to 'In Progress' or 'Developing'. That's why you see only these two Jira status selected in the sql query.
**Issue lead time**, on the other hand, calculates the entire lifetime of a specific Jira issue (from the time of the issue creation -> till it is marked as 'delivered'). Considering this is a sprint board, I thought having both would be useful for sprint board users as most of my teams who opted for sprint board needed Cycle time. I shall remove it and update you so that it is consistent with existing Jira Kanban board.
**2. The number of overall issue delivery rate is not consistent with the rate by sprint**
It's working in my dashboards. I'll troubleshoot and fix it and raise another PR.
**3. Please remove all status/type/... filters only applicable to your own use cases.**
Sure. I'll remove that and update in the next PR.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] feat: Jira Sprint Board (Grafana Dashboard) [incubator-devlake]
Posted by "Bhoopalan1999 (via GitHub)" <gi...@apache.org>.
Bhoopalan1999 commented on PR #8574:
URL: https://github.com/apache/incubator-devlake/pull/8574#issuecomment-3375125558
I've made the changes in the new commit:
Fixed:
1. My biggest question is why there is a cycle time when Issue lead time already exists? They're the same to me. - Removed them
2. The number of overall issue delivery rate is not consistent with the rate by sprint - The possible reason is that issue_type was a custom variable in my dashboard and it is a query variable in existing Jira dashboard. Fixed it. Working fine in my board.
3. Please remove all status/type/... filters only applicable to your own use cases - Removed that in all tiles of the jira sprint dashboard
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org