[rt-users] RT tool & RecordTransaction => 0

Bart bart at pleh.info
Thu Dec 8 02:38:39 EST 2011


***Small update***

Since I want a simple solution I'm now looking into doing this inside SQL
instead of trying to make a clever script.

Few SQL queries that should give you an idea to where I'm heading.


Get all tickets along with their status, though I'll probably make a
separate query for each status (to keep things simple)

select id,Status from Tickets where Status != "deleted" AND Status !=
"resolved" AND Status != "spam" AND Status != "rejected";

Show the CustomField value of ticket 2222 (specific CF, selected by ID),
disabled 0 means it's the active value:

select * from ObjectCustomFieldValues where ObjectId = "2222" AND Disabled
= "0" AND CustomField = "12";

Update the CustomField value for ticket 2222 and increase the value by 5:

update ObjectCustomFieldValues set Content = Content + 5 where CustomField
= "12" AND Disabled = "0" AND ObjectId = "2222";

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).

The CustomField values will be set to 0 + during ticket creation I'll make
sure that the transaction isn't recorded.

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.

One thing that I want to do to make things a little "nicer" is to also
update the "LastUpdated" field for that entry.

Overall I think this will be the better solution for us, will post the
results once I'm done.


-- Bart


Op 8 december 2011 07:34 schreef Bart <bart at pleh.info> het volgende:

> Hmm, I might need to rethink my script then...
>
> Have this script in cron:
>
> #!/bin/bash
>
> datum=`date "+%d%m%y"`
> tijd=`date "+%H%M"`
>
> 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 ]
> then
> 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
>  case `/opt/rt4/bin/rt ls -t ticket "id=$i" -f status | grep ^[0-9] | cut
> -f 2` in
>  new) /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 ))&
>  ;;
> open) /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 ))&
>  ;;
> stalled) /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 ))&
>  ;;
> Bij_Leverancier) /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 ))&
>  ;;
> Bij_Aanvrager) /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 ))&
>  ;;
> Intern) /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 ))&
>  ;;
> *) ;;
>  esac
> done
> fi
>
> 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)
>
> 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). . .
>
> 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)....
>
> Any suggestions on how to achieve the above without writing stuff in the
> ticket history?
>
>
> -- Bart
>
>
> Op 7 december 2011 22:44 schreef Ruslan Zakirov <ruz at bestpractical.com>het volgende:
>
> On Wed, Dec 7, 2011 at 7:23 PM, Bart <bart at pleh.info> wrote:
>> > Hi,
>> >
>> > Is there an equivalent way of making the transactions silent via the RT
>> > tool?
>>
>> No.
>>
>> >
>> > Within a normal scrip using RecordTransaction => 0, is there a similar
>> way
>> > for making an edit via the RT tool silent?
>> >
>> > -- Bart
>> >
>> > --------
>> > RT Training Sessions (http://bestpractical.com/services/training.html)
>> > * Boston — March 5 & 6, 2012
>>
>>
>>
>> --
>> Best regards, Ruslan.
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20111208/f552c194/attachment.htm>


More information about the rt-users mailing list