[rt-users] simple web script to fill "link values to" from db

[Vegard Vesterheim]

On Tue, 4 May 2010 09:34:43 -0400 "Michael W. Lucas" <mwlucas at blackhelicopters.org> wrote:

> Hi,
>
> We're running RT 3.8.6 with postgresql on FreeBSD 8.
>
> I have a set of outside databases I'd like to extract some RT custom
> field values from: circuit IDs, services, etc.  This seems like it
> would be easy to do with the CustomField "Link values to" option.
>
> Surely someone's already written a simple CGI/PHP/somesuch script to
> pull values like this from a postgres database?  I could probably
> remember enough PHP to do it myself, but I'd prefer to use a wheel
> someone else invented.

I am not sure I understand your problem description fully. Why does
this have to be a "web/CGI" script?  The purpose of the "Link values
to" option is to make these Custom Field Values in the RT web UI into
hyperlinks to another service. It is not for importing values to RT.

I have recently written a quick'n'dirty perl script to automatically
update som CFs from an external DB. This script has not been put into
production yet, so it may contain errors, but I think it illustrates the
basic idea. I would like to generalize the script so that it can be reused
for other CFs. 

My idea is to run this script periodically (from cron) so that the CF
values are automatically updated.

You will need to fill out the blanks (...) yourself

Kind::DBIx is simply an DBIx::Class-based Perl module for accessing our external DB.

----- snip - snip -------------------------------------------------
#! /usr/bin/perl 
# Update customfields in RT with values from external DB

use strict;
use Getopt::Long;
use Kind::DBIx;
use DBI;
use Data::Dumper;

my $rt_passwd;

GetOptions (
            'rt_passwd:s' => \$rt_passwd,
           );

my $rt_dbh = DBI->connect ('dbi:Pg:...;dbname=...', 'rt_user...', $rt_passwd);

my $cf = $rt_dbh->selectcol_arrayref
    ('select id from customfields where name=?', undef, '...CF_FIELD_NAME...');
my $cf_field_no = $cf->[0];
die "No valid cf field found" unless $cf_field_no;

my $rt_utstyr_ref = $rt_dbh->selectcol_arrayref
    ('select name from customfieldvalues where customfield = ?', undef, $cf_field_no);
my @rt_utstyr = @{$rt_utstyr_ref};

my $rt_insert_sth = $rt_dbh->prepare ('insert into customfieldvalues (customfield, name) values (?, ?)');

my @kind_utstyr = map {$_->navn} Kind::DBIx->init()->resultset('Utstyr')->search 
    ({ -and => [
                -or => [
                        # utstyrstype => 20,                                                                                               
                        # utstyrstype => 30,                                                                                               
			# # utstyrstype => 40, # switches                                                                                  
                        utstyrstype => 80,
                        # utstyrstype => 90,                                                                                               
                        # utstyrstype => 100,                                                                                              
                       ],
                status => 20,
		navn => {'!=' => undef},
                navn => {'!=' => ''},
               ]});

my %in_rt;
@in_rt{@rt_utstyr} = ();

my %in_kind;
@in_kind{@kind_utstyr} = ();

my (@new_utstyr, @old_utstyr);
foreach my $rt (@rt_utstyr) {
    push (@old_utstyr, $rt) unless exists $in_kind{$rt};
}
 
foreach my $kind (@kind_utstyr) {
    push (@new_utstyr, $kind) unless exists $in_rt{$kind};
}

map {$rt_insert_sth->execute ($cf_field_no, $_)} @new_utstyr;
print "Added", Dumper @new_utstyr;

# FIXME, remove values that have "disappeared"
print "To be removed", Dumper @old_utstyr;
----- snip - snip -------------------------------------------------