I use a cron driven bash script to run this query each night
to monitor the hours for which we’re incurring obligations to
our contractors at YMD Partners LLC.
SELECT realname AS name,
round(sum(timetaken)/60.0,2) as hours,
q.name AS queue,
tix.id AS ticket,
tix.status,
tix.subject
FROM transactions tr
LEFT JOIN tickets tix
ON tr.objectid = tix.id
LEFT JOIN users u
ON u.id = tr.creator
LEFT JOIN queues q
ON tix.queue = q.id
WHERE tr.objecttype = ‘RT::Ticket’
AND tr.created > ‘2010-11-01 00:00:00’
AND tr.created < ‘2010-12-01 00:00:00’
GROUP BY u.id, u.realname, q.name,
tix.queue, tix.id, tix.status,
tix.subject
HAVING sum(timetaken) > 0
ORDER BY u.realname, tix.queue, tix.id ;
This query is run monthly to generate client
specific reports.
SELECT realname AS name,
round(sum(timetaken)/60.0,2) as hours,
q.name AS queue,
tix.id AS ticket,
tix.status,
tix.subject
FROM transactions tr
LEFT JOIN tickets tix
ON tr.objectid = tix.id
LEFT JOIN users u
ON u.id = tr.creator
LEFT JOIN queues q
ON tix.queue = q.id
WHERE tr.objecttype = ‘RT::Ticket’
AND tix.queue IN(14,15,21,22)
AND tr.created > ‘2010-11-01 00:00:00’
AND tr.created < ‘2010-12-01 00:00:00’
GROUP BY u.id, u.realname, q.name,
tix.queue, tix.id, tix.status,
tix.subject
HAVING sum(timetaken) > 0
ORDER BY q.name, u.realname, tix.queue, tix.id ;
These reports get emailed to me each night.On Mon, 06 Dec 2010 15:09:16 -0800 Gary Greene ggreene@minervanetworks.com wrote:
On 6/12/10 1:49 PM, “RAT” robert3t@netzero.net wrote:
We are nearly finished with a new RT deployment but the request was made to be
able to print out reports for each user for each day showing hours worked and
where there are gaps (I know sigh). I didn’t see any reports or extensions
that exactly fit the bill. Does anyone have any ideas?
Robert Threet
http://yesistilluseperl.blogspot.com/
My best guess would be to use a script that either uses the REST API to get
the info from the DB, or reads directly from the DB to get the ticket work
time and then output a pretty HTML report per user, which is linked off a
roster, or some-such.
–
Gary L. Greene, Jr.
IT Operations
Minerva Networks, Inc.
Cell: (650) 704-6633
Office: (408) 240-1239
Hugh Esco
skype: hresco3_ ; 678-921-8186
http://www.CampaignFoundations.com/
Providing Application Hosting,
Telephony and IT Development Services
to Green Candidates, Green Parties and
the non profits working for a just and sustainable future.
if( $insurance->rationing() ) { $people->die(); }