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 21900: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 21900: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.
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.
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. 
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).
[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)