<html><head></head><body bgcolor="#FFFFFF"><div>This is not available in the query builder. I asked the same question a while back and basically the query builder works on the tickets taw only and cannot do cross queries. I ended up writing a shell script for my needs which basically was "any update performed by any member of a particular group yesterday". The next morning it is sent to the manager (or the group) depending on the system and manager. I can share if you're interested. I "may" have already shared an example on the list here if you search back a month or two. <br><br><div>Sent from my mobile device. </div></div><div><br>On Jun 12, 2012, at 5:22 PM, Brian Schrock <<a href="mailto:Brian.Schrock@gardencitygroup.com">Brian.Schrock@gardencitygroup.com</a>> wrote:<br><br></div><div></div><blockquote type="cite"><div>
<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>
<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: <a href="mailto:Brian.Schrock@gardencitygroup.com">Brian.Schrock@gardencitygroup.com</a> </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>
</div></blockquote></body></html>