<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal>Hi,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I’m in the process of writing a small script to pull
data on ticket transactions in order to find the time worked for each RT user,
per queue, per ticket, each week. Once I have all this data I format it
into a CSV file and send out an email with the file attached. Right now
the script is in PHP, though I could move to Perl if need be, and the script is
running locally on the machine which hosts RT and its database.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Currently, I’ve got a fairly large SQL statement which
filters all of the ticket transactions for the given date range (amongst other
things) then pulls ticket, queue, user and custom field data from each of the
tables using LEFT JOIN clauses. The query works great and I’ve been
able to pull and sort/group all the data how I need it for the CSV.
However, I’ve read in the mailing list while researching that the DB
schema is set to change significantly as of 4.0; obviously this was always
going to be an issue with any custom code, but I also saw the REST protocol and
I’m now wondering if there’s a better method which is also
relatively simple to implement.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I had figured that having to learn all of RT’s Perl
functions would take a fair bit of investigation and it would be much faster
just to query the DB directly, and it has been fast as I’ve only spent 2~
hours on it so far. I’ve looked at the REST API and haven’t
found any way to use it for searching transactions, only tickets.
I’ve also seen an RT stats add-on, but again this is custom code and I
want to keep it all as stock as I can, as I’m the only coder employed
here.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I need to be able to first and foremost pull the time worked
for each user during the week, not the total time worked for each ticket they
own/request, the only way I can see doing this is querying the transactions
table in some fashion, because I need to know when a user makes a comment on
any ticket with worked time, or sets the worked time on a ticket.
I’ll keep working with the database directly for now, but if anyone has
any suggestions for a solution that I can “set and forget”,
I’d love to hear it.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hopefully my needs are clear, but if not, I’m happy to
provide the query, an example of the CSV report, or any other further
information.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Thanks in advance.<o:p></o:p></p>
</div>
</body>
</html>