GLPI - OCS Data upload questions

BeccanatriX · 2006-12-07 03:17:45

First of all, congratulations to all the developers on this project! I found this just yesterday, showed my boss and now he's upset we wasted any time on our previous asset software! GLPI combined with OCS has just about everything we were looking for, so thank you!

I have a few questions as far as tweaking the link between the two databases. I'm using GLPI .68.2 and OCS 4020 in a Windows Server 2003 environment.

First, I've managed to upload the software from OCS to GLPI using the dictionary, and can see the list of software for my client test box. However, when I click Inventory -> Software, there is nothing listed. My monitor, printers, etc all come up fine, so I'm assuming Software should as well. In the Admin Setup area for OCS NG, I have everything set to Global Import except Software, which is set to Unit Import (Global being unavailable).

Second, in the OCS NG setup, there is a dropdown menu for the OCS Tag information. I've tried setting this value to each of the options listed (Inventory, Contact Number, etc), butI haven't noticed any information being pulled. I assumed that the Tag information in OCS would be pulled into the field chosen in the dropdown menu, however if I'm looking in the wrong place, please let me know.

Thank you again for your efforts and for your help!

MoYo · 2006-12-07 10:56:59

How do you update the computers ?

BeccanatriX · 2006-12-07 18:46:23

I've only loaded 1 test machine so far, so I've been selecting that computer in GLPI, going to the OCS NG tab and clicking Force Synchronization. This has worked to update Software when I make changes in the dictionary (to exclude Updates, for example), however there is still no Software listed under Inventory.

MoYo · 2006-12-07 20:52:59

If you active the import in the OCS mode configuration softwares must be import in the computer.
I do not understand.

We can not finf the problem without the DBs...

BeccanatriX · 2006-12-07 21:45:09

I select Inventory -> Computers and select my machine. In the Software tab, the software is listed correctly. However, if I select Inventory -> Software, there is nothing listed.

Since there isn't a 'Global Import' option for the Software settings in OCS NG Setup, I'm wondering if they are even supposed to be pulled to the general Inventory area or just limited to the individual computers. For example, I have Monitors set to Global Import and my monitor appears both under Inventory -> Computers -> My Machine -> Connections tab as well just Inventory -> Monitors.

MoYo · 2006-12-07 22:43:32

you have softwares linked to computers but no software listed ?

BeccanatriX · 2006-12-07 22:44:45

There is software listed under individual computers, but no software under Inventory.

MoYo · 2006-12-08 01:41:10

if software is liste une der individual computers, they must appear on the inventory.
These is link between computers and software.
If you click on a software in the computer you see it ?

In debug mode which query is done for the inventory software list ?

BeccanatriX · 2006-12-08 01:50:06

I entered debug mode and directly went to Inventory -> Software. Here is the output:

N° 1 :
SELECT *
FROM glpi_display
WHERE type='6' AND FK_users='2'
ORDER BY rank
Time: 0.001s
--------------------------------------------------------------------------------
N° 2 :
SELECT *
FROM glpi_display
WHERE type='6' AND FK_users='0'
ORDER BY rank
Time: 0.001s
--------------------------------------------------------------------------------
N° 3 :
SELECT count(ID)
FROM glpi_software
WHERE glpi_software.deleted='N' AND glpi_software.is_template='0'
Time: 0.001s
--------------------------------------------------------------------------------
N° 4 :
SET SESSION group_concat_max_len = 9999999;
Time: 0.000s
--------------------------------------------------------------------------------
N° 5 :
SELECT glpi_software.name AS ITEM_0, glpi_enterprises.name AS ITEM_1, glpi_enterprises.website AS ITEM_1_2, glpi_enterprises.ID AS ITEM_1_3, glpi_software.version AS ITEM_2, glpi_dropdown_os.name AS ITEM_3, glpi_software.ID AS ID
FROM glpi_software
LEFT JOIN glpi_enterprises ON (glpi_software.FK_glpi_enterprise = glpi_enterprises.ID)
LEFT JOIN glpi_dropdown_os ON (glpi_software.platform = glpi_dropdown_os.ID)
LEFT JOIN glpi_dropdown_locations ON (glpi_software.location = glpi_dropdown_locations.ID)
LEFT JOIN glpi_state_item ON (glpi_software.ID = glpi_state_item.id_device AND glpi_state_item.device_type='6')
LEFT JOIN glpi_dropdown_state ON (glpi_state_item.state = glpi_dropdown_state.ID)
LEFT JOIN glpi_licenses ON (glpi_software.ID = glpi_licenses.sID)
LEFT JOIN glpi_users AS glpi_users_tech_num ON (glpi_software.tech_num = glpi_users_tech_num.ID)
LEFT JOIN glpi_users AS glpi_users_FK_users ON (glpi_software.FK_users = glpi_users_FK_users.ID)
LEFT JOIN glpi_groups ON (glpi_software.FK_groups = glpi_groups.ID)
LEFT JOIN glpi_tracking ON (glpi_tracking.device_type='6' AND glpi_software.ID = glpi_tracking.computer)
LEFT JOIN glpi_contract_device ON (glpi_software.ID = glpi_contract_device.FK_device AND glpi_contract_device.device_type='6')
LEFT JOIN glpi_contracts ON (glpi_contract_device.FK_contract = glpi_contracts.ID)
LEFT JOIN glpi_infocoms ON (glpi_software.ID = glpi_infocoms.FK_device AND glpi_infocoms.device_type='6')
LEFT JOIN glpi_dropdown_budget ON (glpi_infocoms.budget = glpi_dropdown_budget.ID)
LEFT JOIN glpi_enterprises AS glpi_enterprises_infocoms ON (glpi_infocoms.FK_enterprise = glpi_enterprises_infocoms.ID)
WHERE glpi_software.deleted='N' AND glpi_software.is_template='0' GROUP BY ID
ORDER BY glpi_software.name ASC LIMIT 0, 15

