Merge pull request #1715 from Startrekzky/dashboard
feat: update github and jenkins dashboards
diff --git a/grafana/_archive/Jira.json b/grafana/_archive/Jira.json
deleted file mode 100644
index 9a3191d..0000000
--- a/grafana/_archive/Jira.json
+++ /dev/null
@@ -1,3292 +0,0 @@
-{
- "annotations": {
- "list": [
- {
- "builtIn": 1,
- "datasource": "-- Grafana --",
- "enable": true,
- "hide": true,
- "iconColor": "rgba(0, 211, 255, 1)",
- "name": "Annotations & Alerts",
- "type": "dashboard"
- }
- ]
- },
- "editable": true,
- "gnetId": null,
- "graphTooltip": 0,
- "id": 7,
- "iteration": 1638292472238,
- "links": [
- {
- "asDropdown": false,
- "icon": "bolt",
- "includeVars": false,
- "keepTime": true,
- "tags": [],
- "targetBlank": false,
- "title": "Homepage",
- "tooltip": "",
- "type": "link",
- "url": "/d/Lv1XbLHnk/data-specific-dashboards-homepage"
- },
- {
- "asDropdown": false,
- "icon": "external link",
- "includeVars": false,
- "keepTime": true,
- "tags": [
- "Data Source Specific Dashboard"
- ],
- "targetBlank": false,
- "title": "Metric dashboards",
- "tooltip": "",
- "type": "dashboards",
- "url": ""
- }
- ],
- "panels": [
- {
- "datasource": null,
- "gridPos": {
- "h": 4,
- "w": 24,
- "x": 0,
- "y": 0
- },
- "id": 92,
- "options": {
- "content": "<div style=\"display: block;text-align: center;margin-top: 56px;\">\n <div style=\"display: inline-flex;\">\n <img src=\"/public/img/lake/1.png\" alt=\"No.1\" width=\"56\">\n <p style=\"font-size:24px; margin:10px; color:#BFC1C8;\"><b>Requirement Metrics<b></b></b></p><b><b>\n </div>\n</div>",
- "mode": "html"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "queryType": "randomWalk",
- "refId": "A"
- }
- ],
- "transparent": true,
- "type": "text"
- },
- {
- "datasource": "mysql",
- "description": "1. The average lead time of requirements.\n2. Lead time refers to the length of time from requirement creation to delivery.\n3. The requirements being calculated are filtered by \"requirement resolution time\" (time filter at the upper-right corner) and \"Jira board\"(\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 14
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 4
- },
- "id": 12,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "mean"
- ],
- "fields": "/^value$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "select \r\n now() as time,\r\n avg(lead_time_minutes/1440) as value\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Requirement'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\ngroup by 1;",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Avg Requirement Lead Time (day)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The lead time at which 80% requirements‘ lead time lies below it.\n2. The requirements being calculated are filtered by \"requirement resolution time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 21
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 6,
- "y": 4
- },
- "id": 13,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n ji.lead_time_minutes,\r\n percent_rank() over (order by lead_time_minutes asc) as ranks\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect\r\n now() as time,\r\n max(lead_time_minutes/1440) as value\r\nfrom _ranks\r\nwhere \r\n ranks <= 0.8\r\ngroup by 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "80th Percentile Requirement Lead Time (day)",
- "type": "stat"
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "1. The average requirement lead time over time.\n2. The time granularity can be switched to Week or Month by \"Time Interval\" above. \n3. When Time Interval is set to \"Month\", the average_lead_time of \"2021-06-01\" refers to the average lead time of requirements whose resolution time falls under [2020-06-01, 2020-07-01).",
- "fieldConfig": {
- "defaults": {
- "unit": "short"
- },
- "overrides": []
- },
- "fill": 1,
- "fillGradient": 0,
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 12,
- "y": 4
- },
- "hiddenSeries": false,
- "id": 17,
- "interval": "",
- "legend": {
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "show": true,
- "total": false,
- "values": false
- },
- "lines": true,
- "linewidth": 1,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": true
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 2,
- "points": false,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select \r\n DATE_ADD(date(resolution_date), INTERVAL -$interval(date(resolution_date))+1 DAY) as time,\r\n lead_time_minutes\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n timestamp(time) as time,\r\n avg(lead_time_minutes/1440) as average_lead_time\r\nfrom _requirement\r\ngroup by 1\r\norder by 1 asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Requirement Lead Time over Time",
- "tooltip": {
- "shared": true,
- "sort": 0,
- "value_type": "individual"
- },
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "time",
- "name": null,
- "show": true,
- "values": []
- },
- "yaxes": [
- {
- "$$hashKey": "object:242",
- "format": "short",
- "label": "Lead Time(day)",
- "logBase": 1,
- "max": null,
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:243",
- "format": "short",
- "label": "",
- "logBase": 1,
- "max": null,
- "min": null,
- "show": true
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "1. The cumulative distribution of requirement lead time. \n2. Each point refers to the percent rank of a lead time.",
- "fill": 0,
- "fillGradient": 4,
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 10
- },
- "hiddenSeries": false,
- "id": 15,
- "legend": {
- "alignAsTable": false,
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "rightSide": false,
- "show": false,
- "total": false,
- "values": false
- },
- "lines": true,
- "linewidth": 8,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": false
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 0.5,
- "points": false,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n round(ji.lead_time_minutes/1440) as lead_time_day\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n order by lead_time_day asc\r\n)\r\n\r\nselect \r\n now() as time,\r\n lpad(concat(lead_time_day,'d'), 4, ' ') as metric,\r\n percent_rank() over (order by lead_time_day asc) as value\r\nfrom _ranks\r\norder by lead_time_day asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [
- {
- "$$hashKey": "object:469",
- "colorMode": "ok",
- "fill": true,
- "line": true,
- "op": "lt",
- "value": 0.8,
- "yaxis": "right"
- }
- ],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Requirement Lead Time (Cumulative Distribution)",
- "tooltip": {
- "shared": false,
- "sort": 0,
- "value_type": "individual"
- },
- "transformations": [],
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "series",
- "name": null,
- "show": true,
- "values": [
- "current"
- ]
- },
- "yaxes": [
- {
- "$$hashKey": "object:76",
- "format": "percentunit",
- "label": "Percent Rank (%)",
- "logBase": 1,
- "max": "1.2",
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:77",
- "format": "short",
- "label": null,
- "logBase": 1,
- "max": null,
- "min": null,
- "show": false
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "datasource": "mysql",
- "description": "The average lead time of requirements under different Jira Epics.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Lead Time (day)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 16
- },
- "id": 18,
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n ji.issue_id,\r\n epic_key,\r\n ji.lead_time_minutes\r\n from\r\n jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n ji.std_type = 'Requirement'\r\n and ji.std_status = 'Resolved'\r\n and ji.epic_key is not null\r\n and ji.epic_key != ''\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n now() as time,\r\n epic_key as \"Epic Key\",\r\n avg(lead_time_minutes/1440) as value\r\nfrom _requirement\r\ngroup by 1,2\r\norder by 3 desc\r\nlimit 20",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Requirement Lead Time by Epic",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "The average time spent in each state of a requirement, including all history states that have been used.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Lead Time(day)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 74,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 22
- },
- "id": 97,
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with operation_history as(\n\tSELECT\n\t\tjc.issue_id,\n\t\tjc.created as operation_time,\n\t\tjci.changelog_id,\n\t\tjci.from,\n\t\tjci.from_string,\n\t\tjci.to,\n\t\tjci.to_string,\n\t\tji.created as issue_creation_time,\n\t\tcase \n\t\t\twhen lag(jc.created) over(PARTITION BY jc.issue_id ORDER BY jc.created ASC) is not null then\n\t\t\tTIMESTAMPDIFF(MINUTE, lag(jc.created) over(PARTITION BY jc.issue_id ORDER BY jc.created ASC), jc.created)\n\t\t\telse TIMESTAMPDIFF(MINUTE, ji.created, jc.created) end as time_in_status\n\tFROM\n\t\tjira_changelogs jc\n\t\tJOIN jira_changelog_items jci ON jc.changelog_id = jci.changelog_id\n\t\tJOIN jira_issues ji ON jc.issue_id = ji.issue_id\n\t\tjoin jira_board_issues jbi on ji.issue_id = jbi.issue_id\n\tWHERE\n\t\tjci.field = 'status'\n\t\tand ji.std_type = 'Requirement'\n\t\tand ji.std_status = 'Resolved'\n\t\tand $__timeFilter(ji.resolution_date)\n and jbi.board_id = $board_id\n)\n\nSELECT\n now() as time,\n\tfrom_string as \"Requirement Status\",\n\tavg(time_in_status)/1440 as \"Time in Status\"\nFROM operation_history\nGROUP BY 1,2\nORDER BY 3 desc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Requirement Lead Time by Status",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "1. Compare the average time spent in each state of a requirement in the last 2 months.\n2. The requirements being calculated are the requirements delivered in the last 2 months.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Lead Time(day)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 74,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "short"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 28
- },
- "id": 98,
- "options": {
- "barWidth": 0.9,
- "groupWidth": 0.6,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with status_change_history as(\n\tSELECT\n\t\tjc.issue_id,\n\t\tjc.created as operation_time,\n\t\tjci.changelog_id,\n\t\tjci.from,\n\t\tjci.from_string,\n\t\tjci.to,\n\t\tjci.to_string,\n\t\tji.created as issue_creation_time,\n\t\tlag(jc.created) over(PARTITION BY jc.issue_id ORDER BY jc.created ASC) as last_operation_time,\n\t\tcase \n\t\t\twhen lag(jc.created) over(PARTITION BY jc.issue_id ORDER BY jc.created ASC) is not null then\n\t\t\tTIMESTAMPDIFF(MINUTE, lag(jc.created) over(PARTITION BY jc.issue_id ORDER BY jc.created ASC), jc.created)\n\t\t\telse TIMESTAMPDIFF(MINUTE, ji.created, jc.created) end as time_in_status\n\tFROM\n\t\tjira_changelogs jc\n\t\tJOIN jira_changelog_items jci ON jc.changelog_id = jci.changelog_id\n\t\tJOIN jira_issues ji ON jc.issue_id = ji.issue_id\n\t\tjoin jira_board_issues jbi on ji.issue_id = jbi.issue_id\n\tWHERE\n\t\tjci.field = 'status'\n\t\tand ji.std_type = 'Requirement'\n\t\tand ji.std_status = 'Resolved'\n\t\tand $__timeFilter(ji.resolution_date)\n and jbi.board_id = $board_id\n),\n\ntime_in_status as(\n\tSELECT \n\t\tDATE_ADD(date(issue_creation_time), INTERVAL -DAY(date(issue_creation_time))+1 DAY) as time,\n\t\tfrom_string,\n\t\tavg(time_in_status)/60 as time_in_status\n\tFROM status_change_history\n\tGROUP BY 1,2\n),\n\nlast_month as(\n\tselect \n\t\tDATE_ADD(date(CURDATE()), INTERVAL -DAY(date(CURDATE()))+1 DAY) as last_month\n),\n\nmonth_before_last_month as(\n\tSELECT \n\t\tDATE_ADD(DATE_ADD(date(CURDATE()), INTERVAL -DAY(date(CURDATE()))+1 DAY), INTERVAL -1 MONTH) as two_month_before\n),\n\n\nlast_month_record as(\n\tSELECT\n\t\tfrom_string as issue_status,\n\t\tcase when time in (SELECT last_month from last_month) then time_in_status else null end as last_month\n\tfrom time_in_status\n\tWHERE \n\t\ttime in (SELECT last_month from last_month)\n),\n\ntwo_month_before_record as(\n\tSELECT\n\t\tfrom_string as issue_status,\n\t\tcase when time in (SELECT two_month_before from month_before_last_month) then time_in_status else null end as two_month_before\n\tfrom time_in_status\n\tWHERE \n\t\ttime in (SELECT two_month_before from month_before_last_month)\n)\n\nSELECT\n\tnow() as time,\n\tmbr.two_month_before AS \"The Month Before Last\",\n\tlmr.last_month AS \"Last Month\",\n\tlmr.issue_status\nFROM last_month_record lmr\nleft join two_month_before_record mbr on lmr.issue_status = mbr.issue_status ",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Requirement Lead Time by Status in Last 2 Months",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "1. Total number of requirements 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": 6,
- "x": 0,
- "y": 34
- },
- "id": 2,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "select \r\n now() as time,\r\n count(*) as value\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Requirement'\r\n and created is not null \r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\ngroup by 1;",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Requirement Count",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The number of requirements created over time.\n2. The time granularity can be switched to week or month by \"Time Interval\" above. \n3. When Time Interval is set to \"month\", the requirement_count of \"2021-06-01\" refers to the number of requirements whose creation time falls under [2020-06-01, 2020-07-01)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "short"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 18,
- "x": 6,
- "y": 34
- },
- "id": 6,
- "interval": null,
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n ji.issue_id,\r\n DATE_ADD(date(created), INTERVAL -$interval(date(created))+1 DAY) as time\r\n from\r\n jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and created is not null \r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect\r\n timestamp(time) as time,\r\n count(distinct issue_id) as requirement_count\r\nfrom _requirement\r\ngroup by 1\r\norder by 1 asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Requirement Count over Time",
- "type": "timeseries"
- },
- {
- "datasource": "mysql",
- "description": "1. The number of requirements under different Jira Epics.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "No. of Requirement",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 40
- },
- "id": 7,
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n ji.issue_id,\r\n ji.epic_key\r\n from\r\n jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and created is not null\r\n and epic_key is not null\r\n and epic_key != ''\r\n and std_status = 'Resolved'\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n now() as time,\r\n epic_key as \"Epic Key\",\r\n count(*) as value\r\nfrom _requirement\r\ngroup by 1,2\r\norder by 3 desc\r\nlimit 20",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Requirement Count by Epic",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "1. Number of requirements that have been delivered.\n2. A requirement is delivered when its status equals 'delivered'.\n3. 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
- }
- ]
- },
- "unit": "none"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 46
- },
- "id": 25,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "select\r\n now() as time,\r\n count(*) as delivered_count\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Requirement'\r\n and ji.std_status = 'Resolved'\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Delivered Requirement Count",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "Delivered Requirement Count/Requirement Count (Total)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [],
- "thresholds": {
- "mode": "percentage",
- "steps": [
- {
- "color": "red",
- "value": null
- },
- {
- "color": "green",
- "value": 50
- }
- ]
- },
- "unit": "percentunit"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 6,
- "y": 46
- },
- "id": 22,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n count(distinct ji.issue_id) as total_count,\r\n count(distinct case when ji.std_status = 'Resolved' then ji.issue_id else null end) as delivered_count\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\n\r\nselect \r\n now() as time,\r\n 1.0 * delivered_count/total_count as requirement_delivery_rate\r\nfrom _requirement",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Requirement Delivery Rate",
- "type": "stat"
- },
- {
- "cacheTimeout": null,
- "datasource": "mysql",
- "description": "1. The requirement delivery rate over time.\n2. When Time Interval is set to \"month\", value \"requirement_delivery_rate\" of \"2021-06-01\" calculates the requirements whose creation time falls under [2020-06-01, 2020-07-01)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Delivery Rate (%)",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "percentunit"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 12,
- "y": 46
- },
- "id": 23,
- "links": [],
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n DATE_ADD(date(created), INTERVAL -$interval(date(created))+1 DAY) as time,\r\n count(distinct ji.issue_id) as total_count,\r\n count(distinct case when ji.std_status = 'Resolved' then ji.issue_id else null end) as delivered_count\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n group by 1\r\n)\r\n\r\n\r\nselect \r\n timestamp(time) as time,\r\n 1.0 * delivered_count/total_count as requirement_delivery_rate\r\nfrom _requirement\r\norder by 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Requirement Delivery Rate over Time",
- "type": "timeseries"
- },
- {
- "cacheTimeout": null,
- "datasource": "mysql",
- "description": "The average delivery rate of requirements under different Jira Epics.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Delivery Rate(%)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "percentunit"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 52
- },
- "id": 24,
- "links": [],
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select\r\n ji.epic_key as metric,\r\n count(distinct ji.issue_id) as total_count,\r\n count(distinct case when ji.std_status = 'Resolved' then ji.issue_id else null end) as delivered_count\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and epic_key is not null\r\n and epic_key != ''\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n group by 1\r\n)\r\n\r\n\r\nselect \r\n now() as time,\r\n metric,\r\n 1.0 * delivered_count/total_count as requirement_delivery_rate\r\nfrom _requirement\r\norder by 3 desc\r\nlimit 20",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Requirement Delivery Rate by Epic",
- "type": "barchart"
- },
- {
- "datasource": "mysql",
- "description": "1. The average story point (workload) of requirements.\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"
- },
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 58
- },
- "id": 93,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "select \r\n now() as time,\r\n avg(std_story_point) as value\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Requirement'\r\n and std_story_point != 0\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\ngroup by 1;",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Avg Requirement Granunarity(Story Point)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The story point/estimated hours at which 80% requirements' story point/estimated hours lies below it.\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": 6,
- "x": 6,
- "y": 58
- },
- "id": 94,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n std_story_point,\r\n percent_rank() over (order by std_story_point asc) as ranks\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_story_point != 0\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect\r\n now() as time,\r\n max(std_story_point) as value\r\nfrom _ranks\r\nwhere \r\n ranks <= 0.8\r\ngroup by 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "80th Percentile Requirement Granunarity(Story point)",
- "type": "stat"
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "The average and 80th percentile requirement granularity over time.",
- "fieldConfig": {
- "defaults": {
- "unit": "short"
- },
- "overrides": []
- },
- "fill": 1,
- "fillGradient": 0,
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 12,
- "y": 58
- },
- "hiddenSeries": false,
- "id": 96,
- "interval": "",
- "legend": {
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "show": true,
- "total": false,
- "values": false
- },
- "lines": true,
- "linewidth": 1,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": true
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 2,
- "points": false,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _requirement as(\r\n select \r\n ji.issue_id,\r\n DATE_ADD(date(resolution_date), INTERVAL -$interval(date(resolution_date))+1 DAY) as time,\r\n std_story_point\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_story_point != 0\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n timestamp(time) as time,\r\n avg(std_story_point) as average_story_point\r\nfrom _requirement\r\ngroup by 1\r\norder by 1 asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Requirement Granularity over Time",
- "tooltip": {
- "shared": true,
- "sort": 0,
- "value_type": "individual"
- },
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "time",
- "name": null,
- "show": true,
- "values": []
- },
- "yaxes": [
- {
- "$$hashKey": "object:242",
- "format": "short",
- "label": "Story Points",
- "logBase": 1,
- "max": null,
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:243",
- "format": "short",
- "label": "",
- "logBase": 1,
- "max": null,
- "min": null,
- "show": true
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "1. The cumulative distribution of requirement granularity.\n2. Each point refers to the percent rank of a value of granularity.",
- "fill": 0,
- "fillGradient": 4,
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 64
- },
- "hiddenSeries": false,
- "id": 95,
- "legend": {
- "alignAsTable": false,
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "rightSide": false,
- "show": false,
- "total": false,
- "values": false
- },
- "lines": false,
- "linewidth": 8,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": false
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 5,
- "points": true,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n round(std_story_point) as std_story_point\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Requirement'\r\n and std_story_point != 0\r\n and $__timeFilter(created)\r\n and jbi.board_id = $board_id\r\n order by std_story_point asc\r\n)\r\n\r\nselect \r\n now() as time,\r\n lpad(std_story_point, 4, ' ') as metric,\r\n percent_rank() over (order by std_story_point asc) as value\r\nfrom _ranks\r\norder by std_story_point asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [
- {
- "$$hashKey": "object:469",
- "colorMode": "ok",
- "fill": true,
- "line": true,
- "op": "lt",
- "value": 0.8,
- "yaxis": "right"
- }
- ],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Requirement Granularity (Cumulative Distribution)",
- "tooltip": {
- "shared": false,
- "sort": 0,
- "value_type": "individual"
- },
- "transformations": [],
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "series",
- "name": null,
- "show": true,
- "values": [
- "current"
- ]
- },
- "yaxes": [
- {
- "$$hashKey": "object:76",
- "format": "percentunit",
- "label": "Percent Rank (%)",
- "logBase": 1,
- "max": "1.2",
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:77",
- "format": "short",
- "label": null,
- "logBase": 1,
- "max": null,
- "min": null,
- "show": false
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "datasource": null,
- "gridPos": {
- "h": 4,
- "w": 24,
- "x": 0,
- "y": 70
- },
- "id": 77,
- "options": {
- "content": "<div style=\"display: block;text-align: center;margin-top: 56px;\">\n <div style=\"display: inline-flex;\">\n <img src=\"/public/img/lake/2.png\" alt=\"No.2\" width=\"56\">\n <p style=\"font-size:24px; margin:10px; color:#BFC1C8;\"><b>Bug Metrics<b></b></b></p><b><b>\n </div>\n</div>",
- "mode": "html"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "queryType": "randomWalk",
- "refId": "A"
- }
- ],
- "transparent": true,
- "type": "text"
- },
- {
- "datasource": "mysql",
- "description": "1. Total number of bugs created.\n2. The bugs being calculated are filtered by \"bug creation time\" (time filter at the upper-right corner)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 74
- },
- "id": 100,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n now() as time,\n count(*) as value\nFROM\n jira_issues ji\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\nWHERE\n std_type = 'Bug'\n and jbi.board_id = $board_id\n and $__timeFilter(created)\n",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Number of Bugs found in Testing",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. Number of bugs created over time.\n2. The time granularity can be switched to week or month by \"Time Interval\" above. \n3. When Time Interval is set to \"month\", bug_count of \"2021-06-01\" calculates the bugs whose creation time falls under [2020-06-01, 2020-07-01)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Bug Count",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- },
- "unit": "none"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 18,
- "x": 6,
- "y": 74
- },
- "id": 102,
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "hidden",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _bugs as (\n SELECT\n DATE_ADD(date(ji.created), INTERVAL -$interval(date(ji.created))+1 DAY) as time,\n count(distinct ji.issue_id) as bug_count\n FROM\n jira_issues ji\n LEFT JOIN jira_board_issues jbi ON jbi.issue_id = ji.issue_id\n WHERE\n std_type = 'Bug'\n and jbi.board_id = $board_id\n and $__timeFilter(ji.created)\n group by 1\n order by 1\n) \n\nselect \n timestamp(time) as time,\n bug_count\nfrom _bugs",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Number of Bugs found in Testing over Time",
- "type": "timeseries"
- },
- {
- "datasource": "mysql",
- "description": "1. The average bug age.\n2. Bug age refers to the length of time from bug creation to resolution.\n3. The bugs being calculated are filtered by \"bug resolution time\" (time filter at the upper-right corner) and \"Jira board\"(\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 7
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 80
- },
- "id": 53,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "mean"
- ],
- "fields": "/^value$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "select \r\n now() as time,\r\n avg(lead_time/1440) as value\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Bug'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\ngroup by 1;",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Bug Age (day)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The bug age at which 80% bugs‘ age lies below it.\n2. The bugs being calculated are filtered by \"bug resolution time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 14
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 6,
- "y": 80
- },
- "id": 65,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "mean"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n ji.lead_time,\r\n percent_rank() over (order by lead_time asc) as ranks\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Bug'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect\r\n now() as time,\r\n max(lead_time/1440) as value\r\nfrom _ranks\r\nwhere \r\n ranks <= 0.8\r\ngroup by 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "80th Percentile Bug Age (day)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The average and 80th percentile bug age over time.\n2. The time granularity can be switched to Week or Month by \"Time Interval\" above. \n3. When Time Interval is set to \"Month\", the average_bug_age of \"2021-06-01\" refers to the average age of bugs whose resolution time falls under [2020-06-01, 2020-07-01).",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Bug Age (day)",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "short"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 12,
- "y": 80
- },
- "id": 66,
- "interval": "",
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _bugs as(\r\n select \r\n ji.issue_id,\r\n DATE_ADD(date(resolution_date), INTERVAL -$interval(date(resolution_date))+1 DAY) as time,\r\n lead_time,\r\n round(lead_time/1440) as lead_time_day\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Bug'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n timestamp(time) as time,\r\n avg(lead_time/1440) as \"Average Lead Time\"\r\nfrom _bugs\r\ngroup by 1\r\norder by 1 asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Bug Age over Time",
- "type": "timeseries"
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "1. The cumulative distribution of bug age. \n2. Each point refers to the percent rank of a bug age.",
- "fieldConfig": {
- "defaults": {
- "unit": "percentunit"
- },
- "overrides": []
- },
- "fill": 0,
- "fillGradient": 4,
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 86
- },
- "hiddenSeries": false,
- "id": 61,
- "legend": {
- "alignAsTable": false,
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "rightSide": false,
- "show": false,
- "total": false,
- "values": false
- },
- "lines": true,
- "linewidth": 8,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": false
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 0.5,
- "points": false,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n round(ji.lead_time/1440) as lead_time_day\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Bug'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n order by lead_time_day asc\r\n)\r\n\r\nselect \r\n now() as time,\r\n lpad(concat(lead_time_day,'d'), 4, ' ') as metric,\r\n percent_rank() over (order by lead_time_day asc) as value\r\nfrom _ranks\r\norder by lead_time_day asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [
- {
- "$$hashKey": "object:912",
- "colorMode": "ok",
- "fill": true,
- "line": true,
- "op": "lt",
- "value": 0.8,
- "yaxis": "right"
- }
- ],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Bug Age (Cumulative Distribution)",
- "tooltip": {
- "shared": false,
- "sort": 0,
- "value_type": "individual"
- },
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "series",
- "name": null,
- "show": true,
- "values": [
- "total"
- ]
- },
- "yaxes": [
- {
- "$$hashKey": "object:76",
- "format": "percentunit",
- "label": "Percent Rank (%)",
- "logBase": 1,
- "max": "1.2",
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:77",
- "format": "short",
- "label": null,
- "logBase": 1,
- "max": null,
- "min": null,
- "show": false
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "datasource": "mysql",
- "description": "The average time spent on fixing bugs under different Jira Epics.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Bug Age(day)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 92
- },
- "id": 59,
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {},
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _bugs as(\r\n select\r\n ji.issue_id,\r\n ji.epic_key,\r\n ji.lead_time\r\n from\r\n jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Bug'\r\n and std_status = 'Resolved'\r\n and epic_key is not null\r\n and epic_key != ''\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n now() as time,\r\n epic_key as \"Epic Key\",\r\n avg(lead_time)/1440 as \"Bug Age\"\r\nfrom _bugs\r\ngroup by 1,2\r\norder by 3 desc\r\nlimit 20",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Bug Age by Epic",
- "type": "barchart"
- },
- {
- "datasource": null,
- "gridPos": {
- "h": 4,
- "w": 24,
- "x": 0,
- "y": 98
- },
- "id": 78,
- "options": {
- "content": "<div style=\"display: block;text-align: center;margin-top: 56px;\">\n <div style=\"display: inline-flex;\">\n <img src=\"/public/img/lake/3.png\" alt=\"No.3\" width=\"56\">\n <p style=\"font-size:24px; margin:10px; color:#BFC1C8;\"><b>Incident Metrics<b></b></b></p><b><b>\n </div>\n</div>",
- "mode": "html"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "queryType": "randomWalk",
- "refId": "A"
- }
- ],
- "transparent": true,
- "type": "text"
- },
- {
- "datasource": "mysql",
- "description": "1. Total number of incidents created.\n2. The incidents being calculated are filtered by \"incident creation time\" (time filter at the upper-right corner)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "thresholds"
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 102
- },
- "id": 104,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "lastNotNull"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "SELECT\n now() as time,\n count(*) as value\nFROM\n jira_issues ji\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\nWHERE\n std_type = 'Incident'\n and jbi.board_id = $board_id\n and $__timeFilter(created)\n",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "title": "Number of Incidents found after shipping",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. Number of incidents created over time.\n2. The time granularity can be switched to week or month by \"Time Interval\" above. \n3. When Time Interval is set to \"month\", incident_count of \"2021-06-01\" calculates the incidents whose creation time falls under [2020-06-01, 2020-07-01)",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Incident Count",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- },
- "unit": "none"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 18,
- "x": 6,
- "y": 102
- },
- "id": 106,
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "hidden",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _incidents as (\n SELECT\n DATE_ADD(date(ji.created), INTERVAL -$interval(date(ji.created))+1 DAY) as time,\n count(distinct ji.issue_id) as incident_count\n FROM\n jira_issues ji\n LEFT JOIN jira_board_issues jbi ON jbi.issue_id = ji.issue_id\n WHERE\n std_type = 'Incident'\n and jbi.board_id = $board_id\n and $__timeFilter(ji.created)\n group by 1\n order by 1\n) \n\nselect \n timestamp(time) as time,\n incident_count\nfrom _incidents",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Number of Incidents found after shipping over Time",
- "type": "timeseries"
- },
- {
- "datasource": "mysql",
- "description": "1. The average incident age.\n2. Incident age refers to the length of time from incident creation to resolution.\n3. The incidents being calculated are filtered by \"incident resolution time\" (time filter at the upper-right corner) and \"Jira board\"(\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "decimals": 1,
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 3
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 0,
- "y": 108
- },
- "id": 64,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "mean"
- ],
- "fields": "/^value$/",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "select \r\n now() as time,\r\n avg(lead_time/1440) as value\r\nfrom jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\nwhere \r\n std_type = 'Incident'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\ngroup by 1;",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Incident Age (day)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The incident age at which 80% incidents‘ age lies below it.\n2. The incidents being calculated are filtered by \"incident resolution time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
- "fieldConfig": {
- "defaults": {
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 7
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 6,
- "x": 6,
- "y": 108
- },
- "id": 55,
- "options": {
- "colorMode": "value",
- "graphMode": "area",
- "justifyMode": "auto",
- "orientation": "auto",
- "reduceOptions": {
- "calcs": [
- "mean"
- ],
- "fields": "",
- "values": false
- },
- "text": {},
- "textMode": "auto"
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n ji.lead_time,\r\n percent_rank() over (order by lead_time asc) as ranks\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Incident'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect\r\n now() as time,\r\n max(lead_time/1440) as value\r\nfrom _ranks\r\nwhere \r\n ranks <= 0.8\r\ngroup by 1",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "80th Percentile Incident Age (day)",
- "type": "stat"
- },
- {
- "datasource": "mysql",
- "description": "1. The average and 80th percentile incident age over time.\n2. The time granularity can be switched to Week or Month by \"Time Interval\" above. \n3. When Time Interval is set to \"Month\", the average_incident_age of \"2021-06-01\" refers to the average age of incidents whose resolution time falls under [2020-06-01, 2020-07-01).",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Incident Age (day)",
- "axisPlacement": "auto",
- "barAlignment": 0,
- "drawStyle": "line",
- "fillOpacity": 0,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineInterpolation": "linear",
- "lineWidth": 1,
- "pointSize": 5,
- "scaleDistribution": {
- "type": "linear"
- },
- "showPoints": "auto",
- "spanNulls": false,
- "stacking": {
- "group": "A",
- "mode": "none"
- },
- "thresholdsStyle": {
- "mode": "off"
- }
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- },
- {
- "color": "red",
- "value": 80
- }
- ]
- },
- "unit": "short"
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 12,
- "x": 12,
- "y": 108
- },
- "id": 57,
- "interval": "",
- "options": {
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _incidents as(\r\n select \r\n ji.issue_id,\r\n DATE_ADD(date(resolution_date), INTERVAL -$interval(date(resolution_date))+1 DAY) as time,\r\n lead_time,\r\n round(lead_time/1440) as lead_time_day\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Incident'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n timestamp(time) as time,\r\n avg(lead_time/1440) as \"Average Lead Time\"\r\nfrom _incidents\r\ngroup by 1\r\norder by 1 asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Incident Age over Time",
- "type": "timeseries"
- },
- {
- "aliasColors": {},
- "bars": false,
- "dashLength": 10,
- "dashes": false,
- "datasource": "mysql",
- "description": "1. The cumulative distribution of incident age.\n2. Each point refers to the percent rank of a incident age.",
- "fieldConfig": {
- "defaults": {
- "unit": "percentunit"
- },
- "overrides": []
- },
- "fill": 0,
- "fillGradient": 4,
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 114
- },
- "hiddenSeries": false,
- "id": 68,
- "legend": {
- "alignAsTable": false,
- "avg": false,
- "current": false,
- "max": false,
- "min": false,
- "rightSide": false,
- "show": false,
- "total": false,
- "values": false
- },
- "lines": true,
- "linewidth": 8,
- "nullPointMode": "null",
- "options": {
- "alertThreshold": false
- },
- "percentage": false,
- "pluginVersion": "8.0.6",
- "pointradius": 0.5,
- "points": false,
- "renderer": "flot",
- "seriesOverrides": [],
- "spaceLength": 10,
- "stack": false,
- "steppedLine": false,
- "targets": [
- {
- "format": "time_series",
- "group": [],
- "metricColumn": "none",
- "rawQuery": true,
- "rawSql": "with _ranks as(\r\n select \r\n round(ji.lead_time/1440) as lead_time_day\r\n from jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Incident'\r\n and std_status = 'Resolved'\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n order by lead_time_day asc\r\n)\r\n\r\nselect \r\n now() as time,\r\n lpad(concat(lead_time_day,'d'), 4, ' ') as metric,\r\n percent_rank() over (order by lead_time_day asc) as value\r\nfrom _ranks\r\norder by lead_time_day asc",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "progress"
- ],
- "type": "column"
- }
- ]
- ],
- "table": "ca_analysis",
- "timeColumn": "create_time",
- "timeColumnType": "timestamp",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "thresholds": [
- {
- "$$hashKey": "object:1070",
- "colorMode": "ok",
- "fill": true,
- "line": true,
- "op": "lt",
- "value": 0.8,
- "yaxis": "right"
- }
- ],
- "timeFrom": null,
- "timeRegions": [],
- "timeShift": null,
- "title": "Incident Age (Cumulative Distribution)",
- "tooltip": {
- "shared": false,
- "sort": 0,
- "value_type": "individual"
- },
- "type": "graph",
- "xaxis": {
- "buckets": null,
- "mode": "series",
- "name": null,
- "show": true,
- "values": [
- "total"
- ]
- },
- "yaxes": [
- {
- "$$hashKey": "object:76",
- "format": "percentunit",
- "label": "Percent Rank (%)",
- "logBase": 1,
- "max": "1.2",
- "min": null,
- "show": true
- },
- {
- "$$hashKey": "object:77",
- "format": "short",
- "label": null,
- "logBase": 1,
- "max": null,
- "min": null,
- "show": false
- }
- ],
- "yaxis": {
- "align": false,
- "alignLevel": null
- }
- },
- {
- "datasource": "mysql",
- "description": "The average time spent on fixing incidents under different Jira Epics.",
- "fieldConfig": {
- "defaults": {
- "color": {
- "mode": "palette-classic"
- },
- "custom": {
- "axisLabel": "Incident Age(day)",
- "axisPlacement": "auto",
- "axisSoftMin": 0,
- "fillOpacity": 80,
- "gradientMode": "none",
- "hideFrom": {
- "legend": false,
- "tooltip": false,
- "viz": false
- },
- "lineWidth": 1
- },
- "mappings": [],
- "thresholds": {
- "mode": "absolute",
- "steps": [
- {
- "color": "green",
- "value": null
- }
- ]
- }
- },
- "overrides": []
- },
- "gridPos": {
- "h": 6,
- "w": 24,
- "x": 0,
- "y": 120
- },
- "id": 67,
- "options": {
- "barWidth": 0.5,
- "groupWidth": 0.7,
- "legend": {
- "calcs": [],
- "displayMode": "list",
- "placement": "bottom"
- },
- "orientation": "auto",
- "showValue": "auto",
- "text": {
- "valueSize": 1
- },
- "tooltip": {
- "mode": "single"
- }
- },
- "pluginVersion": "8.0.6",
- "targets": [
- {
- "format": "table",
- "group": [],
- "metricColumn": "none",
- "queryType": "randomWalk",
- "rawQuery": true,
- "rawSql": "with _incidents as(\r\n select\r\n ji.issue_id,\r\n ji.epic_key,\r\n ji.lead_time\r\n from\r\n jira_issues ji\r\n join jira_board_issues jbi on ji.issue_id = jbi.issue_id\r\n where \r\n std_type = 'Incident'\r\n and std_status = 'Resolved'\r\n and epic_key is not null\r\n and epic_key != ''\r\n and $__timeFilter(resolution_date)\r\n and jbi.board_id = $board_id\r\n)\r\n\r\nselect \r\n now() as time,\r\n epic_key as \"Epic Key\",\r\n avg(lead_time)/1440 as \"Incident Age\"\r\nfrom _incidents\r\ngroup by 1,2\r\norder by 3 desc\r\nlimit 20",
- "refId": "A",
- "select": [
- [
- {
- "params": [
- "value"
- ],
- "type": "column"
- }
- ]
- ],
- "timeColumn": "time",
- "where": [
- {
- "name": "$__timeFilter",
- "params": [],
- "type": "macro"
- }
- ]
- }
- ],
- "timeFrom": null,
- "timeShift": null,
- "title": "Average Incident Age by Epic",
- "type": "barchart"
- }
- ],
- "refresh": "",
- "schemaVersion": 30,
- "style": "dark",
- "tags": [
- "Jira",
- "Data Source Specific Dashboard"
- ],
- "templating": {
- "list": [
- {
- "allValue": null,
- "current": {
- "selected": false,
- "text": "Month",
- "value": "DAY"
- },
- "description": null,
- "error": null,
- "hide": 0,
- "includeAll": false,
- "label": "Time Interval",
- "multi": false,
- "name": "interval",
- "options": [
- {
- "selected": false,
- "text": "Week",
- "value": "DAYOFWEEK"
- },
- {
- "selected": true,
- "text": "Month",
- "value": "DAY"
- }
- ],
- "query": "Week : DAYOFWEEK, Month : DAY",
- "queryValue": "",
- "skipUrlSync": false,
- "type": "custom"
- },
- {
- "allValue": null,
- "current": {
- "selected": true,
- "text": [
- "All"
- ],
- "value": [
- "$__all"
- ]
- },
- "datasource": "mysql",
- "definition": "select distinct concat(name, ': ', board_id) from jira_boards",
- "description": null,
- "error": null,
- "hide": 0,
- "includeAll": true,
- "label": "Choose Board",
- "multi": true,
- "name": "board_id",
- "options": [],
- "query": "select distinct concat(name, ': ', board_id) from jira_boards",
- "refresh": 1,
- "regex": "/^(?<text>[^:]+): (?<value>\\d+)$/",
- "skipUrlSync": false,
- "sort": 0,
- "type": "query"
- }
- ]
- },
- "time": {
- "from": "now-6M",
- "to": "now"
- },
- "timepicker": {},
- "timezone": "",
- "title": "Jira",
- "uid": "2e01bLN7z",
- "version": 2
-}
\ No newline at end of file
diff --git a/grafana/dashboards/DemoDetailedBugInfo.json b/grafana/dashboards/DemoDetailedBugInfo.json
index e046573..64c69e9 100644
--- a/grafana/dashboards/DemoDetailedBugInfo.json
+++ b/grafana/dashboards/DemoDetailedBugInfo.json
@@ -15,7 +15,7 @@
"editable": true,
"gnetId": null,
"graphTooltip": 0,
- "id": 7,
+ "id": 6,
"links": [
{
"asDropdown": false,
@@ -191,6 +191,18 @@
"value": 538
}
]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "description"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 753
+ }
+ ]
}
]
},
@@ -213,7 +225,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "select \n title,\n summary,\n case when assignee_id = '' then '-' else assignee_name end as assignee,\n status,\n created_date\n from issues i\n where \n type = 'Bug'\n and $__timeFilter(created_date)\n order by created_date desc",
+ "rawSql": "select \n title,\n description,\n case when assignee_id = '' then '-' else assignee_name end as assignee,\n status,\n created_date\n from issues i\n where \n type = 'Bug'\n and $__timeFilter(created_date)\n order by created_date desc",
"refId": "A",
"select": [
[
@@ -253,5 +265,5 @@
"timezone": "",
"title": "Demo-Detailed Bug Info",
"uid": "s48Lzn5nz",
- "version": 1
+ "version": 2
}
\ No newline at end of file
diff --git a/grafana/dashboards/GithubBasicMetrics.json b/grafana/dashboards/GithubBasicMetrics.json
index 87b2b77..6770c8f 100644
--- a/grafana/dashboards/GithubBasicMetrics.json
+++ b/grafana/dashboards/GithubBasicMetrics.json
@@ -15,8 +15,8 @@
"editable": true,
"gnetId": null,
"graphTooltip": 0,
- "id": 16,
- "iteration": 1650033937381,
+ "id": 13,
+ "iteration": 1650629880265,
"links": [],
"panels": [
{
@@ -60,7 +60,7 @@
},
"gridPos": {
"h": 6,
- "w": 6,
+ "w": 7,
"x": 0,
"y": 1
},
@@ -88,7 +88,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select \n\tcount(distinct i.id)\nfrom \n\tissues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n $__timeFilter(i.created_date)\n and b.id in ($repo_id)\n\n",
+ "rawSql": "select \n\tcount(distinct i.id)\nfrom \n\tissues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n $__timeFilter(i.created_date)\n and b.id in ($repo_id)\n",
"refId": "A",
"select": [
[
@@ -112,7 +112,7 @@
]
}
],
- "title": "1.1 Number of New Issues (Selected Time Range)",
+ "title": "1.1 Number of New Issues [Selected Time Range]",
"type": "stat"
},
{
@@ -155,8 +155,8 @@
},
"gridPos": {
"h": 6,
- "w": 18,
- "x": 6,
+ "w": 17,
+ "x": 7,
"y": 1
},
"id": 74,
@@ -164,7 +164,9 @@
"barWidth": 0.5,
"groupWidth": 0.7,
"legend": {
- "calcs": [],
+ "calcs": [
+ "mean"
+ ],
"displayMode": "list",
"placement": "bottom"
},
@@ -213,13 +215,93 @@
"type": "barchart"
},
{
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 7
+ },
+ "id": 90,
+ "options": {
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "showThresholdLabels": false,
+ "showThresholdMarkers": true,
+ "text": {}
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _issues as(\n SELECT\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n count(distinct i.id) as issue_count\n FROM issues i\n \tjoin board_issues bi on i.id = bi.issue_id\n \tjoin boards b on bi.board_id = b.id\n WHERE\n $__timeFilter(i.created_date)\n and b.id in ($repo_id)\n group by 1\n)\n\nSELECT \n avg(issue_count) as \"Mean Issue Count\",\n std(issue_count) as \"STD\"\nFROM _issues",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "1.3 Mean New Issue Count and Standard Deviation",
+ "type": "gauge"
+ },
+ {
"collapsed": false,
"datasource": null,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
- "y": 7
+ "y": 13
},
"id": 87,
"panels": [],
@@ -253,9 +335,9 @@
},
"gridPos": {
"h": 6,
- "w": 6,
+ "w": 7,
"x": 0,
- "y": 8
+ "y": 14
},
"id": 43,
"options": {
@@ -305,7 +387,7 @@
]
}
],
- "title": "2.1 Number of Closed Issues (Selected Time Range)",
+ "title": "2.1 Number of Closed Issues [Issues Created in Selected Time Range]",
"type": "stat"
},
{
@@ -379,9 +461,9 @@
},
"gridPos": {
"h": 6,
- "w": 18,
- "x": 6,
- "y": 8
+ "w": 17,
+ "x": 7,
+ "y": 14
},
"id": 76,
"options": {
@@ -428,7 +510,7 @@
]
}
],
- "title": "2.2 Number of Open and Closed Issues [Each Month]",
+ "title": "2.2 Number of Open and Closed Issues [Issues Created in Each Month]",
"type": "timeseries"
},
{
@@ -458,9 +540,9 @@
},
"gridPos": {
"h": 6,
- "w": 6,
+ "w": 7,
"x": 0,
- "y": 14
+ "y": 20
},
"id": 64,
"options": {
@@ -486,7 +568,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select \n\tAVG(i.lead_time_minutes/3600) issue_lead_time\nfrom \n\tissues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n $__timeFilter(i.created_date)\n and b.id in ($repo_id)\n and i.status = \"DONE\"",
+ "rawSql": "select \n\tAVG(i.lead_time_minutes/1440) issue_lead_time\nfrom \n\tissues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n $__timeFilter(i.created_date)\n and b.id in ($repo_id)\n and i.status = \"DONE\"",
"refId": "A",
"select": [
[
@@ -510,7 +592,7 @@
]
}
],
- "title": "2.3 Mean Issue Lead Time in Days [Selected Time Range]",
+ "title": "2.3 Mean Issue Lead Time in Days [Issues Created in Selected Time Range]",
"type": "stat"
},
{
@@ -553,9 +635,9 @@
},
"gridPos": {
"h": 6,
- "w": 18,
- "x": 6,
- "y": 14
+ "w": 17,
+ "x": 7,
+ "y": 20
},
"id": 75,
"options": {
@@ -583,7 +665,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "with _issues as(\n SELECT\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n AVG(i.lead_time_minutes/3600) as issue_lead_time\n FROM issues i\n \tjoin board_issues bi on i.id = bi.issue_id\n \tjoin boards b on bi.board_id = b.id\n WHERE\n b.id in ($repo_id)\n and i.status = \"DONE\"\n and $__timeFilter(i.created_date)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n issue_lead_time as \"Issue Lead Time\"\nFROM _issues\nORDER BY time\n",
+ "rawSql": "with _issues as(\n SELECT\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n AVG(i.lead_time_minutes/1440) as issue_lead_time\n FROM issues i\n \tjoin board_issues bi on i.id = bi.issue_id\n \tjoin boards b on bi.board_id = b.id\n WHERE\n b.id in ($repo_id)\n and i.status = \"DONE\"\n and $__timeFilter(i.created_date)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n issue_lead_time as \"Issue Lead Time\"\nFROM _issues\nORDER BY time\n",
"refId": "A",
"select": [
[
@@ -607,17 +689,311 @@
]
}
],
- "title": "2.4 Mean Issue Lead Time [Each Month]",
+ "title": "2.4 Mean Issue Lead Time in Days [Issues Created in Each Month]",
"type": "barchart"
},
{
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Queue Time (Days)",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "<avg_bug_age"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "semi-dark-green",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": ">=avg_bug_age"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "red",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 26
+ },
+ "id": 93,
+ "options": {
+ "barWidth": 0.1,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {},
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Get the queue time of all backlog bugs\nwith _outstanding_issues as(\n select \n b.name as repo_name,\n i.number as issue_key,\n i.title,\n i.created_date,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as queue_time_in_days,\n concat(b.url,'/',i.number) as url\n from \n issues i\n left join board_issues bi on i.id = bi.issue_id\n left join boards b on bi.board_id = b.id\n where\n b.id in ($repo_id)\n and $__timeFilter(i.created_date)\n and i.status != 'DONE'\n)\n\nselect title, queue_time_in_days from _outstanding_issues\norder by 2 desc",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "2.5 Queue Time in Days [All Open Issues]",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "custom": {
+ "align": "auto",
+ "displayMode": "auto"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "<avg_bug_age"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "semi-dark-green",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": ">=avg_bug_age"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "red",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "issue_key"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 110
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "title"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 590
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "created_date"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 149
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "queue_time_in_days"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 155
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "repo_name"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 256
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 32
+ },
+ "id": 92,
+ "options": {
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "queryType": "randomWalk",
+ "rawQuery": true,
+ "rawSql": "-- Get the queue time of all backlog bugs\nselect \n b.name as repo_name,\n i.number as issue_key,\n i.title,\n i.created_date,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as queue_time_in_days,\n concat(b.url,'/',i.number) as url\nfrom \n issues i\n left join board_issues bi on i.id = bi.issue_id\n left join boards b on bi.board_id = b.id\nwhere\n b.id in ($repo_id)\n and $__timeFilter(i.created_date)\n and i.status != 'DONE'\norder by queue_time_in_days desc",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "2.6 List of Outstanding Issues Order By Queue Time [All Open Issues]",
+ "type": "table"
+ },
+ {
"collapsed": false,
"datasource": null,
"gridPos": {
"h": 1,
"w": 24,
"x": 0,
- "y": 20
+ "y": 38
},
"id": 83,
"panels": [],
@@ -653,7 +1029,7 @@
"h": 6,
"w": 6,
"x": 0,
- "y": 21
+ "y": 39
},
"id": 68,
"options": {
@@ -679,7 +1055,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select\n\tcount(*) as pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand repo_id in ($repo_id)\n\t\n\n",
+ "rawSql": "select\n\tcount(*) as pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)\n\t\n\n",
"refId": "A",
"select": [
[
@@ -748,7 +1124,7 @@
"h": 6,
"w": 18,
"x": 6,
- "y": 21
+ "y": 39
},
"id": 77,
"options": {
@@ -776,7 +1152,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "with _prs as(\n SELECT\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n count(*) as pr_count\n FROM pull_requests\n WHERE\n repo_id in ($repo_id)\n and $__timeFilter(created_date)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n pr_count as \"Pull Request Count\"\nFROM _prs\nORDER BY time\n",
+ "rawSql": "with _prs as(\n SELECT\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n count(*) as pr_count\n FROM pull_requests\n WHERE\n base_repo_id in ($repo_id)\n and $__timeFilter(created_date)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n pr_count as \"Pull Request Count\"\nFROM _prs\nORDER BY time\n",
"refId": "A",
"select": [
[
@@ -846,7 +1222,7 @@
"h": 6,
"w": 24,
"x": 0,
- "y": 27
+ "y": 45
},
"id": 59,
"options": {
@@ -874,7 +1250,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select\n author_name,\n\tcount(*) as merged_pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand repo_id in ($repo_id)\n\tand status = \"closed\"\n\tand merged_date is not null\ngroup by 1\norder by 2 desc\nlimit 20\n",
+ "rawSql": "select\n author_name,\n\tcount(*) as merged_pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)\n\tand status = \"closed\"\n\tand merged_date is not null\ngroup by 1\norder by 2 desc\nlimit 20\n",
"refId": "A",
"select": [
[
@@ -908,7 +1284,7 @@
"h": 1,
"w": 24,
"x": 0,
- "y": 33
+ "y": 51
},
"id": 85,
"panels": [],
@@ -945,7 +1321,7 @@
"h": 6,
"w": 6,
"x": 0,
- "y": 34
+ "y": 52
},
"id": 66,
"options": {
@@ -969,7 +1345,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select\n count(distinct case when status = 'closed' and merged_date is null then id else null end)/count(distinct case when status = 'closed' then id else null end) as ratio\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand repo_id in ($repo_id)",
+ "rawSql": "select\n count(distinct case when status = 'closed' and merged_date is null then id else null end)/count(distinct case when status = 'closed' then id else null end) as ratio\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)",
"refId": "A",
"select": [
[
@@ -1049,7 +1425,7 @@
"h": 6,
"w": 18,
"x": 6,
- "y": 34
+ "y": 52
},
"id": 79,
"options": {
@@ -1072,7 +1448,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "SELECT\n DATE_ADD(date(created_date), INTERVAL -DAYOFMONTH(date(created_date))+1 DAY) as time,\n count(distinct case when status != 'closed' then id else null end) as \"PR: Open\",\n count(distinct case when status = 'closed' and merged_date is null then id else null end) as \"PR: Closed without merging\",\n count(distinct case when status = 'closed' and merged_date is not null then id else null end) as \"PR: Closed and merged\"\nFROM pull_requests\nWHERE\n $__timeFilter(created_date)\n and repo_id in ($repo_id)\ngroup by 1\n",
+ "rawSql": "SELECT\n DATE_ADD(date(created_date), INTERVAL -DAYOFMONTH(date(created_date))+1 DAY) as time,\n count(distinct case when status != 'closed' then id else null end) as \"PR: Open\",\n count(distinct case when status = 'closed' and merged_date is null then id else null end) as \"PR: Closed without merging\",\n count(distinct case when status = 'closed' and merged_date is not null then id else null end) as \"PR: Closed and merged\"\nFROM pull_requests\nWHERE\n $__timeFilter(created_date)\n and base_repo_id in ($repo_id)\ngroup by 1\n",
"refId": "A",
"select": [
[
@@ -1128,7 +1504,7 @@
"h": 6,
"w": 6,
"x": 0,
- "y": 40
+ "y": 58
},
"id": 80,
"options": {
@@ -1154,7 +1530,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select\n\tcount(*) as merged_pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand repo_id in ($repo_id)\n\tand status = \"closed\"\n\tand merged_date is null",
+ "rawSql": "select\n\tcount(*) as merged_pull_request_count\nfrom \n\tpull_requests pr\nwhere\n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)\n\tand status = \"closed\"\n\tand merged_date is null",
"refId": "A",
"select": [
[
@@ -1238,7 +1614,7 @@
"h": 6,
"w": 18,
"x": 6,
- "y": 40
+ "y": 58
},
"id": 81,
"options": {
@@ -1261,7 +1637,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "SELECT\n DATE_ADD(date(created_date), INTERVAL -DAYOFMONTH(date(created_date))+1 DAY) as time,\n count(distinct case when status = 'closed' and merged_date is null then id else null end)/count(distinct case when status = 'closed' then id else null end) as ratio\nFROM pull_requests\nWHERE\n $__timeFilter(created_date)\n and repo_id in ($repo_id)\ngroup by 1\n",
+ "rawSql": "SELECT\n DATE_ADD(date(created_date), INTERVAL -DAYOFMONTH(date(created_date))+1 DAY) as time,\n count(distinct case when status = 'closed' and merged_date is null then id else null end)/count(distinct case when status = 'closed' then id else null end) as ratio\nFROM pull_requests\nWHERE\n $__timeFilter(created_date)\n and base_repo_id in ($repo_id)\ngroup by 1\n",
"refId": "A",
"select": [
[
@@ -1317,7 +1693,7 @@
"h": 6,
"w": 6,
"x": 0,
- "y": 46
+ "y": 64
},
"id": 72,
"options": {
@@ -1343,7 +1719,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "select\n\tavg(TIMESTAMPDIFF(DAY,created_date,merged_date))\nfrom \n\tpull_requests\nwhere \n $__timeFilter(created_date)\n\tand repo_id in ($repo_id)\n\tand status = \"closed\"\n\tand merged_date is not null\n\n\n",
+ "rawSql": "select\n\tavg(TIMESTAMPDIFF(Minute,created_date,merged_date)/1440)\nfrom \n\tpull_requests\nwhere \n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)\n\tand merged_date is not null\n\n\n",
"refId": "A",
"select": [
[
@@ -1412,9 +1788,9 @@
"h": 6,
"w": 18,
"x": 6,
- "y": 46
+ "y": 64
},
- "id": 78,
+ "id": 95,
"options": {
"barWidth": 0.5,
"groupWidth": 0.7,
@@ -1440,7 +1816,7 @@
"hide": false,
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "with _prs as(\n SELECT\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n avg(TIMESTAMPDIFF(DAY,created_date,merged_date)) as time_to_merge\n FROM pull_requests\n WHERE\n $__timeFilter(created_date)\n and repo_id in ($repo_id)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n time_to_merge as \"Time to Merge\"\nFROM _prs\nORDER BY time\n",
+ "rawSql": "with _prs as(\n SELECT\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n avg(TIMESTAMPDIFF(Minute,created_date,merged_date)/1440) as time_to_merge\n FROM pull_requests\n WHERE\n $__timeFilter(created_date)\n and base_repo_id in ($repo_id)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n time_to_merge as \"Time to Merge\"\nFROM _prs\nORDER BY time\n",
"refId": "A",
"select": [
[
@@ -1466,6 +1842,183 @@
],
"title": "4.6 Mean Time to Merge of Pull Requests in Days [Each Month]",
"type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 6,
+ "x": 0,
+ "y": 70
+ },
+ "id": 96,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "select\n\tavg(TIMESTAMPDIFF(Minute,created_date,closed_date)/1440) as time_to_close\nfrom \n\tpull_requests\nwhere \n $__timeFilter(created_date)\n\tand base_repo_id in ($repo_id)\n\tand status = 'closed'\n\n\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "4.7 Mean Time to Close of Pull Requests in Days [Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 18,
+ "x": 6,
+ "y": 70
+ },
+ "id": 97,
+ "options": {
+ "barWidth": 0.5,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _prs as(\n SELECT\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n avg(TIMESTAMPDIFF(Minute,created_date,closed_date)/1440) as time_to_close\n FROM pull_requests\n WHERE\n $__timeFilter(created_date)\n and base_repo_id in ($repo_id)\n and status = 'closed'\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n time_to_close as \"Time to Close\"\nFROM _prs\nORDER BY time\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "4.8 Mean Time to Close of Pull Requests in Days [Each Month]",
+ "type": "barchart"
}
],
"refresh": "",
@@ -1486,7 +2039,7 @@
]
},
"datasource": "mysql",
- "definition": "select concat(name, '-', id) as text from repos",
+ "definition": "select concat(name, '--', id) as text from repos",
"description": null,
"error": null,
"hide": 0,
@@ -1495,9 +2048,9 @@
"multi": true,
"name": "repo_id",
"options": [],
- "query": "select concat(name, '-', id) as text from repos",
+ "query": "select concat(name, '--', id) as text from repos",
"refresh": 1,
- "regex": "/^(?<text>.*)-(?<value>.*)$/",
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
"skipUrlSync": false,
"sort": 0,
"type": "query"
@@ -1510,7 +2063,7 @@
},
"timepicker": {},
"timezone": "",
- "title": "GitHub_Basic_Metrics",
- "uid": "vlbSawU7z",
- "version": 2
+ "title": "Github_Basic_Metrics",
+ "uid": "KXWvOFQnz",
+ "version": 7
}
\ No newline at end of file
diff --git a/grafana/dashboards/GithubReleaseQualityAndContributionAnalysis.json b/grafana/dashboards/GithubReleaseQualityAndContributionAnalysis.json
index 9fb37a9..353da56 100644
--- a/grafana/dashboards/GithubReleaseQualityAndContributionAnalysis.json
+++ b/grafana/dashboards/GithubReleaseQualityAndContributionAnalysis.json
@@ -15,8 +15,8 @@
"editable": true,
"gnetId": null,
"graphTooltip": 0,
- "id": 17,
- "iteration": 1650034044604,
+ "id": 11,
+ "iteration": 1650636611699,
"links": [],
"panels": [
{
@@ -92,7 +92,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the bug distribution in major versions\nwith bugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_name,'refs/tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) as repo_id,\n\t\ti.key, i.type, i.title, i.summary\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT \n\tconcat(SUBSTRING_INDEX(biet.tag_name,'.',2), '.x') as minor_version,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nGROUP BY 1",
+ "rawSql": "-- Get the bug distribution in major versions\nwith bugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_id,'refs/tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) as repo_id,\n\t\ti.number, i.type, i.title, i.description\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT \n\tconcat(SUBSTRING_INDEX(biet.tag_name,'.',2), '.x') as minor_version,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nGROUP BY 1",
"refId": "A",
"select": [
[
@@ -218,7 +218,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the number of fixed bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs_of_tags as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_name,'tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) as repo_id,\n\t\tcount(*) as bug_count\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\tGROUP BY 1,2\n),\n\n_combine_pr as (\n select pull_request_id as id, commit_sha from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where p.repo_id = '$repo_id'\n union\n select id, merge_commit_sha as commit_sha from pull_requests where repo_id = '$repo_id'\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_name,'tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) as repo_id,\n pr.id as pull_request_id,\n count(c.sha) as commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\t\tand rcd.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\tgroup by 1,2,3\n),\n\n_pr_worktype as(\n select\n distinct pri.pull_request_id,i.type\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_pr_elco_and_worktype as(\n select\n ccop.tag_name, \n sum(case when pw.type = 'BUG' then commit_count else 0 end)/sum(commit_count) as cost_percentage\n from \n _commit_count_of_pr ccop\n left join _pr_worktype pw on ccop.pull_request_id = pw.pull_request_id\n GROUP BY 1\n)\n\nSELECT \n\tbot.tag_name,\n\tbot.bug_count,\n\tpeaw.cost_percentage as \"cost_percentage(bugfixing commits/total commits)\"\nFROM \n\t_bugs_of_tags bot\n\tjoin _pr_elco_and_worktype peaw on bot.tag_name = peaw.tag_name\nORDER BY 1",
+ "rawSql": "-- Get the number of fixed bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs_of_tags as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_id,'tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) as repo_id,\n\t\tcount(*) as bug_count\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\tGROUP BY 1,2\n),\n\n_combine_pr as (\n select pull_request_id as id, commit_sha from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where p.base_repo_id = ($repo_id)\n union\n select id, merge_commit_sha as commit_sha from pull_requests where base_repo_id = ($repo_id)\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_id,'tags/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) as repo_id,\n pr.id as pull_request_id,\n count(c.sha) as commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\t\tand rcd.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\tgroup by 1,2,3\n),\n\n_pr_worktype as(\n select\n distinct pri.pull_request_id,i.type\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_pr_elco_and_worktype as(\n select\n ccop.tag_name, \n sum(case when pw.type = 'BUG' then commit_count else 0 end)/sum(commit_count) as cost_percentage\n from \n _commit_count_of_pr ccop\n left join _pr_worktype pw on ccop.pull_request_id = pw.pull_request_id\n GROUP BY 1\n)\n\nSELECT \n\tbot.tag_name,\n\tbot.bug_count,\n\tpeaw.cost_percentage as \"cost_percentage(bugfixing commits/total commits)\"\nFROM \n\t_bugs_of_tags bot\n\tjoin _pr_elco_and_worktype peaw on bot.tag_name = peaw.tag_name\nORDER BY 1",
"refId": "A",
"select": [
[
@@ -379,7 +379,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the number of fixed bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n)\n\t\nselect distinct\n\tb.name as repo_name,\n\tSUBSTRING_INDEX(rid.new_ref_name,'tags/', -1) as tag_name, \n\ti.key as issue_key,\n\ti.title,\n\ti.assignee_name,\n\ti.lead_time_minutes/1440 as lead_time_in_days,\n\tconcat(b.url,'/',i.key) as url\nfrom\n\trefs_issues_diffs rid\n\tleft join issues i on rid.issue_id = i.id\n\tjoin boards b on SUBSTRING_INDEX(rid.new_ref_name,':', 3) = b.id\nwhere\n\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\tand i.type = 'BUG'\norder by tag_name desc",
+ "rawSql": "-- Get the number of fixed bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n)\n\t\nselect distinct\n\tb.name as repo_name,\n\tSUBSTRING_INDEX(rid.new_ref_id,'tags/', -1) as tag_name, \n\ti.number as issue_key,\n\ti.title,\n\ti.assignee_name,\n\ti.lead_time_minutes/1440 as lead_time_in_days,\n\tconcat(b.url,'/',i.number) as url\nfrom\n\trefs_issues_diffs rid\n\tleft join issues i on rid.issue_id = i.id\n\tjoin boards b on SUBSTRING_INDEX(rid.new_ref_id,':', 3) = b.id\nwhere\n\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\tand i.type = 'BUG'\norder by tag_name desc",
"refId": "A",
"select": [
[
@@ -465,7 +465,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Component distribution of bugs fixed in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\nbugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_name,'refs/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) as repo_id,\n\t\ti.key, i.component, i.severity, i.title, i.summary\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT\n\tcase when component = '' then 'unlabeled' else 'labeled' end as component,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nGROUP BY 1",
+ "rawSql": "-- Component distribution of bugs fixed in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\nbugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_id,'refs/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) as repo_id,\n\t\ti.number, i.component, i.severity, i.title, i.description\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT\n\tcase when component = '' then 'unlabeled' else 'labeled' end as component,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nGROUP BY 1",
"refId": "A",
"select": [
[
@@ -551,7 +551,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Component distribution of bugs fixed in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\nbugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_name,'refs/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) as repo_id,\n\t\ti.key, i.component, i.severity, i.title, i.summary\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT\n\tcomponent,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nwhere \n component != ''\nGROUP BY 1",
+ "rawSql": "-- Component distribution of bugs fixed in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\nbugs_in_each_tag as(\n\tselect \n\t\tSUBSTRING_INDEX(rid.new_ref_id,'refs/', -1) as tag_name, \n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) as repo_id,\n\t\ti.number, i.component, i.severity, i.title, i.description\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n)\n\n\nSELECT\n\tcomponent,\n\tcount(*) as bug_count\nFROM \n\tbugs_in_each_tag biet\nwhere \n component != ''\nGROUP BY 1",
"refId": "A",
"select": [
[
@@ -638,7 +638,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the % of contributors who fixed 80% of bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect \n\t\ti.key, i.type, i.severity, i.title, i.summary,\n\t\tpr.id, pr.author_name as pr_author, pr.created_date,\n\t\trank() over(partition by i.id order by pr.created_date asc) as pr_rank\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tleft join pull_requests pr on pri.pull_request_id = pr.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\torder by i.key\n),\n\n_bug_fixed_count as(\n SELECT \n pr_author,\n count(*) bug_fixed_count\n FROM _bugs\n WHERE pr_rank = 1\n GROUP BY 1\n),\n\n_bug_fixed_count_running_total as(\n SELECT \n *, \n sum(bug_fixed_count) OVER (Order by bug_fixed_count desc) AS running_total\n FROM \n _bug_fixed_count\n),\n\n_percentile as(\n SELECT \n pr_author,\n bug_fixed_count,\n running_total/sum(bug_fixed_count) OVER () AS cumulative_percentage\n FROM \n _bug_fixed_count_running_total\n)\n\n\nSELECT \n count(case when cumulative_percentage <= 0.8 then pr_author else null end)/count(*) as \"% of contributors who fixed 80% of the bugs\"\nFROM _percentile",
+ "rawSql": "-- Get the % of contributors who fixed 80% of bugs in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect \n\t\ti.number, i.type, i.severity, i.title, i.description,\n\t\tpr.id, pr.author_name as pr_author, pr.created_date,\n\t\trank() over(partition by i.id order by pr.created_date asc) as pr_rank\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tleft join pull_requests pr on pri.pull_request_id = pr.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\torder by i.number\n),\n\n_bug_fixed_count as(\n SELECT \n pr_author,\n count(*) bug_fixed_count\n FROM _bugs\n WHERE pr_rank = 1\n GROUP BY 1\n),\n\n_bug_fixed_count_running_total as(\n SELECT \n *, \n sum(bug_fixed_count) OVER (Order by bug_fixed_count desc) AS running_total\n FROM \n _bug_fixed_count\n),\n\n_percentile as(\n SELECT \n pr_author,\n bug_fixed_count,\n running_total/sum(bug_fixed_count) OVER () AS cumulative_percentage\n FROM \n _bug_fixed_count_running_total\n)\n\n\nSELECT \n count(case when cumulative_percentage <= 0.8 then pr_author else null end)/count(*) as \"% of contributors who fixed 80% of the bugs\"\nFROM _percentile",
"refId": "A",
"select": [
[
@@ -735,7 +735,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the bug fixer distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect \n\t\ti.key, i.type, i.severity, i.title, i.summary,\n\t\tpr.id, pr.author_name as pr_author, pr.created_date,\n\t\trank() over(partition by i.id order by pr.created_date asc) as pr_rank\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tleft join pull_requests pr on pri.pull_request_id = pr.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\torder by i.key\n)\n\nSELECT \n pr_author,\n count(*) bug_fixed_count\nFROM _bugs\nWHERE pr_rank = 1\nGROUP BY 1\nORDER BY 2 desc\nlimit 10",
+ "rawSql": "-- Get the bug fixer distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect \n\t\ti.number, i.type, i.severity, i.title, i.description,\n\t\tpr.id, pr.author_name as pr_author, pr.created_date,\n\t\trank() over(partition by i.id order by pr.created_date asc) as pr_rank\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tleft join pull_requests pr on pri.pull_request_id = pr.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n\torder by i.number\n)\n\nSELECT \n pr_author,\n count(*) bug_fixed_count\nFROM _bugs\nWHERE pr_rank = 1\nGROUP BY 1\nORDER BY 2 desc\nlimit 10",
"refId": "A",
"select": [
[
@@ -818,7 +818,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the avg bug age in history\nselect \n avg(lead_time_minutes)/1440 as average_bug_age\nfrom issues\nleft join board_issues bi on issues.id = bi.issue_id\nwhere \n type = 'BUG'\n and status = 'DONE'\n and bi.board_id = '$repo_id'",
+ "rawSql": "-- Get the avg bug age in history\nselect \n avg(lead_time_minutes)/1440 as average_bug_age\nfrom issues\nleft join board_issues bi on issues.id = bi.issue_id\nwhere \n type = 'BUG'\n and status = 'DONE'\n and bi.board_id in ($repo_id)",
"refId": "A",
"select": [
[
@@ -915,7 +915,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the bug age in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect distinct\n\t\tSUBSTRING_INDEX(rid.new_ref_name,'tags/', -1) as tag_name,\n\t\ti.id,\n\t\ti.lead_time_minutes\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n),\n\n_bugs_percentile as(\n select \n *,\n percent_rank() over (partition by tag_name order by lead_time_minutes) as percentile\n from _bugs order by 1\n),\n\n_avg_bug_age as(\n select \n tag_name,\n avg(lead_time_minutes)/1440 as average_bug_age\n from _bugs_percentile\n group by 1\n),\n\n_50th_bug_age as(\n select \n tag_name,\n min(lead_time_minutes)/1440 as \"50th_bug_age\"\n from _bugs_percentile\n where percentile >= 0.5\n group by 1\n)\n\nselect \n aba.*,\n eba.50th_bug_age\nfrom \n _avg_bug_age aba\n join _50th_bug_age eba on aba.tag_name = eba.tag_name",
+ "rawSql": "-- Get the bug age in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect distinct\n\t\tSUBSTRING_INDEX(rid.new_ref_id,'tags/', -1) as tag_name,\n\t\ti.id,\n\t\ti.lead_time_minutes\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n),\n\n_bugs_percentile as(\n select \n *,\n percent_rank() over (partition by tag_name order by lead_time_minutes) as percentile\n from _bugs order by 1\n),\n\n_avg_bug_age as(\n select \n tag_name,\n avg(lead_time_minutes)/1440 as average_bug_age\n from _bugs_percentile\n group by 1\n),\n\n_50th_bug_age as(\n select \n tag_name,\n min(lead_time_minutes)/1440 as \"50th_bug_age\"\n from _bugs_percentile\n where percentile >= 0.5\n group by 1\n)\n\nselect \n aba.*,\n eba.50th_bug_age\nfrom \n _avg_bug_age aba\n join _50th_bug_age eba on aba.tag_name = eba.tag_name",
"refId": "A",
"select": [
[
@@ -1159,7 +1159,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the bug fixer distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect distinct\n\t b.name,\n\t\tSUBSTRING_INDEX(rid.new_ref_name,'tags/', -1) as tag_name,\n\t\ti.key as issue_key,\n i.title,\n i.lead_time_minutes/1440 as lead_time_in_days,\n concat(b.url,'/',i.key) as url\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tjoin boards b on SUBSTRING_INDEX(rid.new_ref_name,':', 3) = b.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_name,':', 3) = '$repo_id'\n\t\tand rid.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n),\n\n_bug_age_rank as(\n select \n *,\n row_number() over (partition by tag_name order by lead_time_in_days desc) as bug_age_rank\n from _bugs\n)\n\nselect \n name,\n tag_name,\n issue_key,\n title,\n lead_time_in_days,\n url\nfrom _bug_age_rank\nwhere bug_age_rank <=10 \norder by tag_name, lead_time_in_days desc",
+ "rawSql": "-- Get the bug fixer distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n),\n\n_bugs as(\n\tselect distinct\n\t b.name,\n\t\tSUBSTRING_INDEX(rid.new_ref_id,'tags/', -1) as tag_name,\n\t\ti.number as issue_key,\n i.title,\n i.lead_time_minutes/1440 as lead_time_in_days,\n concat(b.url,'/',i.number) as url\n\tfrom\n\t\trefs_issues_diffs rid\n\t\tleft join issues i on rid.issue_id = i.id\n\t\tleft join pull_request_issues pri on i.id = pri.issue_id\n\t\tjoin boards b on SUBSTRING_INDEX(rid.new_ref_id,':', 3) = b.id\n\twhere\n\t\tSUBSTRING_INDEX(rid.new_ref_id,':', 3) in ($repo_id)\n\t\tand rid.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\t\tand i.type = 'BUG'\n),\n\n_bug_age_rank as(\n select \n *,\n row_number() over (partition by tag_name order by lead_time_in_days desc) as bug_age_rank\n from _bugs\n)\n\nselect \n name,\n tag_name,\n issue_key,\n title,\n lead_time_in_days,\n url\nfrom _bug_age_rank\nwhere bug_age_rank <=10 \norder by tag_name, lead_time_in_days desc",
"refId": "A",
"select": [
[
@@ -1301,7 +1301,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the avg bug age in history\nwith _avg_bug_age as(\n select \n type,\n avg(lead_time_minutes) as average_bug_age\n from \n issues\n left join board_issues bi on issues.id = bi.issue_id\n where \n type = 'BUG'\n and status = 'DONE'\n and bi.board_id = '$repo_id'\n group by 1\n),\n\n\n_bug_queue_time as(\n select \n i.id,\n abg.average_bug_age,\n TIMESTAMPDIFF(MINUTE,created_date,NOW()) as queue_time,\n case when TIMESTAMPDIFF(MINUTE,created_date,NOW()) >= average_bug_age then \">=avg_bug_age\" else \"<avg_bug_age\" end as distribution\n from \n issues i\n left join _avg_bug_age abg on i.type = abg.type\n where\n i.type = 'BUG'\n and status != 'DONE'\n)\n\nselect\n distribution,\n count(*) as bug_count\nfrom\n _bug_queue_time\ngroup by 1",
+ "rawSql": "-- Get the avg bug age in history\nwith _avg_bug_age as(\n select \n type,\n avg(lead_time_minutes) as average_bug_age\n from \n issues\n left join board_issues bi on issues.id = bi.issue_id\n where \n type = 'BUG'\n and status = 'DONE'\n and bi.board_id in ($repo_id)\n group by 1\n),\n\n\n_bug_queue_time as(\n select \n i.id,\n abg.average_bug_age,\n TIMESTAMPDIFF(MINUTE,created_date,NOW()) as queue_time,\n case when TIMESTAMPDIFF(MINUTE,created_date,NOW()) >= average_bug_age then \">=avg_bug_age\" else \"<avg_bug_age\" end as distribution\n from \n issues i\n left join _avg_bug_age abg on i.type = abg.type\n where\n i.type = 'BUG'\n and status != 'DONE'\n)\n\nselect\n distribution,\n count(*) as bug_count\nfrom\n _bug_queue_time\ngroup by 1",
"refId": "A",
"select": [
[
@@ -1456,7 +1456,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the queue time of all backlog bugs\nselect \n b.name as repo_name,\n i.key as issue_key,\n i.title,\n i.created_date,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/3600 as queue_time_in_days,\n concat(b.url,'/',i.key) as url\nfrom \n issues i\n left join board_issues bi on i.id = bi.issue_id\n left join boards b on bi.board_id = b.id\nwhere\n i.type = 'BUG'\n and i.status != 'DONE'\n and b.id = '$repo_id'\norder by queue_time_in_days desc",
+ "rawSql": "-- Get the queue time of all backlog bugs\nselect \n b.name as repo_name,\n i.number as issue_key,\n i.title,\n i.created_date,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/3600 as queue_time_in_days,\n concat(b.url,'/',i.number) as url\nfrom \n issues i\n left join board_issues bi on i.id = bi.issue_id\n left join boards b on bi.board_id = b.id\nwhere\n i.type = 'BUG'\n and i.status != 'DONE'\n and b.id in ($repo_id)\norder by queue_time_in_days desc",
"refId": "A",
"select": [
[
@@ -1564,7 +1564,7 @@
"group": [],
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Get the bug distribution in last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 10\n)\n\nselect \n\tSUBSTRING_INDEX(rcd.new_ref_name,'refs/tags/', -1) as tag_name,\n\tSUBSTRING_INDEX(rcd.old_ref_name,'refs/tags/', -1) as old_tag_name,\n\tcount(*) as commit_count\nfrom\n\trefs_commits_diffs rcd\n\tleft join commits c on rcd.commit_sha = c.sha\nwhere\n\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\tand rcd.new_ref_name in (select new_ref_name from _last_5_tags)\ngroup by 1,2\norder by 1",
+ "rawSql": "-- Get the bug distribution in last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 10\n)\n\nselect \n\tSUBSTRING_INDEX(rcd.new_ref_id,'refs/tags/', -1) as tag_name,\n\tSUBSTRING_INDEX(rcd.old_ref_id,'refs/tags/', -1) as old_tag_name,\n\tcount(*) as commit_count\nfrom\n\trefs_commits_diffs rcd\n\tleft join commits c on rcd.commit_sha = c.sha\nwhere\n\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\tand rcd.new_ref_id in (select new_ref_id from _last_5_tags)\ngroup by 1,2\norder by 1",
"refId": "A",
"select": [
[
@@ -1674,7 +1674,7 @@
"group": [],
"metricColumn": "none",
"rawQuery": true,
- "rawSql": "-- Get the bug distribution in last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 10\n)\n\nselect \n\tSUBSTRING_INDEX(rcd.new_ref_name,'refs/tags/', -1) as new_tag_name,\n\tSUBSTRING_INDEX(rcd.old_ref_name,'refs/tags/', -1) as compared_tag_name,\n\tc.sha,\n\tc.message,\n\tc.additions,\n\tc.deletions,\n\tc.author_name\nfrom\n\trefs_commits_diffs rcd\n\tleft join commits c on rcd.commit_sha = c.sha\nwhere\n\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\tand rcd.new_ref_name in (select new_ref_name from _last_5_tags)\norder by 1 desc",
+ "rawSql": "-- Get the bug distribution in last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 10\n)\n\nselect \n\tSUBSTRING_INDEX(rcd.new_ref_id,'refs/tags/', -1) as new_tag_name,\n\tSUBSTRING_INDEX(rcd.old_ref_id,'refs/tags/', -1) as compared_tag_name,\n\tc.sha,\n\tc.message,\n\tc.additions,\n\tc.deletions,\n\tc.author_name\nfrom\n\trefs_commits_diffs rcd\n\tleft join commits c on rcd.commit_sha = c.sha\nwhere\n\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\tand rcd.new_ref_id in (select new_ref_id from _last_5_tags)\norder by 1 desc",
"refId": "A",
"select": [
[
@@ -1829,7 +1829,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 1\n),\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where repo_id = 'github:GithubRepo:60246359'\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where repo_id = 'github:GithubRepo:60246359'\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_name,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\t\tand rcd.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.key,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
+ "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 1\n),\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where base_repo_id = ($repo_id)\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where base_repo_id = ($repo_id)\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_id,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\t\tand rcd.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.number,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
"refId": "A",
"select": [
[
@@ -1914,20 +1914,20 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 1,1\n),\n\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where repo_id = 'github:GithubRepo:60246359'\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where repo_id = 'github:GithubRepo:60246359'\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_name,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\t\tand rcd.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.key,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
+ "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 1,1\n),\n\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where base_repo_id = ($repo_id)\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where base_repo_id = ($repo_id)\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_id,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\t\tand rcd.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.number,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
"refId": "A",
"select": [
[
{
"params": [
- "id"
+ "script_version"
],
"type": "column"
}
]
],
- "table": "ae_projects",
- "timeColumn": "ae_create_time",
+ "table": "_devlake_migration_history",
+ "timeColumn": "created_at",
"timeColumnType": "timestamp",
"where": [
{
@@ -2030,7 +2030,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 2,1\n),\n\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where repo_id = 'github:GithubRepo:60246359'\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where repo_id = 'github:GithubRepo:60246359'\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_name,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_name,':', 3) = '$repo_id'\n\t\tand rcd.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.key,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
+ "rawSql": "-- Get the work-type distribution in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 2,1\n),\n\n\n_combine_pr as (\n select pull_request_id as id, commit_sha, p.`key` as `key` from pull_request_commits left join pull_requests p on pull_request_commits.pull_request_id = p.id\n where base_repo_id = ($repo_id)\n union\n select id, merge_commit_sha, `key` as commit_sha from pull_requests where base_repo_id = ($repo_id)\n),\n\n_commit_count_of_pr as(\n select\n SUBSTRING_INDEX(rcd.new_ref_id,'tags/', -1) as tag_name,\n pr.id as pull_request_id,\n count(c.sha) as pr_commit_count\n FROM \n refs_commits_diffs rcd\n\t\tleft join commits c on rcd.commit_sha = c.sha\n\t\t-- left join pull_request_commits prc on c.sha = prc.commit_sha\n\t\tleft join _combine_pr pr on c.sha = pr.commit_sha\n\twhere\n\t\tSUBSTRING_INDEX(rcd.new_ref_id,':', 3) in ($repo_id)\n\t\tand rcd.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n\tgroup by 1,2\n),\n\n_pr_issues as(\n select\n pri.pull_request_id,\n pri.issue_id,\n i.number,\n i.type,\n row_number() over(partition by issue_id ORDER by pr.created_date asc) as pr_rank\n from\n pull_request_issues pri\n left join pull_requests pr on pri.pull_request_id = pr.id\n left join issues i on pri.issue_id = i.id\n where \n issue_number != 0\n),\n\n_final_results as(\n select\n distinct ccop.*, pi.type\n from \n _commit_count_of_pr ccop\n left join _pr_issues pi on ccop.pull_request_id = pi.pull_request_id\n where pi.pr_rank = 1\n order by 1\n)\n\nSELECT\n tag_name,\n case when type != '' then type else 'UNKNOWN' end as type,\n sum(pr_commit_count) as commit_count\nfrom _final_results\ngroup by 1,2",
"refId": "A",
"select": [
[
@@ -2117,7 +2117,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "-- Get each contributor's work in bugfixing in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 10\n),\n\n_author_commits as(\n SELECT \n \tc.author_name,\n count(c.sha) as commit_count\n FROM \n refs_commits_diffs rcf\n left join commits c on rcf.commit_sha = c.sha\n WHERE\n \trcf.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\n GROUP BY 1\n),\n\n_author_commits_running_total as(\n SELECT \n *, \n sum(commit_count) OVER (Order by commit_count desc) AS running_total\n FROM \n _author_commits\n),\n\n_percentile as(\n SELECT \n author_name,\n commit_count,\n running_total/sum(commit_count) OVER () AS cumulative_percentage\n FROM \n _author_commits_running_total\n)\n\n\nSELECT \n count(case when cumulative_percentage <= 0.8 then author_name else null end)/count(*) as \"contributors who contributed 80% of dev_eq\"\nFROM _percentile",
+ "rawSql": "-- Get each contributor's work in bugfixing in the last 5 tags\nwith _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 10\n),\n\n_author_commits as(\n SELECT \n \tc.author_name,\n count(c.sha) as commit_count\n FROM \n refs_commits_diffs rcf\n left join commits c on rcf.commit_sha = c.sha\n WHERE\n \trcf.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\n GROUP BY 1\n),\n\n_author_commits_running_total as(\n SELECT \n *, \n sum(commit_count) OVER (Order by commit_count desc) AS running_total\n FROM \n _author_commits\n),\n\n_percentile as(\n SELECT \n author_name,\n commit_count,\n running_total/sum(commit_count) OVER () AS cumulative_percentage\n FROM \n _author_commits_running_total\n)\n\n\nSELECT \n count(case when cumulative_percentage <= 0.8 then author_name else null end)/count(*) as \"contributors who contributed 80% of dev_eq\"\nFROM _percentile",
"refId": "A",
"select": [
[
@@ -2211,7 +2211,7 @@
"metricColumn": "none",
"queryType": "randomWalk",
"rawQuery": true,
- "rawSql": "with _last_5_tags as(\n SELECT \n distinct new_ref_name, old_ref_name\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_name,':', 3) = '$repo_id'\n\tORDER BY 1 desc\n\tLIMIT 5\n)\n\n\nSELECT \n\tc.author_name,\n count(c.sha) total_dev_eq\nFROM \n refs_commits_diffs rcf\n left join commits c on rcf.commit_sha = c.sha\nWHERE\n\trcf.new_ref_name in (SELECT new_ref_name FROM _last_5_tags)\nGROUP BY 1\nORDER BY 2 desc\nlimit 10",
+ "rawSql": "with _last_5_tags as(\n SELECT \n distinct new_ref_id, old_ref_id\n FROM \n refs_commits_diffs\n WHERE\n\t\tSUBSTRING_INDEX(new_ref_id,':', 3) in ($repo_id)\n\tORDER BY 1 desc\n\tLIMIT 5\n)\n\n\nSELECT \n\tc.author_name,\n count(c.sha) total_dev_eq\nFROM \n refs_commits_diffs rcf\n left join commits c on rcf.commit_sha = c.sha\nWHERE\n\trcf.new_ref_id in (SELECT new_ref_id FROM _last_5_tags)\nGROUP BY 1\nORDER BY 2 desc\nlimit 10",
"refId": "A",
"select": [
[
@@ -2248,23 +2248,27 @@
{
"allValue": null,
"current": {
- "selected": true,
- "text": "ClickHouse/ClickHouse",
- "value": "github:GithubRepo:60246359"
+ "selected": false,
+ "text": [
+ "ClickHouse/ClickHouse"
+ ],
+ "value": [
+ "github:GithubRepo:60246359"
+ ]
},
"datasource": "mysql",
- "definition": "select concat(name, '-', id) as text from repos",
+ "definition": "select concat(name, '--', id) as text from repos",
"description": null,
"error": null,
"hide": 0,
- "includeAll": false,
+ "includeAll": true,
"label": "Repo",
- "multi": false,
+ "multi": true,
"name": "repo_id",
"options": [],
- "query": "select concat(name, '-', id) as text from repos",
+ "query": "select concat(name, '--', id) as text from repos",
"refresh": 1,
- "regex": "/^(?<text>.*)-(?<value>.*)$/",
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
"skipUrlSync": false,
"sort": 0,
"type": "query"
@@ -2277,7 +2281,7 @@
},
"timepicker": {},
"timezone": "",
- "title": "GitHub_Release_Quality_and_Contribution_Analysis",
- "uid": "2xuOaQUnk",
- "version": 1
+ "title": "New_GitHub_Release_Quality_and_Contribution_Analysis",
+ "uid": "2xuOaQUnk1",
+ "version": 50
}
\ No newline at end of file
diff --git a/grafana/dashboards/Jenkins.json b/grafana/dashboards/Jenkins.json
new file mode 100644
index 0000000..a8550e0
--- /dev/null
+++ b/grafana/dashboards/Jenkins.json
@@ -0,0 +1,883 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": "-- Grafana --",
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "gnetId": null,
+ "graphTooltip": 0,
+ "id": 12,
+ "iteration": 1650627258965,
+ "links": [],
+ "panels": [
+ {
+ "datasource": "mysql",
+ "description": "Number of builds executed in the selected time range",
+ "fieldConfig": {
+ "defaults": {
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 6,
+ "x": 0,
+ "y": 0
+ },
+ "id": 4,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "mean"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n now() AS time,\n count(*)\nFROM \n builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\nORDER BY 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "project_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "gitlab_commits",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "1. Total Number of Builds [Selected Time Range]",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "Number of successful builds / Number of total builds",
+ "fieldConfig": {
+ "defaults": {
+ "mappings": [],
+ "max": 100,
+ "min": 0,
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "blue",
+ "value": null
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 6,
+ "x": 6,
+ "y": 0
+ },
+ "id": 6,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [
+ "lastNotNull"
+ ],
+ "fields": "",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n now() AS time,\n 1.0 * count(case when b.status = 'SUCCESS' then 1 else null end)/count(*)\nFROM builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\nORDER BY 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "project_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "gitlab_commits",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "2. Mean Build Success Rate",
+ "type": "stat"
+ },
+ {
+ "cacheTimeout": null,
+ "datasource": "mysql",
+ "description": "The percentage of successful, failed, and aborted builds.",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ }
+ },
+ "decimals": 0,
+ "mappings": [],
+ "unit": "none"
+ },
+ "overrides": [
+ {
+ "__systemRef": "hideSeriesFrom",
+ "matcher": {
+ "id": "byNames",
+ "options": {
+ "mode": "exclude",
+ "names": [
+ "build_count"
+ ],
+ "prefix": "All except:",
+ "readOnly": true
+ }
+ },
+ "properties": [
+ {
+ "id": "custom.hideFrom",
+ "value": {
+ "legend": false,
+ "tooltip": false,
+ "viz": true
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 6,
+ "x": 12,
+ "y": 0
+ },
+ "id": 37,
+ "interval": null,
+ "links": [],
+ "options": {
+ "displayLabels": [
+ "value",
+ "percent"
+ ],
+ "legend": {
+ "calcs": [],
+ "displayMode": "table",
+ "placement": "right",
+ "values": [
+ "percent",
+ "value"
+ ]
+ },
+ "pieType": "donut",
+ "reduceOptions": {
+ "calcs": [
+ "sum"
+ ],
+ "fields": "",
+ "values": true
+ },
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n now() AS time,\n status,\n count(*) as build_count\nFROM builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\nGROUP BY 1,2\nORDER BY 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "project_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "gitlab_commits",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "3. Total Build Result Distribution",
+ "type": "piechart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "Number of successful builds / Number of total builds",
+ "fieldConfig": {
+ "defaults": {
+ "mappings": [],
+ "max": 100,
+ "min": 0,
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "light-orange",
+ "value": null
+ }
+ ]
+ },
+ "unit": "none"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 6,
+ "x": 18,
+ "y": 0
+ },
+ "id": 55,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": false
+ },
+ "text": {},
+ "textMode": "auto"
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "time_series",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n now() AS time,\n avg(duration_sec/60) as duration_in_minute\nFROM builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\nORDER BY 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "project_id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "gitlab_commits",
+ "timeColumn": "created_at",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "4. Mean Build Duration in Minutes",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Build Count",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 12,
+ "x": 0,
+ "y": 6
+ },
+ "id": 52,
+ "options": {
+ "barWidth": 0.5,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _builds as(\n SELECT\n DATE_ADD(date(b.started_date), INTERVAL -DAYOFMONTH(date(b.started_date))+1 DAY) as time,\n count(*) as build_count\n FROM builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n build_count as \"Build Count\"\nFROM _builds\nORDER BY time\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "1.1 Total Number of Builds [Each Month]",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "1. Mean Build success rate over time.\n2. The builds being calculated are filtered by \"build starting time\" (time filter at the upper-right corner) and \"Jira board\" (\"Choose Board\" filter at the upper-left corner)",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Build Success Rate(%)",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ },
+ "unit": "percentunit"
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "Build Success Rate"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "blue",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 12,
+ "x": 12,
+ "y": 6
+ },
+ "id": 50,
+ "interval": "",
+ "options": {
+ "barWidth": 0.5,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [
+ "mean"
+ ],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _build_success_rate as(\r\n SELECT\r\n DATE_ADD(date(b.started_date), INTERVAL -DAYOFMONTH(date(b.started_date))+1 DAY) as time,\r\n b.status\r\n FROM\r\n builds b\r\n join jobs j on b.job_id = j.id \r\nWHERE\r\n $__timeFilter(b.started_date)\r\n and j.id in ($job_id)\r\n)\r\n\r\nSELECT \r\n date_format(time,'%M %Y') as month,\r\n 1.0 * sum(case when status = 'SUCCESS' then 1 else 0 end)/ count(*) as \"Build Success Rate\"\r\nFROM _build_success_rate\r\ngroup by 1\r\norder by 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "progress"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ca_analysis",
+ "timeColumn": "create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "timeFrom": null,
+ "timeShift": null,
+ "title": "2.1 Build Success Rate [Each Month]",
+ "type": "barchart"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "fixed"
+ },
+ "custom": {
+ "axisLabel": "Build Count",
+ "axisPlacement": "auto",
+ "barAlignment": 1,
+ "drawStyle": "bars",
+ "fillOpacity": 50,
+ "gradientMode": "opacity",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineInterpolation": "linear",
+ "lineWidth": 1,
+ "pointSize": 4,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "showPoints": "auto",
+ "spanNulls": false,
+ "stacking": {
+ "group": "A",
+ "mode": "normal"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "successful_build_count"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "green",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "failed_build_count"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "red",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 12
+ },
+ "id": 54,
+ "options": {
+ "legend": {
+ "calcs": [
+ "sum"
+ ],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "tooltip": {
+ "mode": "multi"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "time_series",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "SELECT\n DATE_ADD(date(b.started_date), INTERVAL -DAYOFMONTH(date(b.started_date))+1 DAY) as time,\n count(distinct case when b.status = 'SUCCESS' then b.id else null end) as successful_build_count,\n count(distinct case when b.status != 'SUCCESS' then b.id else null end) as failed_build_count\nFROM builds b\n join jobs j on b.job_id = j.id\nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\ngroup by 1",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "3.1 Number of Successful and Failed Builds [Each Month]",
+ "type": "timeseries"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisLabel": "Build Count",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "purple",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 60
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "duration_in_minute"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "light-orange",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 6,
+ "w": 24,
+ "x": 0,
+ "y": 18
+ },
+ "id": 56,
+ "options": {
+ "barWidth": 0.5,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom"
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single"
+ }
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _builds as(\n SELECT\n DATE_ADD(date(started_date), INTERVAL -DAYOFMONTH(date(started_date))+1 DAY) as time,\n avg(duration_sec) as duration\n FROM \n builds b\n join jobs j on b.job_id = j.id \nWHERE\n $__timeFilter(b.started_date)\n and j.id in ($job_id)\n group by 1\n)\n\nSELECT \n date_format(time,'%M %Y') as month,\n duration/60 as duration_in_minute\nFROM _builds\nORDER BY time\n",
+ "refId": "A",
+ "select": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "4.1 Mean Build Duration in Minutes [Each Month]",
+ "type": "barchart"
+ }
+ ],
+ "schemaVersion": 30,
+ "style": "dark",
+ "tags": [],
+ "templating": {
+ "list": [
+ {
+ "allValue": null,
+ "current": {
+ "selected": true,
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "datasource": "mysql",
+ "definition": "select concat(name, '--', id) as text from jobs ",
+ "description": null,
+ "error": null,
+ "hide": 0,
+ "includeAll": true,
+ "label": "Job Name",
+ "multi": true,
+ "name": "job_id",
+ "options": [],
+ "query": "select concat(name, '--', id) as text from jobs ",
+ "refresh": 1,
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
+ "skipUrlSync": false,
+ "sort": 0,
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {},
+ "timezone": "",
+ "title": "Jenkins",
+ "uid": "W8AiDFQnk",
+ "version": 2
+}
\ No newline at end of file
