RTAT Import?

Has anyone developed a script or something to import an inventory into
AT (from a csv file, for example)?

js.
Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

The script below takes a tab-delimited file of format:

AssetTag Make Model Serial Building Room MACAddress

And populated a bunch of CFs. Salt to taste.On Tue, 2008-04-29 at 14:01 +0000, Jean-Sebastien Morisset wrote:

Has anyone developed a script or something to import an inventory into
AT (from a csv file, for example)?

js.

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

MAIN

    my $BFILE="/opt/rt3/wslist";


    open(BF,"<$BFILE") or die "$!\n";

    my $ass = RTx::AssetTracker::Asset->new(RT->SystemUser);

    while(<BF>) {
            # 12478 Gateway E4100   0032459740      KEL-HL  219

00:0C:F1:D5:49:2E
chomp;
my @lineparts=split(/\t/);
my $sysname=$lineparts[4];
$sysname =~ s/\W.*$//; # Strip from non-word to eol
$sysname .= $lineparts[5] . “-” . $lineparts[0];

            my ($id, undef, undef) = $ass->Create(Type =>

‘Workstations’,
Name =>
"$sysname",
Status =>
‘production’,
‘CustomField-7’
=> “$lineparts[0]”, # Decal
’CustomField-3’
=> “$lineparts[1]”, # Make
’CustomField-4’
=> “$lineparts[2]”, # Model
’CustomField-8’
=> “$lineparts[3]”, # Serial
’CustomField-59’
=> “$lineparts[4]”, # Building
’CustomField-60’
=> “$lineparts[5]”, # Room
’CustomField-62’
=> “$lineparts[6]”, # MAC address
);
print “$sysname created with id $id\n”;
}

Matthew,

Thanks for your code. This was an exellent starting point. Since I
already have a bunch of assets, I tried to incorporate some code to
update assets. I’m not sure what I’m doing wrong though… Here’s the
result:

/opt/rt3/bin/import-assets.sh /tmp/js.csv

gessolx1:
Loaded gessolx1 as asset #85.
CustomField-10 could not be set to STI.
CustomField-11 could not be set to Montreal QC CA.
CustomField-12 could not be set to SUN.
CustomField-14 could not be set to X4200.
That is already the current value
CustomField-16 could not be set to SUN Solaris 10.
uasolx1:
Loaded uasolx1 as asset #86.
CustomField-10 could not be set to STI.
CustomField-11 could not be set to Montreal QC CA.
CustomField-12 could not be set to SUN.
CustomField-14 could not be set to 0.
CustomField-15 could not be set to 0.
CustomField-16 could not be set to 0.

And the code:

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

if (! $ARGV[0] || $ARGV[0] =~ /^–?h/) {
print “Syntax: $0 {csvfile}\n”;
exit 0;
}

open(CSV, “< $ARGV[0]”) or die “$!\n”;

my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;

while () {
my ($el, $id, $t, $msg);
chomp; $line++; next if ($line == 1);
s/^“//; s/”$//;
my @csv = split(/“?,”?/);
my @fn = (
‘Name’,
‘Status’,
‘Type’,
‘Description’,
‘CustomField-10’, # Client
‘CustomField-11’, # Location
‘CustomField-12’, # Manufacturer
‘CustomField-14’, # Model
‘CustomField-15’, # Serial Number
‘CustomField-16’, # OS Name
);

    print $csv[0], ":\n";
    if ($id = $at->Load($csv[0])) {
            print "\tLoaded $csv[0] as asset #$id.\n";
    } else {
            ($id, $t, $msg) = $at->Create (
                    Name => $csv[0],
                    Status => $csv[1],
                    Type => $csv[2],
                    Description => $csv[3],
            );
            print "\t$msg\n";
    }

    for my $k (@fn) {
            if ($k =~ /^CustomField/) {
                    ($id, $msg) = $at->_Set (
                            Field => $k,
                            Value => $csv[$el],
                            UpdateAsset => 1,
                            TransactionData => undef,
                    );
                    print "\t$msg\n";
            }
            $el++;
    }

}

Any ideas on what I could be doing wrong?

js.On Tue, Apr 29, 2008 at 11:26:41AM -0400, Matthew Keller wrote:

The script below takes a tab-delimited file of format:

AssetTag Make Model Serial Building Room MACAddress

And populated a bunch of CFs. Salt to taste.