MoYo · 2006-12-08 10:58:42

hum hum
default query must be :
SELECT glpi_software.name AS ITEM_0, glpi_enterprises.name AS ITEM_1, glpi_enterprises.website AS ITEM_1_2, glpi_enterprises.ID AS ITEM_1_3, glpi_software.version AS ITEM_2, glpi_dropdown_os.name AS ITEM_3, glpi_software.ID AS ID
FROM glpi_software
LEFT JOIN glpi_enterprises ON (glpi_software.FK_glpi_enterprise = glpi_enterprises.ID)
LEFT JOIN glpi_dropdown_os ON (glpi_software.platform = glpi_dropdown_os.ID)
WHERE glpi_software.deleted='N' AND glpi_software.is_template='0'
ORDER BY glpi_software.name ASC LIMIT 0, 15

which GLPI version do you used ?

BeccanatriX · 2006-12-08 19:42:54

0.68.2

MoYo · 2006-12-08 19:59:51

which items do you have in "Default search fields list" for softwares ?

Have you modify the GLPI source code ?
Your request is unbelievable... if you have LEFT JOIN in the request you need to have more SELECT than default ones.

BeccanatriX · 2006-12-11 19:28:41

I haven't changed the code at all, and I don't enter any search criteria, just click Inventory -> Softwares.

MoYo · 2006-12-12 05:48:03

and for the first question ?

BeccanatriX · 2006-12-12 19:20:20

Manufacturer, Version, Platform

BeccanatriX · 2006-12-14 01:03:50

Ok, I haven't changed the code at all, but now the query is reading out like the default on and still there is no software listed under Inventory. Is there a key in the database that links computers and software that could be missing? Or could the problem be in the OCS upload functions?

MoYo · 2006-12-14 01:29:59

you could check in the database if :
WHERE glpi_software.deleted='N' AND glpi_software.is_template='0'

these conditions are ok for your datas.

BeccanatriX · 2006-12-14 01:55:56

Everything is marked with deleted = 'Y'. Could that be happening when I upload from OCS?

MoYo · 2006-12-14 01:59:18

humm no it must be not deleted
you could restore them showing them from the search list and modify all of them using massive action.

In your database which is the default value of the deleted field ?

BeccanatriX · 2006-12-14 04:14:02

But then won't I have to modify any software the is imported with a new computer? The default setting for the deleted field is N, so there must be somewhere in the code that it gets changed to Y...

MoYo · 2006-12-14 10:48:19

you could see in te history of the software if there is a modification done.

BeccanatriX · 2006-12-14 22:22:24

I'm not seeing anywhere that lists a history of modifications... I've changed the deleted field to N for a few pieces of software so that they show up under Inventory, but there is nothing in their Historical tab and no history information in the Installations page. I did notice that under Serial Number in the Software Installations tab, the license information (free, global, etc) is listed, but it doesn't seem like that should matter.

It seems as though the license, enterprise, software and inst_software tables are referenced when dealing with Software. Only software and enterprise tables have a deleted field, and all of my entries for enterprise have deleted set to N. I'm not sure where the deleted field in the software table is even accessed. I've ran a search for 'fields["deleted"]' and haven't been able to find anywhere this field is set to Y. Could you explain the process that takes place when a new computer is imported and its software is stored in the db? It looks like the function ocsImportComputer calls updateComputer which in turn calls updateSoftware. From there, the software is determined to be new or not and then three functions are called: ocsImportEnterprises, ocsImportTag and InstallSoftware. I haven't been able to find anything in these, but maybe some more understanding of the process behind the scenes would help. Thank you!

BeccanatriX · 2006-12-14 22:24:30

I meant ocsImportLicense, not ocsImportTag...

BeccanatriX · 2006-12-14 23:00:00

