Forum GLPI-Project

Satyan

Member

From: Ain

Registered: 2005-03-16

Posts: 186

Importation des infos ocs vers glpi

Bonjour,

J'ai regardé les différentes aides sur ocs (celle du logiciel et celle de glpi) mais je n'ai pas trouvé comment importer les données ocs vers glpi.
J'utilise le fichier refresh.bat mais j'ai des erreurs 1054.

Merci de votre aide.

---

Satyan

Communiquer c'est commencer à exister.

ludwig33

Member

Registered: 2005-05-03

Posts: 27

Re: Importation des infos ocs vers glpi

tu utilises MySQL sur Linux ou Windows ?

Vérifies si ce n'est pas un problème d'accès à la base de donnée ?

érifies si ce n'est pas un problème au niveau des majuscules. J'ai adapté le script refresh.bat à un batch sur Linux et j'ai eu ce type d'erreur.

Si ça t'interresse...

le -pglpi, signivie que j'envoi glpi en tant que password...

#!/bin/bash

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computers(name) SELECT DISTINCT deviceID FROM SOFTWARES WHERE SOFTWARES.deviceID NOT IN (select name from glpi_computers);"

mysql -u glpiuser -pglpi Inventory -e "Update glpi_computers, BIOS Set glpi_computers.serial = BIOS.SSN WHERE (glpi_computers.Name = BIOS.DEVICEID)"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_computer_device;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_software;"
mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_licenses;"
mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_inst_software;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_software(name, version) SELECT DISTINCT name, version FROM SOFTWARES; "

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_licenses(sID) (SELECT DISTINCT glpi_software.ID FROM glpi_software);"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_inst_software(cID, license) (SELECT DISTINCT glpi_computers.ID, glpi_software.ID FROM glpi_computers, SOFTWARES, glpi_software WHERE glpi_computers.name = SOFTWARES.DeviceID AND SOFTWARES.name = glpi_software.name AND SOFTWARES.version = glpi_software.version);"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_ram;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_ram (designation) SELECT DISTINCT memory FROM HARDWARE; "

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_ram.ID, glpi_computers.ID FROM glpi_computers, HARDWARE, glpi_device_ram WHERE glpi_computers.name = HARDWARE.DeviceID AND HARDWARE.memory = glpi_device_ram.designation) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, HARDWARE,glpi_device_ram SET device_type = '3' WHERE device_type ='0' AND glpi_computers.name = HARDWARE.DeviceID AND HARDWARE.memory = glpi_device_ram.designation;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_processor;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_processor (designation, frequence) SELECT DISTINCT processort,processors FROM HARDWARE;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_processor.ID, glpi_computers.ID FROM glpi_computers, HARDWARE, glpi_device_processor WHERE glpi_computers.name = HARDWARE.DeviceID AND HARDWARE.processort = glpi_device_processor.designation AND HARDWARE.processors = glpi_device_processor.frequence ) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, HARDWARE,glpi_device_processor SET device_type = '2' WHERE device_type ='0' AND glpi_computers.name = HARDWARE.DeviceID AND HARDWARE.processort = glpi_device_processor.designation AND HARDWARE.processors = glpi_device_processor.frequence;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_hdd;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_hdd (designation,specif_default)   SELECT DISTINCT type,total FROM DRIVES WHERE type = 'Hard Drive';"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_hdd.ID, glpi_computers.ID FROM glpi_computers, DRIVES, glpi_device_hdd WHERE glpi_computers.name = DRIVES.DeviceID AND DRIVES.type = glpi_device_hdd.designation AND DRIVES.total = glpi_device_hdd.specif_default) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, DRIVES,glpi_device_hdd SET device_type = '4' WHERE device_type ='0' AND glpi_computers.name = DRIVES.DeviceID AND DRIVES.type = glpi_device_hdd.designation AND DRIVES.total = glpi_device_hdd.specif_default;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_drive;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_drive (designation)  (SELECT DISTINCT type FROM DRIVES WHERE type = 'CD-ROM Drive');"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_drive.ID, glpi_computers.ID FROM glpi_computers, DRIVES, glpi_device_drive WHERE glpi_computers.name = DRIVES.DeviceID AND DRIVES.type = glpi_device_drive.designation) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, DRIVES,glpi_device_drive SET device_type = '6' WHERE device_type ='0' AND glpi_computers.name = DRIVES.DeviceID AND DRIVES.type = glpi_device_drive.designation;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_sndcard;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_sndcard (designation) SELECT DISTINCT name FROM SOUNDS ;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_sndcard.ID, glpi_computers.ID FROM glpi_computers, SOUNDS, glpi_device_sndcard WHERE glpi_computers.name = SOUNDS.DeviceID AND SOUNDS.name = glpi_device_sndcard.designation) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, SOUNDS,glpi_device_sndcard SET glpi_computer_device.device_type = '9' WHERE device_type ='0'AND glpi_computers.name = SOUNDS.DeviceID AND SOUNDS.name = glpi_device_sndcard.designation;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_gfxcard;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_gfxcard(designation)  SELECT DISTINCT name FROM VIDEOS ;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_gfxcard.ID, glpi_computers.ID FROM glpi_computers, VIDEOS, glpi_device_gfxcard WHERE glpi_computers.name = VIDEOS.DeviceID AND VIDEOS.name = glpi_device_gfxcard.designation) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, VIDEOS,glpi_device_gfxcard SET glpi_computer_device.device_type = '8' WHERE glpi_computer_device.device_type ='0' AND glpi_computers.name = VIDEOS.DeviceID AND VIDEOS.name = glpi_device_gfxcard.designation;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_dropdown_os;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_dropdown_os(name)   SELECT DISTINCT osname FROM HARDWARE WHERE osname NOT LIKE 'NULL';"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computers, HARDWARE, glpi_dropdown_os SET  os = glpi_dropdown_os.ID WHERE glpi_computers.name = HARDWARE.DeviceID AND HARDWARE.osname = glpi_dropdown_os.name ;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_device_iface;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_device_iface (designation, bandwidth, comment, specif_default) (SELECT DISTINCT description, speed, ipaddress, macaddr FROM NETWORKS );"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_computer_device( FK_device, FK_computers ) (SELECT DISTINCT glpi_device_iface.ID, glpi_computers.ID FROM glpi_computers, NETWORKS, glpi_device_iface WHERE glpi_computers.name = NETWORKS.DeviceID AND NETWORKS.description = glpi_device_iface.designation AND NETWORKS.ipaddress = glpi_device_iface.comment) ;"

