1387229 - Add API endpoint for querying notes

Status: RESOLVED FIXED

(Tree Management :: Treeherder: API, enhancement)

Description

[Chris Cooper [:coop] (he/him)]

Not sure if this is a dupe of bug 1118212, but I have a real use case for this now.

I have a set of 15 contractors starting soon who will be assisting with sheriffing duties. Since one of the primary sheriff tasks is starring failures, I would like to be able to report on notes created per sheriff per unit time so that I can help evaluate contractor activity.

AFAICT the current note endpoint will only return detail for a single job, e.g. https://treeherder.mozilla.org/api/project/mozilla-inbound/note/?job_id=120750370

How hard would it be to whip this up?

Comment 1

[Ed Morley [:emorley]]

This is doable now using redash, if that helps? (Bug 1335172 open to mention this on Treeherder's RTD page)
https://sql.telemetry.mozilla.org/

Comment 2

[Chris Cooper [:coop] (he/him)]

(In reply to Ed Morley [:emorley] from comment #1)
> This is doable now using redash, if that helps? (Bug 1335172 open to mention
> this on Treeherder's RTD page)
> https://sql.telemetry.mozilla.org/

Thanks, Ed. I try and whip up a dashboard tomorrow and close that out if I'm successful.

Comment 3

[Ed Morley [:emorley]]

The tables you'll need are (some classifications are bug numbers, others are comments aka notes):
* treeherder.bug_job_map
* treeherder.job_note
* treeherder.auth_user (the `id` field on this table foreign-keys to `user_id` on the other two)

Comment 4

[Chris Cooper [:coop] (he/him)]

(In reply to Ed Morley [:emorley] from comment #3)
> The tables you'll need are (some classifications are bug numbers, others are
> comments aka notes):
> * treeherder.bug_job_map
> * treeherder.job_note
> * treeherder.auth_user (the `id` field on this table foreign-keys to
> `user_id` on the other two)

I'm unclear on the relationship between job_note and bug_job_map. Is a note created for every starring interaction, but only some are mapped to bugs? 

I have the following query to grab notes for August: https://sql.telemetry.mozilla.org/queries/11935/source#table

The distribution looks good, but I'm not sure if it's accurate or whether I'm over-counting or under-counting just based on job_notes.

Comment 5

[Ed Morley [:emorley]]

(In reply to Chris Cooper [:coop] from comment #4)
> I'm unclear on the relationship between job_note and bug_job_map. Is a note
> created for every starring interaction, but only some are mapped to bugs? 

They are completely independent - there are two fields in the UI: one for adding a bug number and one for adding a category+free-form string. Users can add either one or both at the same time.

Comment 6

[Chris Cooper [:coop] (he/him)]

(In reply to Ed Morley [:emorley] from comment #5)
> (In reply to Chris Cooper [:coop] from comment #4)
> > I'm unclear on the relationship between job_note and bug_job_map. Is a note
> > created for every starring interaction, but only some are mapped to bugs? 
> 
> They are completely independent - there are two fields in the UI: one for
> adding a bug number and one for adding a category+free-form string. Users
> can add either one or both at the same time.

So I guess I should be grouping by job id then too.

I'm also interested in how often single jobs need to be re-starred, so looking for jobs with multiple notes or bugs could be a separate query.

Comment 7

[Chris Cooper [:coop] (he/him)]

I'm using this as a basic query:

https://sql.telemetry.mozilla.org/queries/11986/source#28562

I can adjust the timescales as needed. Thanks for the pointers!