[rt-users] Mandatory Time Worked Field on Ticket Updates

[Michael Coakley]

Christian,

While I hope that Ruslan's solution works for you - and it may be one that I implement myself. I've approached this problem differently in the past. Generally, I've viewed this issue as a management issue. As such I've developed external reports from RT that tell me how much time individuals have worked on specific queues. The SQL query I use to build these reports gives me a detailed report that I then use Excel PivotTables to provide the summaries I need.

Here is the SQL:

SELECT t.id, t.Subject, tr.TimeTaken, tr.Created, u.Realname
FROM (Tickets AS t INNER JOIN Transactions AS tr ON t.id = tr.ObjectId) INNER JOIN
  Users AS u ON tr.Creator = u.id
WHERE t.EffectiveId = t.id AND
  t.Status = 'resolved' AND
  t.Resolved >= '2010-03-01%' AND
  (t.Status NOT LIKE '%Event%' OR t.Status NOT LIKE 'Log File%') AND
  tr.ObjectType = 'RT::Ticket' AND
  (tr.Type = 'Comment' OR tr.Type = 'Correspond') AND
  tr.TimeTaken > 0
ORDER BY u.RealName, t.id, tr.Created;

While some of this SQL is specific to my situation (i.e. the entire "  (t.Status NOT LIKE '%Event%' OR t.Status NOT LIKE 'Log File%') AND" line can be removed), the command provides all the information you will need to provide very detailed reports on Time Worked across Tickets. (NOTE in the SQL I also ONLY look for transactions with Time (i.e. tr.TimeTaken > 0) you can easily remove that line or change it to tr.TimeTaken = 0 to include Transactions in which there is no time entered.

Anyway... I use this report to keep my technicians in line by showing them the difference of how much work time they logged compared to actual time worked. Also, this type of analytical data is very useful during reviews, such as if a person is only accounting for 50-60% of their time, then they aren't following company policy very well.

I'm sure you get the idea... and if you are looking for simple enforcement Ruslan's method looks pretty clean - as always!

Thanks,

Mike

On Mar 15, 2011, at 8:46 AM, Pena, Christian wrote:

> This is something that has probably been asked on the lists many time so bear with me.
>  
> I want to make the “Worked” field mandatory on every ticket update. I read references on the need to do Call Backs but not sure what Syntax to use to achieve this. Can anyone point me in the right direction?
>  
> Thanks!
>  
> Christian Pena
> Network Engineer Supervisor
> EarthLink Business 
>   
> E: christian.pena at corp.earthlink.com
> O: 786-363-0460 | F: 786-363-0206
> STS Telecom is now EarthLink Business
>  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110317/f0860c69/attachment.htm>