This page describes the role of the help desk coordinator. This role is currently handled by Colin "Phoul" Childs.
# Tasks
The support help desk coordinator handles the following tasks:
* Listowner of the _support-team-private_ mailing list.
* Administrator for the Request Tracker installation at [rt.torproject.org](./org/operations/Infrastructure/rt.torproject.org).
* Keeping the list of known issues at https://help.torproject.org/ up to date.
* Sending monthly reports on the [tor-reports](https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-reports) mailing list.
* Make the life of support assistants as good as it can be.
* Be the contact point for other parts of the project regarding help desk matters.
* Lead discussions about non-technical aspects of help requests to conclusions.
* Maintain the [`support-tools` Git repository](https://gitweb.torproject.org/support-tools).
* Keep an eye on the [calendar](./doc/support/Calendar) for the 'help' queue.
# Howto
## Create accounts for webchat / stats
* Login to the VM "moschatum"
* Navigate to /srv/support.torproject.org/pups
* Run "sudo -u support python manage.py createuser username password"
* Open a Trac ticket for a new account on moschatum's Prosody installation (same username as pups)
* Send credentials for pups / prosody to support assistant
## Manage the private mailing list
Administration of the private mailing list is done through
[Mailman web interface](https://lists.torproject.org/cgi-bin/mailman/admin/support-team-private).
## Create the monthly report
To create the monthly report chart, one should use the script
`rude.torproject.org:/srv/rtstuff/support-tools/monthly-report/monthly_stats.py`.
Also, each month data need to be added for the quarterly reports for the [business graph](https://gitweb.torproject.org/support-tools.git/tree/HEAD:/queue-graphs/data) and for the [time graph](https://gitweb.torproject.org/support-tools.git/tree/HEAD:/response-time/dataresponse).
Data for the business graph is generated by `monthly_stats`. Data for the response time graph is generated by running `rude.torproject.org:/srv/rtstuff/support-tools/response-time/response_time.py`.
## Read/only access to the RT database
Member of the `rtfolks` group can have read-only access to the RT database. The password
can be found in `/srv/rtstuff/db-info`.
To connect to the database, one can use:
```
psql "host=drobovi.torproject.org sslmode=require user=rtreader dbname=rt"
```
### Number of tickets per week
```
SELECT COUNT(tickets.id),
CONCAT_WS(' ', DATE_PART('year', tickets.created),
TO_CHAR(date_part('week', tickets.created), '99')) AS d
FROM tickets
JOIN queues ON (tickets.queue = queues.id)
WHERE queues.name LIKE 'help%'
GROUP BY d
ORDER BY d;
```
## Extract the most frequently used articles
**Replace the dates.**
```
SELECT COUNT(tickets.id) as usage, articles.name as article
FROM queues, tickets, links, articles
WHERE queues.name = 'help'
AND tickets.queue = queues.id
AND tickets.lastupdated >= '2014-02-01'
AND tickets.created < '2014-03-01'
AND links.type = 'RefersTo'
AND links.base = CONCAT('fsck.com-rt://torproject.org/ticket/', tickets.id)
AND articles.id = TO_NUMBER(SUBSTRING(links.target from '[0-9]+$'), '9999999')
GROUP BY articles.id
ORDER BY usage DESC;
```
### Graphs of activity for the past month
Using Gnuplot:
```
set terminal pngcairo enhanced size 600,400
set style fill solid 1.0 border
set border linewidth 1.0
set bmargin at screen 0.28
set tmargin at screen 0.9
set key at screen 0.9,screen 0.95
set xtics rotate
set yrange [0:]
set output "month.png"
plot "< \
echo \"SELECT COUNT(tickets.id), \
TO_CHAR(tickets.created, 'YYYY-MM-DD') AS d \
FROM tickets \
JOIN queues ON (tickets.queue = queues.id) \
WHERE queues.name LIKE 'help%' \
AND tickets.created >= TO_DATE(TO_CHAR(NOW() - INTERVAL '1 MONTH', 'YYYY-MM-01'), 'YYYY-MM-DD') \
AND tickets.created < TO_DATE(TO_CHAR(NOW(), 'YYYY-MM-01'), 'YYYY-MM-DD') \
GROUP BY d \
ORDER BY d;\" | \
ssh rude.torproject.org psql \\\"host=drobovi.torproject.org sslmode=require user=rtreader dbname=rt\\\" | \
sed 's/|//' \
" using 1:xtic(2) with boxes title "new tickets"
```
### Get the most recent version of each RT articles
```
SELECT classes.name AS class,
articles.name AS title,
CASE WHEN objectcustomfieldvalues.content != '' THEN objectcustomfieldvalues.content
ELSE objectcustomfieldvalues.largecontent
END AS content,
objectcustomfieldvalues.lastupdated,
articles.id
FROM classes, articles, objectcustomfieldvalues
WHERE articles.class = classes.id
AND objectcustomfieldvalues.objecttype = 'RT::Article'
AND objectcustomfieldvalues.objectid = articles.id
AND objectcustomfieldvalues.id = (
SELECT objectcustomfieldvalues.id
FROM objectcustomfieldvalues
WHERE objectcustomfieldvalues.objectid = articles.id
AND objectcustomfieldvalues.disabled = 0
ORDER BY objectcustomfieldvalues.lastupdated DESC
LIMIT 1)
ORDER BY classes.id, articles.id;
```
# Assets
The support help desk coordinator needs the following assets to perform their duties:
* Administration password for the support-team-private mailing list.
* Being owner in the support-team-private mailing list configuration.
* Commit access to [help wiki Git repository](https://gitweb.torproject.org/project/help/wiki.git).
* Shell access to [rude.torproject.org](https://db.torproject.org/machines.cgi?host=rude).
* LDAP account member of the `rtfolks` group.
* LDAP account member of the `support` group.
* `root` password for Request Tracker.
* Being owner of the “Tor Support” component in Trac.