On Tue, 2008-04-29 at 14:01 +0000, Jean-Sebastien Morisset wrote:

Has anyone developed a script or something to import an inventory into
AT (from a csv file, for example)?

js.

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

MAIN

    my $BFILE="/opt/rt3/wslist";


    open(BF,"<$BFILE") or die "$!\n";

    my $ass = RTx::AssetTracker::Asset->new(RT->SystemUser);

    while(<BF>) {
            # 12478 Gateway E4100   0032459740      KEL-HL  219

00:0C:F1:D5:49:2E
chomp;
my @lineparts=split(/\t/);
my $sysname=$lineparts[4];
$sysname =~ s/\W.*$//; # Strip from non-word to eol
$sysname .= $lineparts[5] . “-” . $lineparts[0];

            my ($id, undef, undef) = $ass->Create(Type =>

‘Workstations’,
Name =>
“$sysname”,
Status =>
‘production’,
‘CustomField-7’
=> “$lineparts[0]”, # Decal
‘CustomField-3’
=> “$lineparts[1]”, # Make
‘CustomField-4’
=> “$lineparts[2]”, # Model
‘CustomField-8’
=> “$lineparts[3]”, # Serial
‘CustomField-59’
=> “$lineparts[4]”, # Building
‘CustomField-60’
=> “$lineparts[5]”, # Room
‘CustomField-62’
=> “$lineparts[6]”, # MAC address
);
print “$sysname created with id $id\n”;
}

Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

The ‘$at->_Set’ calls you’re making scare me. Is there a reason you’re
setting them iteratively instead of within the Create call?On Tue, 2008-04-29 at 20:08 +0000, Jean-Sebastien Morisset wrote:

Matthew,

Thanks for your code. This was an exellent starting point. Since I
already have a bunch of assets, I tried to incorporate some code to
update assets. I’m not sure what I’m doing wrong though… Here’s the
result:

/opt/rt3/bin/import-assets.sh /tmp/js.csv

gessolx1:
Loaded gessolx1 as asset #85.
CustomField-10 could not be set to STI.
CustomField-11 could not be set to Montreal QC CA.
CustomField-12 could not be set to SUN.
CustomField-14 could not be set to X4200.
That is already the current value
CustomField-16 could not be set to SUN Solaris 10.
uasolx1:
Loaded uasolx1 as asset #86.
CustomField-10 could not be set to STI.
CustomField-11 could not be set to Montreal QC CA.
CustomField-12 could not be set to SUN.
CustomField-14 could not be set to 0.
CustomField-15 could not be set to 0.
CustomField-16 could not be set to 0.

And the code:

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

if (! $ARGV[0] || $ARGV[0] =~ /^–?h/) {
print “Syntax: $0 {csvfile}\n”;
exit 0;
}

open(CSV, “< $ARGV[0]”) or die “$!\n”;

my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;

while () {
my ($el, $id, $t, $msg);
chomp; $line++; next if ($line == 1);
s/^"//; s/"$//;
my @csv = split(/"?,"?/);
my @fn = (
‘Name’,
‘Status’,
‘Type’,
‘Description’,
‘CustomField-10’, # Client
’CustomField-11’, # Location
’CustomField-12’, # Manufacturer
’CustomField-14’, # Model
’CustomField-15’, # Serial Number
’CustomField-16’, # OS Name
);

    print $csv[0], ":\n";
    if ($id = $at->Load($csv[0])) {
            print "\tLoaded $csv[0] as asset #$id.\n";
    } else {
            ($id, $t, $msg) = $at->Create (
                    Name => $csv[0],
                    Status => $csv[1],
                    Type => $csv[2],
                    Description => $csv[3],
            );
            print "\t$msg\n";
    }

    for my $k (@fn) {
            if ($k =~ /^CustomField/) {
                    ($id, $msg) = $at->_Set (
                            Field => $k,
                            Value => $csv[$el],
                            UpdateAsset => 1,
                            TransactionData => undef,
                    );
                    print "\t$msg\n";
            }
            $el++;
    }

}

Any ideas on what I could be doing wrong?

js.

On Tue, Apr 29, 2008 at 11:26:41AM -0400, Matthew Keller wrote:

The script below takes a tab-delimited file of format:

AssetTag Make Model Serial Building Room MACAddress

And populated a bunch of CFs. Salt to taste.

On Tue, 2008-04-29 at 14:01 +0000, Jean-Sebastien Morisset wrote:

