Most efficient way to update custom fields without creating too many transactions

Hello,

Currently we are utilizing some internal systems to lookup the Customer ID
and other data related to that customer to add to Incident Reports coming
into RT. Some organizations include a list of IP addresses or URLs related
to a specific type of Incident. Those IP addresses or URLs might belong to
several customers and the processing of the ticket becomes more arduous to
process by my Action modules. While this is not a problem in itself
because it eventually does get processed the processing also adds a number
of of Transactions that is multiples of the required custom fields to be
updated.

For example if there are 1000 IP addresses in a ticket and there are 4
custom fields to be updated it creates 4000 transactions for that one
ticket when looping each update individually.

What I’m wondering is if there’s a better way to make these updates so
multiple values can be added to the ticket with one transaction.

Example:

This:
foreach my $customerID (@customerids) {
$ticket->AddCustomFieldValue( Field => ‘Customer’, Value => $customerID );
}

Vs. This:
$ticket->AddCustomFieldValue( Field => ‘Customer’, Value =>
join(“\n”,@customerids) );

I noticed the “Content” field in the ObjectCustomFieldValues table is
limited to varchar(255) so the second psudo-code example above would
probably not be a good idea.

ALSO - If anyone has advice on how to split incoming tickets for tons of IP
address into individual tickets please let me know. I have an idea of how
I’m going to do it already based on some parsing and create perl code but
there’s always more than one way to skin a cat.

In fact, having just re-read what I wrote above, I think I might be going
about this incorrectly. Perhaps I should not be adding ALL the data per
Incident Report but rather per Incident. I should probably restrict the
data added to the Incident Report to just Customer ID only. Perhaps the
single report having many IP addresses listed should be linked to a new
Incident per customer instead of creating multiple Incident Reports. That
actually makes more sense.

Thanks for reading! Any feedback is appreciated.

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

For example if there are 1000 IP addresses in a ticket and there are 4
custom fields to be updated it creates 4000 transactions for that one
ticket when looping each update individually.

What I’m wondering is if there’s a better way to make these updates so
multiple values can be added to the ticket with one transaction.

Not quite want you want but can you silence the transactions by using
the long form of the Set method described in the “How to be Silent”
section of
http://requesttracker.wikia.com/wiki/WriteCustomAction

Gareth

For example if there are 1000 IP addresses in a ticket and there are 4
custom fields to be updated it creates 4000 transactions for that one
ticket when looping each update individually.

What I’m wondering is if there’s a better way to make these updates so
multiple values can be added to the ticket with one transaction.

Not quite want you want but can you silence the transactions by
using the long form of the Set method described in the “How to be
Silent”
section of
http://requesttracker.wikia.com/wiki/WriteCustomAction

It’d be safer to use the RecordTransaction argument to
AddCustomFieldValue which tells RT whether or not to record a
transaction.

This avoids the problem of directly manipulating OCFVs and potentially
truncating them when you shove content > 255 into the Content field.
RT transparently bounces between the Content and LargeContent field as
needed if you use the API.

-kevin

It’d be safer to use the RecordTransaction argument to
AddCustomFieldValue which tells RT whether or not to record a
transaction.

This avoids the problem of directly manipulating OCFVs and potentially
truncating them when you shove content > 255 into the Content field.
RT transparently bounces between the Content and LargeContent field as
needed if you use the API.

So, to be clear here, if in my RT::Action::ModuleName action module if I
use something like this:

$Ticket->AddCustomFieldValue( Field => ‘Customer’, Value =>
join(“\n”,@customerids), RecordTransaction => 0 );

And if @customerids contained a list of 1000 five or six digit numbers it
would not choke on that many because LargeContent would get used or because
I’m not recording the transaction at all? Or am I mistaken here somewhere
still?

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

 It'd be safer to use the RecordTransaction argument to
 AddCustomFieldValue which tells RT whether or not to record a
 transaction.

 This avoids the problem of directly manipulating OCFVs and potentially
 truncating them when you shove content > 255 into the Content field.
 RT transparently bounces between the Content and LargeContent field as
 needed if you use the API.

