***Small update***<div><br></div><div>Since I want a simple solution I'm now looking into doing this inside SQL instead of trying to make a clever script.</div><div><br></div><div>Few SQL queries that should give you an idea to where I'm heading.</div>
<div><br></div><div><br></div><div>Get all tickets along with their status, though I'll probably make a separate query for each status (to keep things simple)</div><div><br></div><div><font class="Apple-style-span" face="'courier new', monospace">select id,Status from Tickets where Status != "deleted" AND Status != "resolved" AND Status != "spam" AND Status != "rejected";</font></div>
<div><br></div><div>Show the CustomField value of ticket 2222 (specific CF, selected by ID), disabled 0 means it's the active value:</div><div><br></div><div><font class="Apple-style-span" face="'courier new', monospace">select * from ObjectCustomFieldValues where ObjectId = "2222" AND Disabled = "0" AND CustomField = "12";</font></div>
<div><br></div><div>Update the CustomField value for ticket 2222 and increase the value by 5:</div><div><br></div><div><font class="Apple-style-span" face="'courier new', monospace">update ObjectCustomFieldValues set Content = Content + 5 where CustomField = "12" AND Disabled = "0" AND ObjectId = "2222";</font></div>
<div><br></div><div>The only problem I'm now looking at is that if a CF never had a value (empty) then it won't have an entry in the DB. So in addition to the SQL queries I'm going to make sure that these specific CF's always have a value (set during ticket creation + set once manually for all tickets).</div>
<div><br></div><div>The CustomField values will be set to 0 + during ticket creation I'll make sure that the transaction isn't recorded.</div><div><br></div><div>When you update a CF that's already been recorded in the ticket history you'll update the value shown in the ticket history for that entry.</div>
<div><br></div><div>One thing that I want to do to make things a little "nicer" is to also update the "LastUpdated" field for that entry.</div><div><br></div><div>Overall I think this will be the better solution for us, will post the results once I'm done.</div>
<div><br></div><div><br clear="all">-- Bart<br>
<br><br><div class="gmail_quote">Op 8 december 2011 07:34 schreef Bart <span dir="ltr"><<a href="mailto:bart@pleh.info">bart@pleh.info</a>></span> het volgende:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>Hmm, I might need to rethink my script then...</div><div><br></div><div>Have this script in cron:</div><div><br></div><div><div><font face="'courier new', monospace">#!/bin/bash</font></div>
<div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">datum=`date "+%d%m%y"`</font></div><div><font face="'courier new', monospace">tijd=`date "+%H%M"`</font></div>
<div><font face="'courier new', monospace"><br></font></div><div><font face="'courier new', monospace">if [ $tijd -ge 0830 -a $datum -ne 261211 -a $datum -ne 090412 -a $datum -ne 300412 -a $datum -ne 170512 -a $datum -ne 180512 -a $datum -ne 280512 -a $datum -ne 251212 -a $datum -ne 261212 ]</font></div>
<div><font face="'courier new', monospace">then</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>for i in `/opt/rt4/bin/rt ls -t ticket "Status!='resolved' and Status!='rejected' and Status!='spam' and Status!='deleted'" -f status | grep ^[0-9] | cut -f 1`; do</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>case `/opt/rt4/bin/rt ls -t ticket "id=$i" -f status | grep ^[0-9] | cut -f 2` in</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>new)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_new'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_new' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>open)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_open'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_open' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>stalled)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_stalled'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_stalled' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>Bij_Leverancier)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_Bij_Leverancier'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_Bij_Leverancier' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>Bij_Aanvrager)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_Bij_Aanvrager'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_Bij_Aanvrager' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>Intern)<span style="white-space:pre-wrap"> </span>/opt/rt4/bin/rt edit ticket/$i set CF-'aantal_minuten_Intern'=$(( `/opt/rt4/bin/rt ls -t ticket "id=$i" -f CF-'aantal_minuten_Intern' | grep ^[0-9] | cut -f 2` + 5 ))&</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>;;</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>*)<span style="white-space:pre-wrap"> </span>;;</font></div>
<div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>esac</font></div><div><font face="'courier new', monospace"><span style="white-space:pre-wrap"> </span>done</font></div>
<div><font face="'courier new', monospace">fi</font></div></div><div><br></div><div>It runs every 5 minutes during business hours, rough translation would be that it runs 114 times a day. During each run it increases the value of a custom field by 5, this will give us the amount of time in minutes a ticket stood at a certain active status. (in the above you'll see a few default statuses along with a few custom ones)</div>
<div><br></div><div>I kinda overlooked that it writes an entry in the ticket history where it shows the change of the CF value... 114 times a day for each ticket, where an average ticket might be open a few days (changes or problems even longer). . .</div>
<div><br></div><div>Users however won't see these changes unless they hit the history tab, but I the ticket view will be slowed down when the history gets bigger and bigger (we use RT::Extension::HistoryFilter, but even with it RT will first load the entire history)....</div>
<div><br></div><div>Any suggestions on how to achieve the above without writing stuff in the ticket history?</div><div><br></div><div><br></div>-- Bart<br>
<br><br><div class="gmail_quote">Op 7 december 2011 22:44 schreef Ruslan Zakirov <span dir="ltr"><<a href="mailto:ruz@bestpractical.com" target="_blank">ruz@bestpractical.com</a>></span> het volgende:<div><div class="h5">
<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>On Wed, Dec 7, 2011 at 7:23 PM, Bart <<a href="mailto:bart@pleh.info" target="_blank">bart@pleh.info</a>> wrote:<br>
> Hi,<br>
><br>
> Is there an equivalent way of making the transactions silent via the RT<br>
> tool?<br>
<br>
</div>No.<br>
<div><br>
><br>
> Within a normal scrip using RecordTransaction => 0, is there a similar way<br>
> for making an edit via the RT tool silent?<br>
><br>
> -- Bart<br>
><br>
</div>> --------<br>
> RT Training Sessions (<a href="http://bestpractical.com/services/training.html" target="_blank">http://bestpractical.com/services/training.html</a>)<br>
> * Boston — March 5 & 6, 2012<br>
<span><font color="#888888"><br>
<br>
<br>
--<br>
Best regards, Ruslan.<br>
</font></span></blockquote></div></div></div><br>
</blockquote></div><br></div>