-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf
Of Darren Nickerson
Sent: Monday, May 12, 2008 10:53 AM
To: rt-users@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
You can quote this wherever you found