So, to be clear here, if in my RT::Action::ModuleName action module if I use something like
this:
$Ticket->AddCustomFieldValue( Field => ‘Customer’, Value => join("\n",@customerids),
RecordTransaction => 0 );
And if @customerids contained a list of 1000 five or six digit numbers it would not choke on
that many because LargeContent would get used or because I’m not recording the transaction at
all? Or am I mistaken here somewhere still?

Because Custom Fields can hold a LONGBLOB worth of data.
Go save a large text CF and look in the database.
Saving them as 1000 individual values makes 1000 records in the
database, saving them as 1 large record makes 1 record. These are
treated differently on update and display.

If you follow the advice of the wiki article that was linked to and
reach around the API, you’ll hurt yourself.

Not recording transactions is orthogonal to the size of the data which
can be recorded.

-kevin

Because Custom Fields can hold a LONGBLOB worth of data.
Go save a large text CF and look in the database.
Saving them as 1000 individual values makes 1000 records in the
database, saving them as 1 large record makes 1 record. These are
treated differently on update and display.

If you follow the advice of the wiki article that was linked to and
reach around the API, you’ll hurt yourself.

Not recording transactions is orthogonal to the size of the data which
can be recorded.

I’m sorry - I’m pretty sure I’m reading you correctly but I’m unable to add
multiple values to a Custom Field without creating multiple
ObjectCustomFieldValue records. Maybe it’s not possible to do so.

The %Updates hash is a hash of arrays like this (this is one key, I don’t
know why Dumper prints it as two VARs):
$VAR11 = ‘Customer’;
$VAR12 = [
‘1xxx59’,
‘1xxx22’,
‘1xxx56’,
‘1xxx5’,
‘6xxx5’,
‘1xxx93’,
‘6xxx1’,
‘4xxx1’,
‘2xxx1’,
‘3xxx8’,
‘6xxx7’,
‘1xxx08’,
‘1xxx36’
];

Here’s what I’ve tried and seen:
foreach my $ufn (keys %Updates) {
$ticket->AddCustomFieldValue( Field => $ufn, Value => join(“\n”,
@{$Updates{$ufn}}), RecordTransaction => 0 );
}
– What this does is adds one ObjectCustomFieldValue record with \n between
some stuff. These are displayed in RT as values with spaces between them.

foreach my $ufn (keys %Updates) {
$ticket->AddCustomFieldValue( Field => $ufn, Value => join(‘,’,
@{$Updates{$ufn}}), RecordTransaction => 0 );
}
– I didn’t think this would really work but I thought I’d try it anyway.
Same thing but comma delimited. They are displayed in RT as values with
commas between them.

So in other words I’m starting to think I’ve been after something that
can’t be done. RecordTransaction => 0 stops the “Transactions” table from
getting a ton of records added to it (one for each OCFV record added) but I
cannot avoid tons of OCFV records being added.

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

 Because Custom Fields can hold a LONGBLOB worth of data.
 Go save a large text CF and look in the database.
 Saving them as 1000 individual values makes 1000 records in the
 database, saving them as 1 large record makes 1 record. These are
 treated differently on update and display.

 If you follow the advice of the wiki article that was linked to and
 reach around the API, you'll hurt yourself.

 Not recording transactions is orthogonal to the size of the data which
 can be recorded.

I’m sorry - I’m pretty sure I’m reading you correctly but I’m unable to add multiple values to
a Custom Field without creating multiple ObjectCustomFieldValue records. Maybe it’s not

If you have a multiple value custom field, then each value will be an
OCFV.

As I wrote above “Go save a large text CF and look in the database”
(added emphasis on text).

It’s unclear to me how you think we could store a Custom Field with a
thousand distinct values as anything other than distinct records in
the database?

Think particularly about searching in the context of my question.

RecordTransactions => 0 does what it sounds like, it stops you from
getting 1000 transactions about the updates.

-kevin

If you have a multiple value custom field, then each value will be an
OCFV.

As I wrote above “Go save a large text CF and look in the database”

(added emphasis on text).

It’s unclear to me how you think we could store a Custom Field with a
thousand distinct values as anything other than distinct records in
the database?

I thought it was possible simply because I was asked by someone who has
experience with RT to find out how to do it.

Anyway - thanks for confirming that it’s not possible at least.

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932