You are not logged in.
Pages: 1
Topic closed
I have a new GPLI installation and I would now like to migrate my existing data (database) from IRM. Is this possible and if so can you point me to the solution?
Thanks in advance
GLPI version (glpi-0.72.21.tar.gz)
Offline
Only one solution i think
Export your data in csv files if you can with IRM, and then import with the data injection plugin
Last edited by doum (2010-02-22 23:09:23)
Offline
I recently completed this very task.
I ended up writing a perl script to do the migration. We hacked our IRM to death adding features and such, so you'll probably have to modify it, but its fairly straightforward.
#!/usr/bin/perl
#use warnings FATAL => 'all', NONFATAL => 'uninitialized';
use warnings FATAL => 'all';
use DBI;
$irmdb = DBI->connect('DBI:mysql:database=irm;host=','','');
$glpidb = DBI->connect('DBI:mysql:database=glpi;host=','','');
for my $table (qw(computers networking monitors plugin_rack plugin_rack_content plugin_rack_device_spec plugin_map_items dropdown_plugin_rack_ways dropdown_manufacturer dropdown_model dropdown_model_networking contracts contract_device plugin_order_orders plugin_order_items dropdown_plugin_order_model_other plugin_order_projects ocs_link device_drive device_gfxcard device_hdd device_iface device_moboard device_pci device_power device_processor device_ram device_sndcard computer_device computerdisks networking_ports inst_software softwareversions software softwarelicenses history dropdown_domain dropdown_locations dropdown_os dropdown_os_version dropdown_ram_type connect_wire dropdown_filesystems dropdown_model_monitors dropdown_os_sp dropdown_software_category enterprises peripherals plugin_order_model_links printers)) {
$glpidb->do("truncate table glpi_$table");
}
$drops = {};
$idlinks = {};
%eqtypes = (
'computers' => 1,
'docs' => 13,
'monitors' => 4,
'networking' => 2,
'peripherals' => 5,
'phones' => 23,
'printers' => 3,
);
$q_rackID = $glpidb->prepare("select id from glpi_plugin_rack where name=? and FK_location=?");
$q_rackSpec_1 = $glpidb->prepare("select id from glpi_plugin_rack_device_spec where fk_model=? and device_type=?");
$q_rackSpec_2 = $glpidb->prepare("insert into glpi_plugin_rack_device_spec (fk_model,device_type,nb_alim,size,length) values(?,?,2,1,1)");
$q_glpidb_rack_insert = $glpidb->prepare("insert into glpi_plugin_rack_content (fk_rack,device_type,fk_device,fk_face,fk_spec,position,first_powersupply,second_powersupply) values(?,?,?,1,?,?,?,?)");
rack_populate();
contracts_populate();
$q_irm_location = $irmdb->prepare("select *,substring_index(eq_type,' ',1) as manf, substring(eq_type,instr(eq_type,' ')) as model,nullif(nullif(os,'No OS installed'),'Unknown') as fixed_os,rackid as rack, 'server' as type, if(rackid = 'xxx',1,0) as deleted from location left join computers on location.ID=computers.ID where eq_type not like '%rack%'");
$q_irm_location->execute();
while($row = $q_irm_location->fetchrow_hashref()) {
lckeys($row);
$i = {};
my($eqtype) = $row->{'eq_type'};
if($row->{'eq_type'} =~ /cisco/i) {
$type = 'networking';
} elsif($row->{'eq_type'} =~ /(\bmonitor\b|\blcd\b)/i) {
$type = 'monitors';
} elsif($row->{'eq_type'} =~ /(\bhd\b|\bkvm\b|\bomni\b|keyboard|\bdrive\b|harddrive|cd-?rom|\bdvd\b|modem|portmaster|external|secure web|adtran|analyzer|\bapc\b|tft5600|datasmart|storagetek|blackberry|\bmsa\b|windows mobile|tape library|diskpack|storage works|iphone|msa2000)/i) {
$type = 'peripherals';
} elsif($eqtype =~ /(nokia ip|silkworm|ethernet|netscreen|officeconnect switch|big ip|server iron|fortigate|internap|fs708|larscom|gigafast|100tx|riverbed|datasmart|procurve|avocent|aggregator)/i) {
$type = 'networking';
} elsif($eqtype =~ /(laserjet|officejet)/i) {
$type = 'printers';
} else {
$type = 'computers';
}
remap($row,$i,[ #this gets stored as an array, but created as a hash so that we can have duplicate keys.
'dc_location' => "$type.location{drop:locations}",
#'purpose' => "$type.contact",
'boxname' => "$type.name",
'comments' => "$type.comments",
'manf' => "$type.fk_glpi_enterprise{drop:manufacturer}",
'eq_type' => "$type.model{drop:model_$type}",
'serialnum' => "$type.serial",
'assettag' => "$type.otherserial",
'fixed_os' => "$type.os{drop:os}",
'type' => "$type.type{drop:type_$type}",
'deleted' => "$type.deleted",
]);
my($newlinks) = insertMap($glpidb,$i); #returns a hash of the glpi_TABLENAME => ID maps for the tables the data was inserted into
@{$idlinks->{'location'}->{$row->{'id'}}}{keys %$newlinks} = values(%$newlinks);
#if(defined($row->{'rack'}) && $row->{'rack'} =~ /^\d?[a-z]\d+$/i && $row->{'rack_pos'} =~ /^\d+$/) {
if(defined($row->{'rack'}) && $row->{'rack'} =~ /^\d?([a-zA-Z0-9\-]+)-(\d+)$/) {
my($rack,$rack_pos) = ($1,$2);
rack_insert($type,$idlinks->{'location'}->{$row->{'id'}}->{$type},$row->{'eq_type'},$row->{'dc_location'},$rack,$rack_pos,$row->{'primary_power'},$row->{'backup_power'});
}
if(defined($row->{'ms_id'})) {
$i = {};
remap($row,$i,[
"{$idlinks->{'support_contracts'}->{$row->{'ms_id'}}->{'contracts'}}" => "contract_device.fk_contract",
"{$idlinks->{'location'}->{$row->{'id'}}->{$type}}" => "contract_device.fk_device",
"{$eqtypes{$type}}" => "contract_device.device_type",
]);
my($newlinks) = insertMap($glpidb,$i);
@{$idlinks->{'location'}->{$row->{'id'}}}{keys %$newlinks} = values(%$newlinks);
#print("Computer $idlinks->{'location'}->{$row->{'id'}}->{$type}, Contract $idlinks->{'support_contracts'}->{$row->{'ms_id'}}->{'contracts'} => $idlinks->{'location'}->{$row->{'id'}}->{'contract_device'}\n");
}
}
$q_irm_orders = $irmdb->prepare("select spo.*,if(instr(group_concat(spolist.status),'not recieved'),0,1) as complete from spo left join spolist on spo.po_id = spolist.po_id group by spo.po_id");
$q_irm_order_items = $irmdb->prepare("select * from spolist where po_id=?");
$q_irm_orders->execute();
while($row = $q_irm_orders->fetchrow_hashref()) {
lckeys($row);
$i = {};
$projects = 'plugin_order_projects';
remap($row,$i,[
'project' => "$projects.name",
'shipto' => "$projects.lead{drop:users}",
]);
my($projectlinks) = insertMap($glpidb,$i);
$i = {};
$orders = 'plugin_order_orders';
remap($row,$i,[
'po_id' => "$orders.order_num",
'odate' => "$orders.order_date",
'vendor' => "$orders.supplier{drop:enterprises}",
'location' => "$orders.ship_loc{drop:locations}",
'comments' => "$orders.comments",
'shipno' => "$orders.ship_num",
'project' => "$orders.project{drop:$projects}",
'complete' => "$orders.completed",
]);
my($orderlinks) = insertMap($glpidb,$i);
$items = 'plugin_order_items';
$q_irm_order_items->execute($row->{'po_id'});
while($itemrow = $q_irm_order_items->fetchrow_hashref()) {
my($state) = $itemrow->{'status'};
if($state eq 'not recieved') {
$state = 'ordered';
}
$deliver_date = '';
if($state eq 'received' && defined($itemrow->{'cdate'})) {
$deliver_date = $itemrow->{'cdate'};
}
$ii = {};
remap($itemrow,$ii,[
"{$orderlinks->{$orders}}" => "$items.order_id",
'podesc' => "$items.line",
'{7510}' => "$items.type", #7510 is the id used for 'other' items in our glpi order plugin
'podesc' => "$items.model{drop:plugin_order_model_other}",
"{$state}" => "$items.state",
"icomment" => "$items.comment",
"{$deliver_date}" => "$items.deliver_date",
]);
my($a);
for($a = 0; $a < $itemrow->{'qty'}; $a++) {
my($itemlinks) = insertMap($glpidb,$ii)
}
}
}
sub lckeys {
my($hashref) = $_[0];
%$hashref = map { lc $_ => $hashref->{$_} } keys %$hashref;
}
sub dropValue {
my($table_short,$value,$retnull) = @_;
if(!defined($value) || length($value) == 0) {
return($retnull);
}
if(defined($drops->{$table_short}) && defined($drops->{$table_short}->{$value})) {
return($drops->{$table_short}->{$value});
}
my($tableprefix) = 'dropdown_';
if($table_short =~ /^type_(.+)$/) {
if(defined($eqtypes{$1})) { #for some reason these werent prefixed with 'dropdown_'
$tableprefix = '';
}
}
if($table_short =~ /^(enterprises|users|plugin_order_projects)/) { #these arent really dropdown tables, but we want to treat them as such
$tableprefix = '';
}
$table_short =~ s/model_computers/model/; #and the computers model dropdown table doesnt have _computers after it, but the other equipment types do
my($q_dropValue_1) = $glpidb->prepare("select id from glpi_$tableprefix$table_short where name=?");
my($q_dropValue_2) = $glpidb->prepare("insert into glpi_$tableprefix$table_short (name) values(?)");
$q_dropValue_1->execute($value);
my($row);
if($row = $q_dropValue_1->fetchrow_hashref()) {
$drops->{$table_short}->{$value} = $row->{'id'};
return($row->{'id'});
}
$q_dropValue_2->execute($value);
if(!defined($drops->{$table_short})) {
$drops->{$table_short} = {};
}
$drops->{$table_short}->{$value} = $glpidb->last_insert_id(undef,undef,undef,undef);
if($table_short eq 'locations') {
my($updateq) = $glpidb->prepare("update glpi_$tableprefix$table_short set completename=name where name=?");
$updateq->execute($value);
}
return($drops->{$table_short}->{$value});
}
sub remap {
my($src,$dst,$map) = @_;
my($table);
my(@mapcopy) = @$map;
while(my $srckey = shift(@mapcopy)) {
my($dstkey) = shift(@mapcopy);
if($dstkey =~ /^([^\.]+)\.(.+)$/) {
if(!defined($dst->{$1})) {
$dst->{$1} = {};
}
$table = $dst->{$1};
$dstkey = $2;
} else {
$table = $dst;
}
my($srcval);
if($srckey =~ /^\{([^}]+)\}$/) {
$srcval = $1;
} else {
$srcval = $src->{$srckey};
}
if($dstkey =~ /^(.+?)\{drop:(.+)\}$/) {
$dstkey = $1;
$value = dropValue($2,$srcval);
} else {
$value = $srcval;
}
$table->{$dstkey} = $value;
}
}
sub insertMap {
my($db,$map) = @_;
$newids = {};
for my $tableName (keys(%$map)) {
my($tableData) = $map->{$tableName};
my(%insertData);
for my $key (keys(%$tableData)) {
if(defined($tableData->{$key}) && $tableData->{$key} ne '') {
$insertData{$key} = $tableData->{$key};
}
}
my(@keys) = keys(%insertData);
#print("insert into glpi_$tableName (" . join(',',@keys) . ") values(" . join(',',values(%insertData)) . ")\n");
my($q) = $db->prepare("insert into glpi_$tableName (" . join(',',@keys) . ") values(" . join(',',split(//,'?'x($#keys + 1))) . ")"); # I <3 this line
$q->execute(values(%insertData));
$newids->{$tableName} = $db->last_insert_id(undef,undef,undef,undef);
}
return($newids);
}
sub rack_populate {
#my($irmq) = $irmdb->prepare("select dc_location,substring_index(rackid,'-',1) as rack,eq_type,assettag from location left join computers on computers.id=location.id where eq_type like '%rack%' and dc_location!='Not In Inventory'");
my($irmq) = $irmdb->prepare("select dc_location,rackid rack,eq_type,assettag from location left join computers on computers.id=location.id where eq_type like '%rack%' and dc_location!='Not In Inventory'");
my($glpiq) = $glpidb->prepare("insert into glpi_plugin_rack (name,fk_location,rack_size,notes) values(?,?,40,?)");
$irmq->execute();
my($row);
while($row = $irmq->fetchrow_hashref()) {
my($rackname) = lc($row->{'rack'});
$rackname =~ s/^1//;
$rackname =~ s/-00$//;
my($location_id) = dropValue('locations',$row->{'dc_location'});
$glpiq->execute($rackname,$location_id,$row->{'eq_type'});
$drops->{'rack'}->{"$location_id:$rackname"} = $glpidb->last_insert_id(undef,undef,undef,undef);
}
}
sub rackID {
my($rack,$location) = @_;
$rack = lc($rack);
$rack =~ s/^1//;
$location_id = dropValue('locations',$location);
if(defined($drops->{'rack'}->{"$location_id:$rack"})) {
return($drops->{'rack'}->{"$location_id:$rack"});
}
$q_rackID->execute($rack,$location_id);
my($row);
if($row = $q_rackID->fetchrow_hashref()) {
$drops->{'rack'}->{"$location_id:$rack"} = $row->{'id'};
return($row->{'id'});
}
}
sub rackSpec {
my($model,$type) = @_;
my($model_id) = dropValue("model_$type",$model);
if(defined($drops->{'rack_spec'}->{$type}->{$model_id})) {
return($drops->{'rack_spec'}->{$type}->{$model_id});
}
$q_rackSpec_1->execute($model_id,$eqtypes{$type});
my($row);
if($row = $q_rackSpec_1->fetchrow_hashref()) {
$drops->{'rack_spec'}->{$type}->{$model_id} = $row->{'id'};
return($row->{'id'});
}
$q_rackSpec_2->execute($model_id,$eqtypes{$type});
$drops->{'rack_spec'}->{$type}->{$model_id} = $glpidb->last_insert_id(undef,undef,undef,undef);
return($glpidb->last_insert_id(undef,undef,undef,undef));
}
sub rack_insert {
my($type,$id,$model,$loc,$rack,$pos,$power1,$power2) = @_;
my($rackID) = rackID($rack,$loc);
if(defined($rackID)) { #there are a few items in the DB with their rack set to a rack that doesnt actually exist, so we have to verify them here
if($power2 eq 'NA') {
$power2 = undef;
}
$q_glpidb_rack_insert->execute($rackID,$eqtypes{$type},$id,rackSpec($model,$type),$pos,dropValue('plugin_rack_ways',$power1,0),dropValue('plugin_rack_ways',$power2,0));
}
}
sub contracts_populate {
$q_irm_contracts = $irmdb->prepare("select ms_id, concat(company_name,' - ',account_num) as name, company_name, start_date, end_date, period_diff(date_format(end_date,'%Y%m'),date_format(start_date,'%Y%m')) as duration, pri_contact, phone_number, account_num, service_level, concat(pri_contact,'\n',service_level,'\n',comments) as comments, if(end_date < now(),1,0) as deleted from support_contracts");
$q_irm_contracts->execute();
$i = {};
while($row = $q_irm_contracts->fetchrow_hashref()) {
remap($row,$i,[
'name' => 'contracts.name',
'start_date' => 'contracts.begin_date',
'duration' => 'contracts.duration',
'duration' => 'contracts.periodicity',
'duration' => 'contracts.facturation',
'{1}' => 'contracts.notice',
'phone_number' => 'contracts.num',
'account_num' => 'contracts.compta_num',
'comments' => 'contracts.comments',
'deleted' => 'contracts.deleted',
]);
$idlinks->{'support_contracts'}->{$row->{'ms_id'}} = insertMap($glpidb,$i);
}
for $months (qw(12 24 36 48)) {
$glpidb->do("update glpi_contracts set duration=$months where duration=" . ($months - 1));
$glpidb->do("update glpi_contracts set periodicity=$months where periodicity=" . ($months - 1));
$glpidb->do("update glpi_contracts set facturation=$months where facturation=" . ($months - 1));
}
}
Edit: thats from IRM 1.55. EXTREMELY old version. Doubt its anything similar to the newer versions.
Last edited by feystorm (2010-02-23 01:44:47)
Offline
Thank you all!
I have solved the issue.
Best
Offline
Pages: 1
Topic closed