mysql -u glpiuser -pglpi Inventory -e "UPDATE glpi_computer_device,glpi_computers, NETWORKS, glpi_device_iface SET device_type = '5'WHERE device_type ='0' AND glpi_computers.name = NETWORKS.DeviceID AND NETWORKS.description = glpi_device_iface.designation AND NETWORKS.ipaddress = glpi_device_iface.comment;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_printers;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_printers (name, comments) (SELECT DISTINCT name, port FROM PRINTERS );"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_enterprises;"

mysql -u glpiuser -pglpi Inventory -e "TRUNCATE TABLE glpi_monitors;"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_enterprises (name) (SELECT DISTINCT manufacturer FROM MONITORS );"

mysql -u glpiuser -pglpi Inventory -e "INSERT INTO glpi_monitors (name) (SELECT DISTINCT description FROM MONITORS );"

Satyan

Member

From: Ain

Registered: 2005-03-16

Posts: 186

Re: Importation des infos ocs vers glpi

J'utilise windows xp, le message c'est unknown column 'specif default' in 'field list' et d'autres messages avec d'autres colonnes

---

Satyan

Communiquer c'est commencer à exister.

ludwig33

Member

Registered: 2005-05-03

Posts: 27

Re: Importation des infos ocs vers glpi

Est-ce que tes tables OCS et GLPI sont dans la même base de données ?

prediction

Member

Registered: 2005-07-07

Posts: 11

Re: Importation des infos ocs vers glpi

Peut être que certaines données de OCS ne correspondent a rien dans GLPI ?

Satyan

Member

From: Ain

Registered: 2005-03-16

Posts: 186

Re: Importation des infos ocs vers glpi

oui les tables ocs et glpi sont dans la meme base.

---

Satyan

Communiquer c'est commencer à exister.

ludwig33

Member

Registered: 2005-05-03

Posts: 27

Re: Importation des infos ocs vers glpi

je sens des erreur de syntaxe. Vérifies bien l'ortographe des tables ..

n'oublies pas de mettre le nom de la base .. pour tout modifier en profondeur utilises l'outil "remplacer" de ton éditeur de texte.

Satyan

Member

From: Ain

Registered: 2005-03-16

Posts: 186

Re: Importation des infos ocs vers glpi

Les informations sont bien dans la base inventory qui est la meme que glpi. mais la correspondance entre les tables ne se fait pas correctement.

---

Satyan

Communiquer c'est commencer à exister.