Updating Ticket Status via PostgreSQL

I’m sure this has been answered by someone somewhere, but my Google-fu
is not helping me.

I have a few hundred tickets in a production system that I want to
change the status with some criteria a bit more sophisticated than RT
(4.2.12) is allowing me to do.

The SQL I want to issue is:

 UPDATE tickets
    SET status = 'noresponse'
  WHERE status = 'open' AND told < '2015-11-01'
    AND told > lastupdated;

The last condition is the one I can’t enter in RT.

Effectively, update the status of all tickets that are still open to
"noresponse", where the last update was us sending the requester an
email.

Is this safe? Is there something else I should be updating as well?

Thank you in advance.

  • Aaron

Hi Aaron,

I believe you can do this all in RT.

I’m sure this has been answered by someone somewhere, but my Google-fu is
not helping me.

I have a few hundred tickets in a production system that I want to change
the status with some criteria a bit more sophisticated than RT (4.2.12) is
allowing me to do.

The SQL I want to issue is:

UPDATE tickets
   SET status = 'noresponse'
 WHERE status = 'open' AND told < '2015-11-01'
   AND told > lastupdated;

The last condition is the one I can’t enter in RT.

In your RT web interface, go to: Search → Tickets → New Search.

The key here is knowing that “Last Contacted” is really “Told”, or so I believe.

From a grep in the source directory:

share/html/Elements/SelectDateType:<&|/l&>Last
Contacted</&>

Once you get your search results, you can bulk update the status change.

Effectively, update the status of all tickets that are still open to
“noresponse”, where the last update was us sending the requester an email.

Is this safe?

Nope. Don’t perform update/insert/delete queries directly on the
database. There are things like effective ticket id that your query
doesn’t handle. Scrips and things wouldn’t fire either.

Cheers,

-m

Hi Aaron,

I believe you can do this all in RT.

I’m sure this has been answered by someone somewhere, but my Google-fu
is
not helping me.

I have a few hundred tickets in a production system that I want to
change
the status with some criteria a bit more sophisticated than RT
(4.2.12) is
allowing me to do.

The SQL I want to issue is:

UPDATE tickets
   SET status = 'noresponse'
 WHERE status = 'open' AND told < '2015-11-01'
   AND told > lastupdated;

The last condition is the one I can’t enter in RT.

In your RT web interface, go to: Search → Tickets → New Search.

The key here is knowing that “Last Contacted” is really “Told”, or so I
believe.

From a grep in the source directory:

share/html/Elements/SelectDateType:<&|/l&>Last
Contacted</&>

Once you get your search results, you can bulk update the status
change.

What I’m getting at is that RT won’t let me compare told to last updated
through that interface.

I can check that told was before a certain date and that last updated
was before a certain date, but I can’t check for told being older or
newer than last updated.

Effectively, update the status of all tickets that are still open to
“noresponse”, where the last update was us sending the requester an
email.

Is this safe?

Nope. Don’t perform update/insert/delete queries directly on the
database. There are things like effective ticket id that your query
doesn’t handle. Scrips and things wouldn’t fire either.

Cheers,

-m

Is there a way to feed ticket IDs or something to rt-crontool?

Hi Aaron,

I believe you can do this all in RT.

I’m sure this has been answered by someone somewhere, but my Google-fu is
not helping me.

I have a few hundred tickets in a production system that I want to change
the status with some criteria a bit more sophisticated than RT (4.2.12)
is
allowing me to do.

The SQL I want to issue is:

UPDATE tickets
   SET status = 'noresponse'
 WHERE status = 'open' AND told < '2015-11-01'
   AND told > lastupdated;

The last condition is the one I can’t enter in RT.

In your RT web interface, go to: Search → Tickets → New Search.

The key here is knowing that “Last Contacted” is really “Told”, or so I
believe.

From a grep in the source directory:

share/html/Elements/SelectDateType:<&|/l&>Last
Contacted</&>

Once you get your search results, you can bulk update the status change.

What I’m getting at is that RT won’t let me compare told to last updated
through that interface.

I can check that told was before a certain date and that last updated was
before a certain date, but I can’t check for told being older or newer than
last updated.

Sure. Sorry for not catching that in your query.

Effectively, update the status of all tickets that are still open to
“noresponse”, where the last update was us sending the requester an
email.

Is this safe?

Nope. Don’t perform update/insert/delete queries directly on the
database. There are things like effective ticket id that your query
doesn’t handle. Scrips and things wouldn’t fire either.

Cheers,

-m

Is there a way to feed ticket IDs or something to rt-crontool?

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

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