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

[Matt Zagrabelny]

On Wed, Dec 2, 2015 at 8:05 AM, Aaron W. Swenson
<aaron at grandmasfridge.org> wrote:
> On 2015-12-01 15:58, Matt Zagrabelny wrote:
>>
>> Hi Aaron,
>>
>> I believe you can do this all in RT.
>>
>> On Tue, Dec 1, 2015 at 2:33 PM, Aaron W. Swenson
>> <aaron at grandmasfridge.org> wrote:
>>>
>>> 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:<option value="Told"><&|/l&>Last
>> Contacted</&></option>
>>
>> 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