Forum GLPI-Project

bsmckenz

Member

Registered: 2009-10-21

Posts: 5

Upgraded from IRM to GLPI and want to import my IRM database

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)

doum

Member

From: Nice

Registered: 2007-03-27

Posts: 1,421

Re: Upgraded from IRM to GLPI and want to import my IRM database

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)

feystorm

Member

Registered: 2009-10-13

Posts: 6

Re: Upgraded from IRM to GLPI and want to import my IRM database

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)

bsmckenz

Member

Registered: 2009-10-21

Posts: 5

Re: Upgraded from IRM to GLPI and want to import my IRM database

Thank you all!

I have solved the issue.

Best

JMD

GLPI - Lead

Registered: 2004-09-13

Posts: 9,180

Website

Re: Upgraded from IRM to GLPI and want to import my IRM database

I close .

---

JMD / Jean-Mathieu Doléans - Glpi-project.org - Association Indepnet
Apportez votre pierre au  projet GLPI   : Soutenir