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

[Dominic Lepiane]

> -----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