Integrated visualisation / easier extraction of time tracking data
Description
There are various use cases in different issues over here but many of them can be summed up as having a need to be able to extract global data with aggregated sums from time tracking notes.
First, some are concerned with estimates, while others are concerned with time spent.
My use cases currently revolve around time spent but I believe cross-checking time spent against estimates may come into the picture at some point with proper design.
I am currently using the attached hack of a bash script to produce CSV output that allows for further analysis. Excerpt output from ./track.sh | grep lnageleisen | sort -t , -k 5
:
issue_id,issue_iid,username,time,date,project_id,issue_title
14537,2676,lnageleisen,4h,2018-01-04,246,Mails de mr02 mis en quarantaine
14516,2662,lnageleisen,4h,2018-01-10,246,Faciliter les accès pour des diagnostics plus simples
14537,2676,lnageleisen,-4h,2018-01-10,246,Mails de mr02 mis en quarantaine
14537,2676,lnageleisen,4h,2018-01-10,246,Mails de mr02 mis en quarantaine
12554,2298,lnageleisen,10m,2018-01-11,246,Investigation des faux build fail récurrents
14459,2647,lnageleisen,20m,2018-01-11,246,Mélange de références
14518,2663,lnageleisen,4h,2018-01-11,246,Remise à niveau du prototype
14444,2644,lnageleisen,30m,2018-01-11,246,Renouveler la licence GitLab
14586,2694,lnageleisen,2h,2018-01-12,246,Extraction des suivi de temps GitLab
14518,2663,lnageleisen,1h,2018-01-12,246,Remise à niveau du prototype
14518,2663,lnageleisen,4h,2018-01-12,246,Remise à niveau du prototype
14459,2647,lnageleisen,2h,2018-01-15,246,Mélange IBAN dans Mandat
14518,2663,lnageleisen,2h 30m,2018-01-15,246,Remise à niveau du prototype
14518,2663,lnageleisen,3h,2018-01-15,246,Remise à niveau du prototype
13122,2410,lnageleisen,30m,2018-01-15,246,Ruby et Rails: état de l'art et problématiques récurrentes
14503,2655,lnageleisen,15m,2018-01-15,246,Spectre+Meltdown
14503,2655,lnageleisen,20m,2018-01-15,246,Spectre+Meltdown
14612,2703,lnageleisen,30m,2018-01-16,246,Activer Ruby 2.4
14608,2701,lnageleisen,1h,2018-01-16,246,Echecs répétés sur certains runners
14608,2701,lnageleisen,30m,2018-01-16,246,Echecs répétés sur certains runners
14537,2676,lnageleisen,10m,2018-01-16,246,Mails de mr02.adhoc-gti.com mis en quarantaine
14518,2663,lnageleisen,4h,2018-01-16,246,Remise à niveau du prototype
14503,2655,lnageleisen,15m,2018-01-16,246,Spectre+Meltdown
14505,2656,lnageleisen,20m,2018-01-17,246,Backup restic
14641,2707,lnageleisen,1h,2018-01-17,246,Documentation PSI Digital Ocean
14518,2663,lnageleisen,6h,2018-01-17,246,Remise à niveau du prototype
14671,2715,lnageleisen,5h,2018-01-18,246,Kubernetes: prise en main et mise en place
14671,2715,lnageleisen,1h,2018-01-19,246,Kubernetes: prise en main et mise en place
14671,2715,lnageleisen,2h,2018-01-19,246,Kubernetes: prise en main et mise en place
14671,2715,lnageleisen,3h,2018-01-19,246,Kubernetes: prise en main et mise en place
14671,2715,lnageleisen,7h,2018-01-22,246,Kubernetes: prise en main et mise en place
10358,2073,lnageleisen,1h,2018-01-23,246,Accès à la nouvelle API
14671,2715,lnageleisen,2h,2018-01-23,246,Kubernetes: prise en main et mise en place
14518,2663,lnageleisen,1h,2018-01-23,246,Remise à niveau du prototype
14718,2730,lnageleisen,1h 30m,2018-01-25,246,Problème de transfert de données
14718,2730,lnageleisen,30m,2018-01-25,246,Problème de transfert de données
Proposal
Have a global, cross project view of time spent records, allowing to filter, group by, and tally time spent by:
- username
- day
- week
- month
- issue
- project
- ...?
The goal is to be able to answer questions such as how much time has been spent:
- by any user on project X?
- by user foo this week?
- by user foo on each day, per issue?
- by any user on issue Y of project Z?
- ...?
This can be combined with gitlab-ce#42465 for even more insights.
Links / references
This bash script crawls the whole issue list based on the configuration file, which takes quite some time, but dumbly caches the result for faster subsequent lookups. You can feel the limits of the API and of the current data model around time tracking in the extraction process.
{
"token": "p3rs0n4lt0k3n",
"projects": [
"foo/project1",
"bar/project2",
]
}
#!/bin/bash
set -e
set -u
set -o pipefail
# GET /projects/:id/issues
# GET /projects/:id/issues/:issue_iid/notes?sort=asc
API_TOKEN=$(jq -r .token config.json)
PROJECTS=( $(jq -r '.projects | .[]' config.json) )
function h_link() {
local rel="$1"
perl -ne '/^link: .*<([^>]+)>; rel="'"${rel}"'"/ and print "$1\n"'
}
function h_x_next_page() {
perl -ne '/^x-next-page: (\d+)/ and print "$1\n"'
}
function get() {
local headers=""
if [[ "$1" == "-h" ]]; then
headers="$2"
shift
shift
else
headers=$(mktemp)
fi
local path="$1"
shift
local query=""
for q in "$@"; do
if [[ -z "${query}" ]]; then
query="${q}"
else
query="${query}&${q}"
fi
done
if [[ -n "${query}" ]]; then
query="?${query}"
fi
local token="${API_TOKEN}"
curl --dump-header "$headers" --silent --header "Private-Token: ${token}" "https://gitlab.adhoc-gti.com/api/v4/${path}${query}"
}
function get_all() {
local path="$1"
local page="1"
local headers=""
headers="$(mktemp)"
while [[ -n "$page" ]]; do
get -h "$headers" "$path" page="${page}"
page=$(h_x_next_page < "$headers")
done | jq '.[]' | jq -s '[.[]]'
}
function get_all_project_id() {
local project="$1"
cache "projects" get_all projects | jq -r '.[] | select(.path_with_namespace == "'"${project}"'") | .id'
}
function get_issue() {
local issue_id="$1"
jq -r ".[] | select(.id == ${issue_id})" cache/project_*_issues
}
function get_time_notes() {
jq '.[] | select(.system == true and (.body | test("(added|subtracted) .* time spent at")))' cache/project_*_issues_*_notes
}
function cache() {
local key="$1"
shift
if [[ -f "cache/${key}" ]]; then
cat "cache/$key"
else
mkdir -p cache
"$@" | tee "cache/$key"
fi
}
#get projects | jq 'map({id: .id, path: .path_with_namespace})'
#get_all projects | jq '.[] | {id: .id, path: .path_with_namespace}' | jq -s
projects=( $(get_all_project_id "adhoc-gti/suivi") )
#echo "projects: ${projects[*]}"
for project_id in ${projects[*]}; do
#echo "** project ${project_id}"
issues=( $(cache "project_${project_id}_issues" get_all projects/${project_id}/issues | jq -r '.[] | .iid') )
#echo "** issues: ${issues[*]}"
for issue_id in ${issues[*]}; do
#echo "**** issue ${issue_id}"
cache "project_${project_id}_issues_${issue_id}_notes" get_all projects/${project_id}/issues/${issue_id}/notes > /dev/null
done
done
get_time_notes | jq -r '[(.noteable_id|tostring), (.noteable_iid|tostring), .author.username, .body] | join(",")' | perl -p -e 's/added //;s/subtracted /-/;s/ of time spent at /,/' | while read -r line; do
id="${line%%,*}"
rest="${line#,*}"
echo "$line,$(get_issue "$id" | jq -r '[(.project_id|tostring), .title] | join(",")')"
done