Add report to Trac site to show "Active tickets by last modified date"

[sid]

When doing ticket triage, I often run across tickets that have been forgotten. But I have to dig to find them. It would be really nice to have a report I could use to show most and least active tickets.

This report (grabbed from #3393) should do the trick, although it might need a little tweaking for whatever DB t.e.o. is using:

SELECT p.value AS __color__,
  id AS ticket, summary, component, version, milestone, t.severity AS severity,
  (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
  (CASE WHEN strftime('%s','now') - changetime > 86400
        THEN round((strftime('%s','now') - changetime)/86400,0) || ' days'
        WHEN strftime('%s','now') - changetime > 3600
        THEN round((strftime('%s','now') - changetime)/3600,0) || ' hours'
        ELSE (strftime('%s','now') - changetime)/60 || ' mins'
  END) AS Modified,
  author as Modified_By,
  description AS _description,
  reporter AS _reporter
FROM ticket t, enum p,
     (SELECT ticket, author, MAX(time) FROM ticket_change WHERE field = 'comment' GROUP BY ticket) tc
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.id = tc.ticket
ORDER BY changetime desc, p.value, milestone, t.severity, time

This will display active tickets by last modified date. Can you add it to t.e.o. reports?

[ilias@…]

This was requested and implemented for the more flexible queries (although a report would be fine, too).

#4174 should have solved this, although I don't know if the production server (t.e.o) was updated with the lates 0.10.3dev release (or whichever is relevant).

btw: this is the main reason for a version 'production' #4256, because this would imply "the code live on the production server".

Would #4174 had a version 'production', then it would be still open, as the code is not yet transfered to the production-version (= t.e.o server).

[Christian Boos]

sid, I created {20}, with the following SQL:

-- ## 20: Active tickets by last modified date ## --

-- This will display active tickets by last modified date.
-- 
-- This can be used to show most and least active tickets.
-- 
-- 

SELECT p.value AS __color__,
  id AS ticket, summary, component, version, t.severity AS severity,
  (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
  (CASE WHEN round(time.now - changetime) > 86400
        THEN round((time.now - changetime)/86400.0) || ' days'
        WHEN time.now - changetime > 3600
        THEN round((time.now - changetime)/3600.0) || ' hours'
        ELSE round((time.now - changetime)/60) || ' mins'
  END) AS LastModified,
  tc.author_cnt as Authors,
  description AS _description,
  reporter AS _reporter,
  milestone AS _milestone
FROM ticket t, enum p, 
  (SELECT extract(epoch from current_timestamp) as now) time,
  (SELECT ticket, count(author) as author_cnt, max(time) FROM ticket_change 
    WHERE field = 'comment' GROUP BY ticket) tc
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.id = tc.ticket
  AND t.milestone = ''
ORDER BY changetime desc, p.value, t.severity, time

• I had to change the changetime conversion to adapt to the PostgreSQL way, plus avoid to use the Modified column name, as the report module expects a float for that
• using author in the tc subselect was not permitted, I either had to add it to the GROUP BY clause (but that generated too many rows) or use an aggregate; ideally I'd liked a concat() aggregate, but that doesn't seem to exist.
• For now, I left out the tickets having a milestone set, as those are assumed to have been already triaged; let me know if you'd like to have another report with the tickets with a milestone

[sid]

That's great, cboos! I especially like the "authcomments" section you added. This will be a big help.

[Christian Boos]

In case someone was wondering why the report {20} above and query:status=!closed&milestone= were differing (305 tickets vs. 395, at the time of this writing), here's why: tickets with no changes were simply not shown!

Updated query:

SELECT p.value AS __color__,
  t.type AS __group__,
  id AS ticket, summary, component, version, t.severity AS severity,
  (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
  (CASE WHEN round(time.now - changetime) > 86400
        THEN round((time.now - changetime)/86400.0) || ' days'
        WHEN time.now - changetime > 3600
        THEN round((time.now - changetime)/3600.0) || ' hours'
        ELSE round((time.now - changetime)/60) || ' mins'
  END) AS LastModified,
  tc.author_cnt || '/' || tc.change_cnt as Auths_Chgs,
  description AS _description,
  reporter AS _reporter,
  milestone AS _milestone
FROM ticket t LEFT JOIN 
   (SELECT ticket, count(distinct author) as author_cnt, 
          count(*) as change_cnt
    FROM ticket_change 
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket), 
   enum p, 
   (SELECT extract(epoch from current_timestamp) as now) time
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.milestone = ''
ORDER BY t.type, changetime desc, p.value, t.severity, time

Now it's way better, but still not exactly the same (394 tickets vs. 397). A slot in the THANKS file is to be awarded for the one finding the reason behind this difference!

[entro]

Maybe because of that?

AND t.milestone = ''

[entro]

Since an update to version 0.10 i'm getting the following error:

Report execution failed: near "from": syntax error

any ideas?

[Emmanuel Blot]

Replying to entro:

any ideas?

Which kind of DB backend are you using (sqlite/postgresql/mysql)?

[sid]

I'm not sure why this was reopened. This ticket addresses a report specifically for t.e.o. site for TracTicketTriage, and that is working great. If you're getting syntax problems, you'll have to debug it for your DB backend. You can get help for that on the MailingList. Thanks.

[Christian Boos]

The timestamps are now stored in the database in microseconds, as big ints (see r9210).

Such reports should be modified accordingly.

For example {23}:

-- ## 23: Active tickets for 0.12, by last modified date ## --

-- '''Those are the tickets for the next Trac release, [milestone:0.12]'''
-- 
-- Notes:
--  - the ''[query:?milestone=0.12&keywords=~needinfo&status=!closed needinfo]'' tickets have been excluded from this report (see also {21}).
--  - the ''Chgs_Auths'' field corresponds to: ''(number of changes) by (number of different change authors)'', and can be used as an indicator of the popularity of the issue
-- 
--   

SELECT p.value AS __color__,
  t.type,
  id AS ticket, summary, component, version, t.severity AS severity,
  owner, status,
  (SELECT (CASE
           WHEN delta > 86400 THEN round(delta/86400.0) || ' days'
           WHEN delta > 3600  THEN round(delta/3600.0) || ' hours'
           ELSE round(delta/60) || ' mins' 
           END)
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS LastModified,
  COALESCE(tc.change_cnt || ' by ' || tc.author_cnt, '-') as Chgs_Auths,
  description AS _description,
  reporter AS _reporter,
  milestone AS _milestone
FROM ticket t LEFT JOIN 
   (SELECT ticket, count(distinct author) as author_cnt, 
          count(*) as change_cnt
    FROM ticket_change 
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket), 
   enum p, 
   (SELECT extract(epoch from current_timestamp) as now) time
WHERE t.status != 'closed' 
  AND (t.keywords IS NULL OR t.keywords NOT LIKE '%needinfo%')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.milestone = '0.12'
ORDER BY changetime desc, p.value, t.severity, time