[rt-users] How to flag neglected tickets for support engineer response??

[Kenneth Crocker]

Dominic, Darren, & Graham,


	Hoe many tickets do you get a week? How many Queues do you have? What 
kind of support infrastructure have you set up? Depending on your 
answers, you might want to consider setting up a single queue for 
receiving ALL requests and then run some RT queries to select/sort those 
requests based on whatever criteria is most important and then move thos 
tickets to the support queue where the work can be done/documented by a 
member of the appropriate support group. there's a lot of work that goes 
into setting up the groups/privileges that will support a descent 
support infrastructure. I'll be glad to help as we went thru the same 
problems. We now have over 75 support queues that handle hundreds of 
tickets a month with the ability to review & prioritize/approve tickets 
for work as well as a QA Testing WorkFlow process as well. Let me know.


Kenn
LBNL

On 5/12/2008 11:24 AM, Dominic Lepiane wrote:
>> -----Original Message-----
>> From: rt-users-bounces at lists.bestpractical.com
>> [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf
>> Of Darren Nickerson
>> Sent: Monday, May 12, 2008 10:53 AM
>> To: rt-users at lists.bestpractical.com
>> Subject: [rt-users] How to flag neglected tickets for support
>> engineer response??
>>
>> Folks,
>>
>> We've been using RT for some time now, and have managed to
>> get by with pretty much a stock configuration. Our support
>> reps are starting to get lost in a sea of tickets though,
>> where only the most recently 'alive' tickets get attended to,
>> and lots of important ones drop off their radar. We're
>> looking to take advantage of some of the fancy hooks, bells
>> and whistles that are possible due to RT's open architecture.
>>
>> Our objective is to flag tickets that require a support
>> engineer response somehow. Our criteria for determining which
>> tickets require a response might be, for example, tickets
>> where the last comment or correspondence was not from someone
>> in the RT group "Support". In theory, that would mean that
>> the customer (or other interested parties in the cc: list)
>> were the last ones to update the ticket, and the ball is now
>> in our court.
>>
>> Our preliminary research suggests that we might be able to
>> invoke RT::Search::FromSQL from rt-crontool and use that to
>> locate tickets matching the above condition, and "do
>> something" to those tickets.  We're not sure what "do
>> something" means, but it might mean setting a custom field
>> (Ruslan says custom statuses are bad ;-)) or something
>> similar. Where we really hit a wall is how to prioritize
>> these items in the "RT at a glance" page as needing a
>> response from us. Is it possible to colorize the tickets red, perhaps?
>>
>> Essentially what we need is a 'Customer Pending' and 'Support
>> Pending' designation that will allow us to flag "Support
>> Pending" tickets for support engineer attention. This seems
>> like a common problem ... does anyone have any
>> recipes/solutions/tips that might help? All advice welcomed!
>>
>> -Darren
>>
> 
> Hi Darren,
> 
> We have the same problem here.  Large amounts of tickets are pending and we need to be able to identify which ones are customer-last-commented vs. staff-last-commented.  Currently, we do this outside RT and have MySQL queries run from a PHP web page that query the RT database for this info.  And it is a big ugly query (see below).
> 
> This was all done before I started here (I didn't do it!) so if there's some sane way of handling this in RT, I'm all ears.  Barring that, maybe this query can help you.
> 
> "SELECT
> `Tickets`.`id` AS '".$db_col1."',
> `Tickets`.`Subject` AS '".$db_col2."',
> DATE_SUB(`Tickets`.`Told` ,  INTERVAL 8  HOUR  ) AS '".$db_col3."',
> DATE_SUB(`Tickets`.`LastUpdated` ,  INTERVAL 8  HOUR  ) AS '".$db_col7."',
> $hours_waiting AS '".$db_col8."',
> `Users1`.`RealName` AS '".$db_col4."',
> SUBSTRING( MAX( CONCAT( LPAD(Transactions.id,8,'0'), Users2.EmailAddress   ) ) , 9 ) AS '".$db_col10."',
> $pgr_last AS '".$db_col9."',
> IF(UNIX_TIMESTAMP(`Tickets`.`Told`)<UNIX_TIMESTAMP(`Tickets`.`LastUpdated`),1,0) AS '".$db_col11."',
> IF(!$pgr_last,
>    IF($hours_waiting > 20,1.0,IF($hours_waiting > 8,2.0,IF(ISNULL(`Tickets`.`Told`),2.0,3.0))),
>    IF($hours_waiting > 200,1.5,IF($hours_waiting > 48,2.0,IF($hours_waiting > 24, 3.0, IF(ISNULL(`Tickets`.`Told`),2.0,4.0))))
>    ) AS Priority
> 
> FROM  `Tickets` , `Queues`, `Users` AS `Users1`, `Users` AS `Users2`, Transactions
> WHERE
> (
> `Tickets`.`Status`  =  'new' OR
> `Tickets`.`Status`  =  'open'
> ) AND
> `Tickets`.`Queue` =`Queues`.`id` AND
> `Queues`.`Name` = 'PGR Support' AND
> `Users1`.`id` = `Tickets`.`Owner` AND
> `Users2`.`id` = `Transactions`.`Creator` AND
> (`Users2`.`EmailAddress` NOT LIKE '%@ptgrey.com%' OR
> `Users2`.`EmailAddress` LIKE '%@ptgrey.com%') AND
> Transactions.ObjectId = Tickets.id AND
> ((Transactions.Type = 'Correspond') OR
>  (Transactions.Type = 'Create' ))
> GROUP BY Tickets.id
> ORDER BY Priority, '$db_col8' DESC";
> 
> 
> 
> Dominic Lepiane
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
>