feat: add the work log dashboard and update the homepage dashboard (#7180)
* fix: update the title of Bitbucket dashboard
* fix: update the wording and categories on the homepage dashboard
* fix: adjust the dashboard tag
* feat: add the work log dashboard
---------
Co-authored-by: Startrekzky <kaiyun.zhang@merico.dev>
diff --git a/grafana/dashboards/BitBucket.json b/grafana/dashboards/BitBucket.json
index 775bf09..dc0117b 100644
--- a/grafana/dashboards/BitBucket.json
+++ b/grafana/dashboards/BitBucket.json
@@ -1215,7 +1215,7 @@
},
"timepicker": {},
"timezone": "",
- "title": "BitBucket",
+ "title": "Bitbucket",
"uid": "4LzQHZa4k",
"version": 3,
"weekStart": ""
diff --git a/grafana/dashboards/Homepage.json b/grafana/dashboards/Homepage.json
index c974493..156f000 100644
--- a/grafana/dashboards/Homepage.json
+++ b/grafana/dashboards/Homepage.json
@@ -18,7 +18,7 @@
"editable": true,
"fiscalYearStartMonth": 0,
"graphTooltip": 0,
- "id": 9,
+ "id": 26,
"links": [],
"liveNow": false,
"panels": [
@@ -40,10 +40,10 @@
"showLineNumbers": false,
"showMiniMap": false
},
- "content": "# Welcome to Apache DevLake\n\n - DevLake provides following dashboards, grouped by use cases and data sources.\n - The data displayed in these dashboards are queried from DevLake's domain layer data, see <a href=\"https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema\" target=\"_blank\">data schema</a>.\n - You can also make your own dashboard based on the <a href=\"https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema\" target=\"_blank\">domain layer schema</a>, the SQL exmaples of <a href=\"https://devlake.apache.org/docs/Metrics\" target=\"_blank\">engineering metrics</a>, and <a href=\"https://devlake.apache.org/docs/Configuration/Dashboards/GrafanaUserGuide\" target=\"_blank\">Grafana manuals</a>.\n - Feel free to <a href=\"https://github.com/apache/incubator-devlake/issues/new/choose\" target=\"_blank\">fire an issue</a> if you have any question.\n",
+ "content": "# Welcome\n\n - Dashboards are grouped by use cases and data sources.\n - All data is queried from <a href=\"https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema\" target=\"_blank\">here</a>. You can go to the 'Explore' page on the left menu to play around.\n - You can always make your own dashboard based on the <a href=\"https://devlake.apache.org/docs/DataModels/DevLakeDomainLayerSchema\" target=\"_blank\">domain layer schema</a>, SQL exmaples in <a href=\"https://devlake.apache.org/docs/Metrics\" target=\"_blank\">engineering metrics</a>, and <a href=\"https://devlake.apache.org/docs/Configuration/Dashboards/GrafanaUserGuide\" target=\"_blank\">dashboard manuals</a>.\n",
"mode": "markdown"
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -78,7 +78,7 @@
"showStarred": true,
"tags": []
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -116,7 +116,7 @@
"Highlights"
]
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"targets": [
{
"datasource": {
@@ -179,7 +179,7 @@
"Engineering Leads Dashboard"
]
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"title": "For Engineering Leads",
"type": "dashlist"
},
@@ -206,7 +206,7 @@
"OSS Maintainer Dashboard"
]
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"title": "For OSS Maintainers",
"type": "dashlist"
},
@@ -230,11 +230,11 @@
"showSearch": true,
"showStarred": false,
"tags": [
- "QA Dashboard"
+ "Developer"
]
},
- "pluginVersion": "9.5.2",
- "title": "For QA Engineers",
+ "pluginVersion": "9.5.15",
+ "title": "For Developers",
"type": "dashlist"
},
{
@@ -286,7 +286,7 @@
"Data Source Dashboard"
]
},
- "pluginVersion": "9.5.2",
+ "pluginVersion": "9.5.15",
"type": "dashlist"
}
],
@@ -305,6 +305,6 @@
"timezone": "",
"title": "Homepage",
"uid": "lCO8w-pVk",
- "version": 16,
+ "version": 4,
"weekStart": ""
}
\ No newline at end of file
diff --git a/grafana/dashboards/WeeklyBugRetro.json b/grafana/dashboards/WeeklyBugRetro.json
index 9a6d8ff..bf8fbe9 100644
--- a/grafana/dashboards/WeeklyBugRetro.json
+++ b/grafana/dashboards/WeeklyBugRetro.json
@@ -1986,7 +1986,7 @@
"schemaVersion": 38,
"style": "dark",
"tags": [
- "QA Dashboard"
+ "Engineering Leads Dashboard"
],
"templating": {
"list": [
diff --git a/grafana/dashboards/WorkLogs.json b/grafana/dashboards/WorkLogs.json
new file mode 100644
index 0000000..ffe9148
--- /dev/null
+++ b/grafana/dashboards/WorkLogs.json
@@ -0,0 +1,1152 @@
+{
+ "annotations": {
+ "list": [
+ {
+ "builtIn": 1,
+ "datasource": {
+ "type": "grafana",
+ "uid": "-- Grafana --"
+ },
+ "enable": true,
+ "hide": true,
+ "iconColor": "rgba(0, 211, 255, 1)",
+ "name": "Annotations & Alerts",
+ "type": "dashboard"
+ }
+ ]
+ },
+ "editable": true,
+ "fiscalYearStartMonth": 0,
+ "graphTooltip": 0,
+ "id": 38,
+ "links": [],
+ "liveNow": false,
+ "panels": [
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "gridPos": {
+ "h": 5,
+ "w": 24,
+ "x": 0,
+ "y": 0
+ },
+ "id": 19,
+ "links": [],
+ "options": {
+ "code": {
+ "language": "plaintext",
+ "showLineNumbers": false,
+ "showMiniMap": false
+ },
+ "content": "- Use Cases: This dashboard shows the work logs of a team or a developer\n- Data Sources Required to show all data: \n - One of the Git tools, e.g. [GitHub](https://devlake.apache.org/docs/Configuration/GitHub), [GitLab](https://devlake.apache.org/docs/Configuration/GitLab), [Bitbucket](https://devlake.apache.org/docs/Configuration/BitBucket) or [Azure DevOps](https://devlake.apache.org/docs/Configuration/AzureDevOps)\n - One of the issue tracking tools, e.g. [Jira](https://devlake.apache.org/docs/Configuration/Jira)\n - You also need to complete the [team configuration](https://devlake.apache.org/docs/Configuration/TeamConfiguration) to use this dashboard",
+ "mode": "markdown"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": {
+ "type": "datasource",
+ "uid": "grafana"
+ },
+ "queryType": "randomWalk",
+ "refId": "A"
+ }
+ ],
+ "title": "Dashboard Introduction",
+ "type": "text"
+ },
+ {
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 5
+ },
+ "id": 9,
+ "title": "Worklogs",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "custom": {
+ "align": "auto",
+ "cellOptions": {
+ "type": "auto"
+ },
+ "inspect": false
+ },
+ "mappings": [
+ {
+ "options": {
+ "Comment on PR": {
+ "color": "super-light-orange",
+ "index": 5
+ },
+ "Create an issue": {
+ "color": "light-blue",
+ "index": 0
+ },
+ "Finish a commit": {
+ "color": "light-purple",
+ "index": 2
+ },
+ "Issue resolved": {
+ "color": "semi-dark-blue",
+ "index": 1
+ },
+ "Open a PR": {
+ "color": "light-green",
+ "index": 3
+ },
+ "PR gets merged": {
+ "color": "semi-dark-green",
+ "index": 4
+ },
+ "Review PR": {
+ "color": "orange",
+ "index": 6
+ }
+ },
+ "type": "value"
+ }
+ ],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "Activity"
+ },
+ "properties": [
+ {
+ "id": "custom.filterable",
+ "value": true
+ },
+ {
+ "id": "custom.cellOptions",
+ "value": {
+ "type": "color-background"
+ }
+ },
+ {
+ "id": "custom.width",
+ "value": 180
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "Date"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 153
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "Time"
+ },
+ "properties": [
+ {
+ "id": "custom.width",
+ "value": 228
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 15,
+ "w": 24,
+ "x": 0,
+ "y": 6
+ },
+ "id": 3,
+ "options": {
+ "cellHeight": "sm",
+ "footer": {
+ "countRows": false,
+ "fields": "",
+ "reducer": [
+ "sum"
+ ],
+ "show": false
+ },
+ "frameIndex": 1,
+ "showHeader": true,
+ "sortBy": []
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n_activities as (\n SELECT \n *,\n ROW_NUMBER() OVER (PARTITION BY `Date` ORDER BY `Time` desc) AS _row_number\n FROM (\n SELECT DATE_FORMAT(created_date, '%d/%m/%Y') as Date, created_date as Time, 'Create an issue' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name\n FROM issues i\n join _accounts a on i.creator_id = a.account_id\n where $__timeFilter(created_date)\n\n union\n\n SELECT DATE_FORMAT(resolution_date, '%d/%m/%Y') as Date, resolution_date as Time, 'Issue resolved' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name \n FROM issues i\n join _accounts a on i.assignee_id = a.account_id\n where $__timeFilter(resolution_date)\n\n union\n\n SELECT DATE_FORMAT(authored_date, '%d/%m/%Y') as Date, authored_date as Time, 'Finish a commit' as Activity, concat(message, ' #', sha) as Details, a.name as Name \n FROM commits c\n join _accounts a on c.author_id = a.account_id\n where $__timeFilter(authored_date)\n\n union\n\n SELECT DATE_FORMAT(created_date, '%d/%m/%Y') as Date, created_date as Time, 'Open a PR' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name\n FROM pull_requests pr\n join _accounts a on pr.author_id = a.account_id\n where $__timeFilter(created_date)\n\n union\n\n SELECT DATE_FORMAT(merged_date, '%d/%m/%Y') as Date, merged_date as Time, 'PR gets merged' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name \n FROM pull_requests pr\n join _accounts a on pr.author_id = a.account_id\n where $__timeFilter(merged_date)\n\n union\n\n SELECT DATE_FORMAT(prc.created_date, '%d/%m/%Y') as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name \n FROM pull_request_comments prc\n left join pull_requests pr on prc.pull_request_id = pr.id\n join _accounts a on prc.account_id = a.account_id\n WHERE \n prc.type = 'NORMAL'\n and $__timeFilter(prc.created_date)\n\n union\n\n SELECT DATE_FORMAT(prc.created_date, '%d/%m/%Y') as Date, prc.created_date as Time, 'Review PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name \n FROM pull_request_comments prc\n left join pull_requests pr on prc.pull_request_id = pr.id\n join _accounts a on prc.account_id = a.account_id\n WHERE \n prc.type in ('REVIEW', 'DIFF')\n and $__timeFilter(prc.created_date)\n ) t\n\n ORDER BY Time desc\n)\n\nSELECT \n CASE WHEN _row_number = 1 THEN `Date` ELSE NULL END AS `Date`, `Time`, Activity, Details, Name\nFROM _activities",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Work Logs",
+ "type": "table"
+ },
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 21
+ },
+ "id": 10,
+ "panels": [],
+ "title": "Throughput",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ }
+ },
+ "mappings": []
+ },
+ "overrides": [
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "TODO"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "#f2f1eb",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "DONE"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "green",
+ "mode": "fixed"
+ }
+ }
+ ]
+ },
+ {
+ "matcher": {
+ "id": "byName",
+ "options": "IN_PROGRESS"
+ },
+ "properties": [
+ {
+ "id": "color",
+ "value": {
+ "fixedColor": "yellow",
+ "mode": "fixed"
+ }
+ }
+ ]
+ }
+ ]
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 4,
+ "x": 0,
+ "y": 22
+ },
+ "id": 4,
+ "options": {
+ "legend": {
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true,
+ "values": [
+ "value",
+ "percent"
+ ]
+ },
+ "pieType": "donut",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ }
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_issues as (\n SELECT DATE_FORMAT(created_date, '%d/%m/%Y') as Date, created_date as Time, 'Create an issue' as Activity, concat('#', issue_key, ' ', title) as Details, status, a.name as Name\n FROM issues i\n join _accounts a on i.creator_id = a.account_id\n where $__timeFilter(created_date)\n)\n\nSELECT status, count(*) FROM _issues\nGROUP BY 1",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Statuses of assigned issues",
+ "type": "piechart"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 4,
+ "y": 22
+ },
+ "id": 5,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n)\n\nSELECT count(distinct c.sha)\nFROM commits c\njoin _accounts a on c.author_id = a.account_id\nwhere $__timeFilter(authored_date)\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Code Commits",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 9,
+ "y": 22
+ },
+ "id": 6,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_commits as (\n SELECT distinct DATE_FORMAT(authored_date, '%d/%m/%Y') as Date, authored_date as Time, 'Finish a commit' as Activity, concat(message, ' #', sha) as Details, a.name as Name, c.additions, c.deletions\n FROM commits c\n join _accounts a on c.author_id = a.account_id\n where $__timeFilter(authored_date)\n)\n\nSELECT sum(additions + deletions) FROM _commits\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Lines of Code",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 14,
+ "y": 22
+ },
+ "id": 14,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "/.*/",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_prs as (\n SELECT DATE_FORMAT(prc.created_date, '%d/%m/%Y') as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name, pr.id\n FROM pull_request_comments prc\n left join pull_requests pr on prc.pull_request_id = pr.id\n join _accounts a on prc.account_id = a.account_id\n WHERE \n prc.type = 'NORMAL'\n and $__timeFilter(prc.created_date)\n)\n\nSELECT \n count(distinct id) FROM _prs\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Reviewed or commented PRs",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 19,
+ "y": 22
+ },
+ "id": 15,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "/.*/",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_prs as (\n SELECT DATE_FORMAT(prc.created_date, '%d/%m/%Y') as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name, pr.id as pr_id, prc.id as prc_id\n FROM pull_request_comments prc\n left join pull_requests pr on prc.pull_request_id = pr.id\n join _accounts a on prc.account_id = a.account_id\n WHERE \n prc.type = 'NORMAL'\n and $__timeFilter(prc.created_date)\n)\n\nSELECT \n count(distinct prc_id) FROM _prs\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Reviews & comments",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ }
+ ]
+ },
+ "unit": "short"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 9,
+ "x": 0,
+ "y": 30
+ },
+ "id": 8,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "/.*/",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_prs as (\n SELECT distinct DATE_FORMAT(created_date, '%d/%m/%Y') as Date, created_date as Time, 'Open a PR' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name, pr.id, pr.merged_date\n FROM pull_requests pr\n join _accounts a on pr.author_id = a.account_id\n where \n $__timeFilter(created_date)\n)\n\nSELECT \n concat(count(case when merged_date is not null then id else null end), '/', count(distinct id)) FROM _prs\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Merged PRs / Open PRs",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "palette-classic"
+ },
+ "custom": {
+ "axisCenteredZero": false,
+ "axisColorMode": "text",
+ "axisLabel": "",
+ "axisPlacement": "auto",
+ "axisSoftMin": 0,
+ "fillOpacity": 80,
+ "gradientMode": "none",
+ "hideFrom": {
+ "legend": false,
+ "tooltip": false,
+ "viz": false
+ },
+ "lineWidth": 1,
+ "scaleDistribution": {
+ "type": "linear"
+ },
+ "thresholdsStyle": {
+ "mode": "off"
+ }
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green",
+ "value": null
+ },
+ {
+ "color": "red",
+ "value": 80
+ }
+ ]
+ }
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 15,
+ "x": 9,
+ "y": 30
+ },
+ "id": 18,
+ "links": [
+ {
+ "targetBlank": true,
+ "title": "PR Count",
+ "url": "https://devlake.apache.org/docs/Metrics/PRCount"
+ }
+ ],
+ "options": {
+ "barRadius": 0,
+ "barWidth": 0.5,
+ "fullHighlight": false,
+ "groupWidth": 0.7,
+ "legend": {
+ "calcs": [],
+ "displayMode": "list",
+ "placement": "bottom",
+ "showLegend": true
+ },
+ "orientation": "auto",
+ "showValue": "auto",
+ "stacking": "none",
+ "text": {
+ "valueSize": 12
+ },
+ "tooltip": {
+ "mode": "single",
+ "sort": "none"
+ },
+ "xTickLabelRotation": 0,
+ "xTickLabelSpacing": 0
+ },
+ "pluginVersion": "8.0.6",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "group": [],
+ "hide": false,
+ "metricColumn": "none",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n_prs as (\n SELECT \n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n count(distinct pr.id) as pr_count\n FROM pull_requests pr\n join _accounts a on pr.author_id = a.account_id\n where \n $__timeFilter(created_date)\n -- and created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)\n and pr.merged_date is not null\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": [
+ [
+ {
+ "params": [
+ "id"
+ ],
+ "type": "column"
+ }
+ ]
+ ],
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ },
+ "table": "ae_projects",
+ "timeColumn": "ae_create_time",
+ "timeColumnType": "timestamp",
+ "where": [
+ {
+ "name": "$__timeFilter",
+ "params": [],
+ "type": "macro"
+ }
+ ]
+ }
+ ],
+ "title": "Merged PRs per month",
+ "type": "barchart"
+ },
+ {
+ "collapsed": false,
+ "gridPos": {
+ "h": 1,
+ "w": 24,
+ "x": 0,
+ "y": 38
+ },
+ "id": 11,
+ "panels": [],
+ "title": "Work Culture",
+ "type": "row"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green"
+ }
+ ]
+ },
+ "unit": "lines of code"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 0,
+ "y": 39
+ },
+ "id": 12,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_commits as (\n SELECT distinct DATE_FORMAT(authored_date, '%d/%m/%Y') as Date, authored_date as Time, 'Finish a commit' as Activity, concat(message, ' #', sha) as Details, a.name as Name, c.additions, c.deletions, c.sha\n FROM commits c\n join _accounts a on c.author_id = a.account_id\n where $__timeFilter(authored_date)\n),\n\n_pr_commits_data as(\n SELECT\n pr.id as pr_id,\n pr.merge_commit_sha,\n sum(c.additions)+sum(c.deletions) as loc\n FROM \n pull_requests pr\n left join _commits c on pr.merge_commit_sha = c.sha\n WHERE\n $__timeFilter(pr.created_date)\n and pr.status = 'MERGED'\n group by 1,2\n)\n\nSELECT \n avg(loc) as 'PR Size'\nFROM _pr_commits_data\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Average PR Size",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green"
+ }
+ ]
+ },
+ "unit": "comments per PR"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 5,
+ "y": 39
+ },
+ "id": 13,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n),\n\n\n_prs as (\n SELECT DATE_FORMAT(prc.created_date, '%d/%m/%Y') as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name, pr.id as pr_id, prc.id as prc_id\n FROM pull_request_comments prc\n left join pull_requests pr on prc.pull_request_id = pr.id\n join _accounts a on prc.account_id = a.account_id\n WHERE \n prc.type = 'NORMAL'\n and $__timeFilter(prc.created_date)\n)\n\nSELECT \n round(count(distinct prc_id)/count(distinct pr_id) , 1) FROM _prs\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Average Review Depth",
+ "type": "stat"
+ },
+ {
+ "datasource": "mysql",
+ "description": "average pr review time from first comment to the last comment",
+ "fieldConfig": {
+ "defaults": {
+ "color": {
+ "mode": "thresholds"
+ },
+ "mappings": [],
+ "thresholds": {
+ "mode": "absolute",
+ "steps": [
+ {
+ "color": "green"
+ }
+ ]
+ },
+ "unit": "m"
+ },
+ "overrides": []
+ },
+ "gridPos": {
+ "h": 8,
+ "w": 5,
+ "x": 10,
+ "y": 39
+ },
+ "id": 17,
+ "options": {
+ "colorMode": "value",
+ "graphMode": "area",
+ "justifyMode": "auto",
+ "orientation": "auto",
+ "reduceOptions": {
+ "calcs": [],
+ "fields": "",
+ "values": true
+ },
+ "textMode": "auto"
+ },
+ "pluginVersion": "9.5.15",
+ "targets": [
+ {
+ "datasource": "mysql",
+ "editorMode": "code",
+ "format": "table",
+ "rawQuery": true,
+ "rawSql": "with _accounts as (\n select ua.account_id, ua.user_id, u.name\n from accounts a \n join user_accounts ua on a.id = ua.account_id\n join users u on ua.user_id = u.id\n where ua.user_id in ($users)\n)\n\n\nSELECT avg(TIMESTAMPDIFF(Minute,pr.created_date,pr.merged_date)/60) as 'Time to merge'\nFROM \n pull_requests pr\n join _accounts a on pr.author_id = a.account_id\nWHERE \n $__timeFilter(pr.created_date)\n",
+ "refId": "A",
+ "sql": {
+ "columns": [
+ {
+ "parameters": [],
+ "type": "function"
+ }
+ ],
+ "groupBy": [
+ {
+ "property": {
+ "type": "string"
+ },
+ "type": "groupBy"
+ }
+ ],
+ "limit": 50
+ }
+ }
+ ],
+ "title": "Average PR time to merge",
+ "type": "stat"
+ }
+ ],
+ "refresh": "",
+ "schemaVersion": 38,
+ "style": "dark",
+ "tags": [
+ "Developer",
+ "Highlights"
+ ],
+ "templating": {
+ "list": [
+ {
+ "current": {
+ "selected": false,
+ "text": "red",
+ "value": "1"
+ },
+ "datasource": "mysql",
+ "definition": "select concat(name, '--', id) from teams",
+ "hide": 0,
+ "includeAll": false,
+ "label": "Team",
+ "multi": false,
+ "name": "team",
+ "options": [],
+ "query": "select concat(name, '--', id) from teams",
+ "refresh": 1,
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
+ "skipUrlSync": false,
+ "sort": 1,
+ "type": "query"
+ },
+ {
+ "current": {
+ "selected": true,
+ "text": [
+ "All"
+ ],
+ "value": [
+ "$__all"
+ ]
+ },
+ "datasource": "mysql",
+ "definition": "select concat(users.name, '--', users.id) from users left join team_users on users.id = team_users.user_id where team_users.team_id in ($team)",
+ "hide": 0,
+ "includeAll": true,
+ "label": "User",
+ "multi": true,
+ "name": "users",
+ "options": [],
+ "query": "select concat(users.name, '--', users.id) from users left join team_users on users.id = team_users.user_id where team_users.team_id in ($team)",
+ "refresh": 1,
+ "regex": "/^(?<text>.*)--(?<value>.*)$/",
+ "skipUrlSync": false,
+ "sort": 1,
+ "type": "query"
+ }
+ ]
+ },
+ "time": {
+ "from": "now-6M",
+ "to": "now"
+ },
+ "timepicker": {},
+ "timezone": "",
+ "title": "Work Logs",
+ "uid": "d449042e-22f0-4357-b8b7-22083f47618d",
+ "version": 7,
+ "weekStart": ""
+}
\ No newline at end of file