Has anyone developed a script or something to import an inventory into
AT (from a csv file, for example)?

js.

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

MAIN

    my $BFILE="/opt/rt3/wslist";


    open(BF,"<$BFILE") or die "$!\n";

    my $ass = RTx::AssetTracker::Asset->new(RT->SystemUser);

    while(<BF>) {
            # 12478 Gateway E4100   0032459740      KEL-HL  219

00:0C:F1:D5:49:2E
chomp;
my @lineparts=split(/\t/);
my $sysname=$lineparts[4];
$sysname =~ s/\W.*$//; # Strip from non-word to eol
$sysname .= $lineparts[5] . “-” . $lineparts[0];

            my ($id, undef, undef) = $ass->Create(Type =>

‘Workstations’,
Name =>
"$sysname",
Status =>
‘production’,
‘CustomField-7’
=> “$lineparts[0]”, # Decal
’CustomField-3’
=> “$lineparts[1]”, # Make
’CustomField-4’
=> “$lineparts[2]”, # Model
’CustomField-8’
=> “$lineparts[3]”, # Serial
’CustomField-59’
=> “$lineparts[4]”, # Building
’CustomField-60’
=> “$lineparts[5]”, # Room
’CustomField-62’
=> “$lineparts[6]”, # MAC address
);
print “$sysname created with id $id\n”;
}

The ‘$at->_Set’ calls you’re making scare me. Is there a reason you’re
setting them iteratively instead of within the Create call?

I run a Load, if the Load doesn’t work, then do a Create (with just the
basics), after that update all the CFs. This way I can maintain just the
@fn array, and update and/or create the assets as need be from the CSV.

js.
Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

Ah yes… The way I usually do things like that is by grabbing the asset
CF info, ala:

my $AssCFs=$AssObj->CustomFields();

and then setting the appropriate values ala:

while(my $AssCF = $AssCFs->Next()) {
$AssCF->Value = whatever
}

The above is pseudocode from memory, but is +/- 96% correct. :)On Tue, 2008-04-29 at 20:18 +0000, Jean-Sebastien Morisset wrote:

On Tue, Apr 29, 2008 at 04:15:03PM -0400, Matthew Keller wrote:

The ‘$at->_Set’ calls you’re making scare me. Is there a reason you’re
setting them iteratively instead of within the Create call?

I run a Load, if the Load doesn’t work, then do a Create (with just the
basics), after that update all the CFs. This way I can maintain just the
@fn array, and update and/or create the assets as need be from the CSV.

js.

Ah yes… The way I usually do things like that is by grabbing the asset
CF info, ala:

my $AssCFs=$AssObj->CustomFields();

and then setting the appropriate values ala:

while(my $AssCF = $AssCFs->Next()) {
$AssCF->Value = whatever
}

The above is pseudocode from memory, but is +/- 96% correct. :slight_smile:

Hmmm… Here’s what I get:

/opt/rt3/bin/import-assets.sh /tmp/js.csv

gessolx1:
Loaded gessolx1 as asset #85.
Setting Client to STI
[Tue Apr 29 21:00:16 2008] [crit]: Can’t modify non-lvalue subroutine
call at /opt/rt3/bin/import-assets.sh line 56, line 2.
(/opt/rt3/lib/RT.pm:361)
Can’t modify non-lvalue subroutine call at /opt/rt3/bin/import-assets.sh
line 56, line 2.

And here’s the code I’m using…

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

if (! $ARGV[0] || $ARGV[0] =~ /^–?h/) {
print “Syntax: $0 {csvfile}\n”;
exit 0;
}

open(CSV, “< $ARGV[0]”) or die “$!\n”;

my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;

customfield columns in csv file

my %cf_map = (
‘Client’ => 4,
‘Location’ => 5,
‘Manufacturer’ => 6,
‘Model’ => 7,
‘Serial Number’ => 8,
‘OS Name’ => 9,
);

while () {
chomp; $line++;
next if ($line == 1); # Skip CSV Header
s/^“//; s/”$//;
my @csv = split(/“?,”?/);

    print $csv[0], ":\n";
    if (my $id = $at->Load($csv[0])) {
            print "\tLoaded $csv[0] as asset #$id.\n";
    } else {
            my ($id, $t, $msg) = $at->Create (
                    Name => $csv[0],
                    Status => $csv[1],
                    Type => $csv[2],
                    Description => $csv[3],
            );
            print "\t$msg\n";
    }

    my $atcf = $at->CustomFields();
    while (my $cf = $atcf->Next()) {
            # check to see if we have a column number for this customfield
            if (my $col = $cf_map{$cf->Name}) {
                    print "\tSetting ".$cf->Name." to ".$csv[$col]."\n";
                    $cf->Value = $csv[$col];
            }
    }

}

Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

