Simple web script to fill "link values to" from db

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.

Thanks,
==ml

Michael W. Lucas mwlucas@BlackHelicopters.org
http://www.MichaelWLucas.com/
New book: Network Flow Analysis
pre-order now! http://www.networkflowanalysis.com/

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