> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf > Of Darren Nickerson > Sent: Monday, May 12, 2008 10:53 AM > To: [email protected] > 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 '[EMAIL PROTECTED]' OR `Users2`.`EmailAddress` LIKE '[EMAIL PROTECTED]') 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: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