If you look in the API documentation about setting a CF (the
’CustomField’ object), it will show you where my oversimplification of
$cf->Value=$thing was in err, and how to do it correctly.On Tue, 2008-04-29 at 21:01 +0000, Jean-Sebastien Morisset wrote:

On Tue, Apr 29, 2008 at 04:32:43PM -0400, Matthew Keller wrote:

Ah yes… The way I usually do things like that is by grabbing the asset
CF info, ala:

my $AssCFs=$AssObj->CustomFields();

and then setting the appropriate values ala:

while(my $AssCF = $AssCFs->Next()) {
$AssCF->Value = whatever
}

The above is pseudocode from memory, but is +/- 96% correct. :slight_smile:

Hmmm… Here’s what I get:

/opt/rt3/bin/import-assets.sh /tmp/js.csv

gessolx1:
Loaded gessolx1 as asset #85.
Setting Client to STI
[Tue Apr 29 21:00:16 2008] [crit]: Can’t modify non-lvalue subroutine
call at /opt/rt3/bin/import-assets.sh line 56, line 2.
(/opt/rt3/lib/RT.pm:361)
Can’t modify non-lvalue subroutine call at /opt/rt3/bin/import-assets.sh
line 56, line 2.

And here’s the code I’m using…

#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

if (! $ARGV[0] || $ARGV[0] =~ /^–?h/) {
print “Syntax: $0 {csvfile}\n”;
exit 0;
}

open(CSV, “< $ARGV[0]”) or die “$!\n”;

my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;

customfield columns in csv file

my %cf_map = (
‘Client’ => 4,
‘Location’ => 5,
‘Manufacturer’ => 6,
‘Model’ => 7,
‘Serial Number’ => 8,
‘OS Name’ => 9,
);

while () {
chomp; $line++;
next if ($line == 1); # Skip CSV Header
s/^"//; s/"$//;
my @csv = split(/"?,"?/);

    print $csv[0], ":\n";
    if (my $id = $at->Load($csv[0])) {
            print "\tLoaded $csv[0] as asset #$id.\n";
    } else {
            my ($id, $t, $msg) = $at->Create (
                    Name => $csv[0],
                    Status => $csv[1],
                    Type => $csv[2],
                    Description => $csv[3],
            );
            print "\t$msg\n";
    }

    my $atcf = $at->CustomFields();
    while (my $cf = $atcf->Next()) {
            # check to see if we have a column number for this customfield
            if (my $col = $cf_map{$cf->Name}) {
                    print "\tSetting ".$cf->Name." to ".$csv[$col]."\n";
                    $cf->Value = $csv[$col];
            }
    }

}

If you look in the API documentation about setting a CF (the
‘CustomField’ object), it will show you where my oversimplification of
$cf->Value=$thing was in err, and how to do it correctly.

Aha! Thanks! Ok, so far this seems to work well:

—BEGIN—
#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
RT::LoadConfig();
RT::Init();

use strict;

if (! $ARGV[0] || $ARGV[0] =~ /^–?h/) {
print “Syntax: $0 {csvfile}\n”;
exit 0;
}

open(CSV, “< $ARGV[0]”) or die “$!\n”;

my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;

customfield columns in csv file

my %cf_map = (
‘Client’ => 4,
‘Location’ => 5,
‘Manufacturer’ => 6,
‘Model’ => 7,
‘Serial Number’ => 8,
‘OS Name’ => 9,
);

while () {
chomp; $line++;
next if ($line == 1); # Skip CSV Header
s/^“//; s/”$//;
my @csv = split(/“?,”?/);

    print $csv[0], ":\n";
    unless (my $id = $at->Load($csv[0])) {
            my ($id, $t, $msg) = $at->Create (
                    Name => $csv[0],
                    Status => $csv[1],
                    Type => $csv[2],
                    Description => $csv[3],
            );
            print "\t$msg\n" if ($msg);
    }

    my $atcf = $at->CustomFields();
    while (my $cf = $atcf->Next()) {
            # check to see if we have a CSV column for this customfield
            if (my $col = $cf_map{$cf->Name}) {
                    my ($status, $msg) = $at->AddCustomFieldValue(
                            Field => $cf->Id, Value => $csv[$col]);
                    print "\t$msg\n" if ($msg);
            }
    }

}
—END—

