[rt-users] [phish?] Re: Updating Ticket Status via PostgreSQL

[Aaron W. Swenson]

On 2015-12-02 09:29, Matt Zagrabelny wrote:
> Perhaps the "rt" tool.
> 
> I just looked at some (paper!) documentation. Here is what I see:
> 
> rt edit ticket/[TICKET ID] set status=noresponse
> 
> Write your ticket ids to a file, one per line. psql uses "less" to
> page the results - you can write the results to a file using '-o'.
> 
> Wrap the above command in a bash for loop.
> 
> for id in `cat /path/to/file/with/ids`; do
>     /opt/rt4/bin/rt edit ticket/$id set status=noresponse
> done
> 
> Give that a whirl?
> 
> -m

This is it!

So, for those following along at home:

     $ psql -U rtuser -d rtdatabase

     rtuser=> \t
     rtuser=> \o ticket_ids.txt
     rtuser=> SELECT id FROM tickets WHERE
                id = effectiveid -- avoid awkwardness, maybe.
                AND status = 'open'
                AND told < '2015-11-01'
                -- truncate the timestamp to the minute because the
                -- seconds might not be exact, and it's unlikely that a
                -- customer would have sent something when we did.
                AND date_trunc('minute', told) =
                    date_trunc('minute', lastupdate);
     rtuser=> \o
     rtuser=> \t
     rtuser=> \quit

     $ for t in $(<ticket_ids.txt); do
       RTUSER="your rt login" \
         RTPASSWD="your rt password" \
         RTSERVER="http://localhost/rt" \
         /path/to/rt/base/bin/rt edit ticket/$t set status=noresponse || 
break;
       done

For each ticket ID, the loop should output:

     # Ticket $t updated.

This updated a couple tickets (or a bit more) per second. Not blazing
fast, but significantly faster than a human. Further, the ticket history
through the browser interface properly shows that I updated the ticket.

Thanks!

- Aaron