<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<style>
<!--
@font-face
{font-family:Wingdings}
@font-face
{font-family:Wingdings}
@font-face
{font-family:Calibri}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif"}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif"}
span.EmailStyle17
{font-family:"Calibri","sans-serif";
color:windowtext}
.MsoChpDefault
{font-family:"Calibri","sans-serif"}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
ol
{margin-bottom:0in}
ul
{margin-bottom:0in}
-->
</style>
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">All,</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">The issues are:</p>
<p class="MsoNormal"> </p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="">1)<span style="font:7.0pt "Times New Roman"">
</span></span>How do I search transaction history using the query builder?</p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="">2)<span style="font:7.0pt "Times New Roman"">
</span></span>How do I convert an SQL statement like the one lower down into something I can use in a custom report?</p>
<p class="MsoNormal" style="margin-left:.25in"> </p>
<p class="MsoNormal">People here in my company have asked me if they can get a report on the number of tickets whose status was changed from stalled or resolved into open during a specific time frame because of an external e-mail from a customer. I think I
have the SQL statement working, but I cannot figure out how to do the same thing with RT’s query builder. I have been all over google and the docs and I am not finding anything useful to help point me in the right direction. It looks like this issue comes
up every so often on the mailing list, but no one has really been answered well, I hope I fare better.
<span style="font-family:Wingdings">J</span></p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Any suggestions or help would be greatly appreciated.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">SELECT Tickets.id, Tickets.Created, Tickets.Subject, Tickets.Status, Queues.Name</p>
<p class="MsoNormal">FROM Transactions, Tickets, Queues</p>
<p class="MsoNormal">WHERE </p>
<p class="MsoNormal">Tickets.Queue=Queues.id</p>
<p class="MsoNormal">AND Queues.Name="Test Queue 1"</p>
<p class="MsoNormal">AND Tickets.Status='open'</p>
<p class="MsoNormal">AND Transactions.ObjectId=Tickets.id</p>
<p class="MsoNormal">AND Transactions.OldValue REGEXP "stalled|resolved"</p>
<p class="MsoNormal">AND Transactions.NewValue='open' </p>
<p class="MsoNormal">AND Transactions.Data = 'Ticket auto-opened on incoming correspondence'
</p>
<p class="MsoNormal">AND Transactions.Created >= @START </p>
<p class="MsoNormal">AND Transactions.Created <= @FINISH</p>
</div>
<br>
<font size="3" face="Garamond"><b>Brian Schrock</b></font><br>
<font size="3" face="Calibri">Linux Administrator<br>
Network Operations<br>
The Garden City Group, Inc. <br>
5151 Blazer Parkway Suite A <br>
Dublin, ohio 43017<br>
Telephone: 614-289-5457 <br>
Mobile: 614-745-5491<br>
Email: Brian.Schrock@gardencitygroup.com </font><br>
<hr>
<font face="Arial" color="Gray" size="1"><br>
This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly
prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.<br>
</font>
</body>
</html>