Progress! In the ocsUpdateSoftware function in ocsng.function.php, there is an error in the if statement checking for whether the software is new or not. In the block under if (!in_array($name,$already_imported)), there is query that sets the variable $isNewSoft. Then that variable is checked for a value, but the if and else statements are backwards. In the original, the first if reads:

if (!$isNewSoft) {
$soft = new Software;
$soft->fields["name"] = $name;
$soft->fields["version"] = $version;
if (!empty($publisher))
$soft->fields["FK_glpi_enterprise"] = ocsImportEnterprise($publisher);
$isNewSoft = $soft->addToDB();
}

However, this is obviously setting up the fields for new software. To fix, just remove the ! from that if statement and add it to the following if statement that in the original reads:

if ($isNewSoft){
$instID=installSoftware($glpi_id,ocsImportLicense($isNewSoft),'',$dohistory);
addToOcsArray($glpi_id,array($instID=>$initname),"import_software");
}

After making that easy change, I can import a new computer and have its software come up under Inventory. However, now I don't see any software listed under that computer, so this might not be the root cause of the problem...

BeccanatriX · 2006-12-14 23:12:00

To see the software listed under individual computers, I just copied the two lines from

if ($isNewSoft){
$instID=installSoftware($glpi_id,ocsImportLicense($isNewSoft),'',$dohistory);
addToOcsArray($glpi_id,array($instID=>$initname),"import_software");
}

into the previous if statement. So now, the final copy that pulls software into individual computers and into inventory is the following:

if ($isNewSoft) {
$soft = new Software;
$soft->fields["name"] = $name;
$soft->fields["version"] = $version;
if (!empty($publisher))
$soft->fields["FK_glpi_enterprise"] = ocsImportEnterprise($publisher);
$isNewSoft = $soft->addToDB();
$instID=installSoftware($glpi_id,ocsImportLicense($isNewSoft),'',$dohistory);
addToOcsArray($glpi_id,array($instID=>$initname),"import_software");
}

if (!$isNewSoft){
$instID=installSoftware($glpi_id,ocsImportLicense($isNewSoft),'',$dohistory);
addToOcsArray($glpi_id,array($instID=>$initname),"import_software");
}

Now the only other issue I'm having is duplicates of some software. So for instance, Windows XP is listed under software for each computer it is installed on due to the Unit Importation limitation. It would be nice to be able to group the software as global since ultimately, we only want to know how many copies are out there and if we have enough licenses. The inidivdual machines the software is installed in is already listed under the Installations tab, so having only 1 copy listed in the Inventory would save space and time... I'm hoping there's a way to set the dictionary in OCS to achieve this.

Forum GLPI-Project

Announcement

#1 2006-12-07 03:17:45

GLPI - OCS Data upload questions

#2 2006-12-07 10:56:59

Re: GLPI - OCS Data upload questions

#3 2006-12-07 18:46:23

Re: GLPI - OCS Data upload questions

#4 2006-12-07 20:52:59

Re: GLPI - OCS Data upload questions

#5 2006-12-07 21:45:09

Re: GLPI - OCS Data upload questions

#6 2006-12-07 22:43:32

Re: GLPI - OCS Data upload questions

#7 2006-12-07 22:44:45

Re: GLPI - OCS Data upload questions

#8 2006-12-08 01:41:10

Re: GLPI - OCS Data upload questions

#9 2006-12-08 01:50:06

Re: GLPI - OCS Data upload questions

#10 2006-12-08 10:58:42

Re: GLPI - OCS Data upload questions

#11 2006-12-08 19:42:54

Re: GLPI - OCS Data upload questions

#12 2006-12-08 19:59:51

Re: GLPI - OCS Data upload questions

#13 2006-12-11 19:28:41

Re: GLPI - OCS Data upload questions

#14 2006-12-12 05:48:03

Re: GLPI - OCS Data upload questions

#15 2006-12-12 19:20:20

Re: GLPI - OCS Data upload questions

#16 2006-12-14 01:03:50

Re: GLPI - OCS Data upload questions

#17 2006-12-14 01:29:59

Re: GLPI - OCS Data upload questions

#18 2006-12-14 01:55:56

Re: GLPI - OCS Data upload questions

#19 2006-12-14 01:59:18

Re: GLPI - OCS Data upload questions

#20 2006-12-14 04:14:02

Re: GLPI - OCS Data upload questions

#21 2006-12-14 10:48:19

Re: GLPI - OCS Data upload questions

#22 2006-12-14 22:22:24

Re: GLPI - OCS Data upload questions

#23 2006-12-14 22:24:30

Re: GLPI - OCS Data upload questions

#24 2006-12-14 23:00:00

Re: GLPI - OCS Data upload questions

#25 2006-12-14 23:12:00

Re: GLPI - OCS Data upload questions

Board footer