Creating a customer drop-down multi-select menu with a remote SQL query

RT Users
1

Howdy,

Still trying to feel my way around RT, and couldn’t find a definitive answer
in any of the docs/wiki…what I aim to do is enforce some consistency in
customer names used in the Subject lines of tickets…I figure this should
make things like reporting chronic issues/customers with endless problems
rather simply if they’re all listed the same rather than the multiple
abbreviations that get used.

I’ve written a quick and nasty 5-liner in Perl that queries a MySQL table on
a different box, and returns a complete list of customer names. Assuming
that these names will live in a drop-down custom field that allows
multi-selects, how would I kick off this script and feed the results in to
that drop-down? I’m trying very, very hard to not sledge this directly in
to the RT code itself, but to bolt it in based on the current frame-work so
that future ugprades won’t be as much a trainwreck as our last one was (due
to lots of customization inside RT itself). Any clues or steers in the
right direction would be appreciated :slight_smile:

Thanks!

One way or another, everyone stops bleeding.

2

WARNING - Trying this might be a Really Stupid idea

This might work, but it messes with the RT database directly, so before
doing anything else you should (in this order):

  1. wait about a week to see how badly this idea gets flailed by the
    developers and the more experienced users;
  2. consider it carefully a couple dozen times before doing it;
  3. cross your fingers;
  4. cover your ears in case it explodes

That said, I’d certainly try it. But I’ve always been kind of a thrill
seeker.

When you set up a “select multiple values” CF in RT, an entry for it is
added to the CustomFields table of the rt3 database. After you’ve created
your CF and given it at least 1 value, go into that table and find the “id”
value that goes with your CF. Write it down here: _________

The CustomFieldValues table of the rt3 database has a record for each
“select multiple values” entry. What I would do is write a 27-line perl
script that adds an entry to this table for each of the names you pulled
from your other database with the 5-liner you wrote. The fields you need
to be concerned with are:
CustomField: A constant, the number you got from looking at the
CustomFields table and that you wrote down above
Name: This is what shows up in the pull-down menu for your CF, put the
names you extracted here
Description: I’m not sure what this text field is used for or where it
shows up other than the page used for creating custom fields
SortOrder: Integer determines what order the items appear in your
pull-down list

To be complete, you should also fill these fields:
Creator: A constant - your user id (an integer) goes here
Created: Today’s date
LastUpdatedBy: Same as Creator
LastUpdated: Same as Created

Downsides:

  1. It might explode.
  2. You only get a snapshot of your other database, there’s no linkage
    to keep the CF values in synch with the other database.
  3. It might explode.

Before trying this, go back and repeat steps 1 and 2 from my first list,
paying particular attention to step 1.

Good luck!
Gene

At 01:04 PM 5/8/2007, mike wrote:

Still trying to feel my way around RT, and couldn’t find a definitive
answer in any of the docs/wiki…what I aim to do is enforce some
consistency in customer names used in the Subject lines of tickets…I
figure this should make things like reporting chronic issues/customers
with endless problems rather simply if they’re all listed the same rather
than the multiple abbreviations that get used.

I’ve written a quick and nasty 5-liner in Perl that queries a MySQL table
on a different box, and returns a complete list of customer
names. Assuming that these names will live in a drop-down custom field
that allows multi-selects, how would I kick off this script and feed the
results in to that drop-down? I’m trying very, very hard to not sledge
this directly in to the RT code itself, but to bolt it in based on the
current frame-work so that future ugprades won’t be as much a trainwreck
as our last one was (due to lots of customization inside RT itself). Any
clues or steers in the right direction would be appreciated :slight_smile:

Gene LeDuc, GSEC
Security Analyst
San Diego State University

3

Yeah, I was afraid of it being a potentially Really Stupid Idea - I’m no
Perl idiot, nor am I a real Perl hacker, so I am definitely concerned about
this perhaps not being the best solution to the problem I’m trying to
solve. I am going to chew this over and wait for some more feedback from
the devs and generally more clueful RT folk. I have a test RT box that I
can break six ways to Sunday, so this would absolutely go through a good
hammering in test before ever seeing our production RT system…I’m pretty
sure I’d be strung up if I hurt the prod system, the upgrade to 3.6.3 was
interesting enough (too much magic sledged in the old version from previous
employees :)). The lack of linkage between the two disparate DBs isn’t too
large a problem, but I would definitely need to find a clever way of keeping
it updated at least two or three times per day, so I’ll need to do some more
thinking on that, too. at first glace, rt-crontool doesn’t seem to be my
answer to that, but i had great hope for about 30 seconds :slight_smile: Thanks for the
great response, I clearly have a lot more thinking to do!

cheers,
mikeOn 5/8/07, Gene LeDuc gleduc@mail.sdsu.edu wrote:

WARNING - Trying this might be a Really Stupid idea

This might work, but it messes with the RT database directly, so before
doing anything else you should (in this order):

  1. wait about a week to see how badly this idea gets flailed by the
    developers and the more experienced users;
  2. consider it carefully a couple dozen times before doing it;
  3. cross your fingers;
  4. cover your ears in case it explodes

That said, I’d certainly try it. But I’ve always been kind of a thrill
seeker.

When you set up a “select multiple values” CF in RT, an entry for it is
added to the CustomFields table of the rt3 database. After you’ve created
your CF and given it at least 1 value, go into that table and find the
“id”
value that goes with your CF. Write it down here: _________

The CustomFieldValues table of the rt3 database has a record for each
“select multiple values” entry. What I would do is write a 27-line perl
script that adds an entry to this table for each of the names you pulled
from your other database with the 5-liner you wrote. The fields you need
to be concerned with are:
CustomField: A constant, the number you got from looking at the
CustomFields table and that you wrote down above
Name: This is what shows up in the pull-down menu for your CF, put the
names you extracted here
Description: I’m not sure what this text field is used for or where it
shows up other than the page used for creating custom fields
SortOrder: Integer determines what order the items appear in your
pull-down list

To be complete, you should also fill these fields:
Creator: A constant - your user id (an integer) goes here
Created: Today’s date
LastUpdatedBy: Same as Creator
LastUpdated: Same as Created

Downsides:

  1. It might explode.
  2. You only get a snapshot of your other database, there’s no linkage
    to keep the CF values in synch with the other database.
  3. It might explode.

Before trying this, go back and repeat steps 1 and 2 from my first list,
paying particular attention to step 1.

Good luck!
Gene

At 01:04 PM 5/8/2007, mike wrote:

Still trying to feel my way around RT, and couldn’t find a definitive
answer in any of the docs/wiki…what I aim to do is enforce some
consistency in customer names used in the Subject lines of tickets…I
figure this should make things like reporting chronic issues/customers
with endless problems rather simply if they’re all listed the same rather
than the multiple abbreviations that get used.

I’ve written a quick and nasty 5-liner in Perl that queries a MySQL table
on a different box, and returns a complete list of customer
names. Assuming that these names will live in a drop-down custom field
that allows multi-selects, how would I kick off this script and feed the
results in to that drop-down? I’m trying very, very hard to not sledge
this directly in to the RT code itself, but to bolt it in based on the
current frame-work so that future ugprades won’t be as much a trainwreck
as our last one was (due to lots of customization inside RT itself). Any
clues or steers in the right direction would be appreciated :slight_smile:

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

One way or another, everyone stops bleeding.