And for reference, here’s the CSV file I’m using:

“NAME”,“STATUS”,“TYPE”,“DESCRIPTION”,“CLIENT”,“LOCATION”,“MANUFACTURER”,“MODEL”,“SERIAL NUMBER”,“OS NAME”

Column 5 (CLIENT) and up are all custom fields and must be defined in
the %cf_map hash. If anyone wants to make this a little more elegant,
feel free. :slight_smile:

js.
Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

Aha! Thanks! Ok, so far this seems to work well:
[snip!]

Alright, so I spent a little more time on this script, and added some
validation against the customfield select values. Here’s an example:

bin/import-assets.sh /tmp/js.csv

gessolx1:
uasolx1:
“” value not ok for OS Name customfield
BDM-MTL-FWSM-PROD-1:
“X” value not ok for Client customfield

And here’s the script:

—BEGIN—
#!/usr/bin/perl

use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
use RTx::AssetTracker::Asset;
use Getopt::Std;
use strict;

our ($opt_h, $opt_f);
getopts(‘hf:’);

RT::LoadConfig();
RT::Init();
my $at = RTx::AssetTracker::Asset->new(RT->SystemUser);
my $line;
my @csv_def = (
‘Name’,
‘Status’,
‘Type’,
‘Description’,
‘Client’,
‘Location’,
‘Manufacturer’,
‘Model’,
‘Serial Number’,
‘OS Name’,
‘Switch Type’,
);

if ($opt_h || !$opt_f) {
print " Syntax: $0 [-h] -f {csvfile}\n";
print "CSV Format: ";
for (@csv_def) { print “"$_",”; }
print “\n”;
exit 0;
}

open(CSV, “< $opt_f”) or die “$!\n”;

customfield columns in csv file

my (%cf_map, $cf_item);
for (@csv_def) { $cf_map{$_} = $cf_item++; }

while () {
chomp; $line++;
next if ($line == 1); # Skip CSV Header
s/^“//; s/”$//;
my @csv = split(/“?,”?/);
print $csv[0], “:\n”;
unless (my $id = $at->Load($csv[0])) {
my ($id, $t, $msg) = $at->Create (
Name => $csv[0],
Status => $csv[1],
Type => $csv[2],
Description => $csv[3],
);
print “\t$msg\n” if ($msg);
}
my $atcf = $at->CustomFields();
# check each customfield for this asset
while (my $cf = $atcf->Next()) {
# check to see if we have a CSV column for this customfield
if (my $col = $cf_map{$cf->Name}) {
my $accept;
if ($cf->Type eq “Select”) {
# read all selectable values for this customfield
my $cfvs = $cf->Values;
while (my $value = $cfvs->Next) {
# check to see if new values is defined in select
if ($value->Name eq $csv[$col]) {
$accept = 1;
last;
}
}
} elsif ($cf->Type eq “Freeform”) {
$accept = 1;
} else {
print “\tneed more code for “.$cf->Type.” customfield type\n”;
}
if ($accept) {
my ($status, $msg) = $at->AddCustomFieldValue(
Field => $cf->Id,
Value => $csv[$col],
RecordTransaction => 1,
);
print “\t$msg\n” if ($msg);
} else {
print “\t"$csv[$col]" value not ok for “.$cf->Name.” customfield\n”;
}
}
}
}
—END—

Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

Aha! Thanks! Ok, so far this seems to work well:
[snip!]

Alright, so I spent a little more time on this script, and added some
validation against the customfield select values. Here’s an example:
[snip!]

Well, I couldn’t leave well enough alone, and kept tweeking the import
script. I know a few people are already using my first script, so you’ll
probably enjoy this little update (script attached). :slight_smile:

[root:/opt/rt3/bin]

./rt-import-assets.pl

syntax: ./rt-import-assets.pl [-h] [-c] [-d] -f {csvfile}
-c: create missing assets (script only updates by default)
-d: change empty Description csv fields to Manufacturer and Model

js.
Jean-Sebastien Morisset, Sr. UNIX Administrator jsmoriss@mvlan.net

rt-import-assets.pl (4.34 KB)