Import Virtuals Machines, PCs and others data in OCS inventory database on iTop

[Florian Vignes]

Hello,
I need some help to import my virtuals machines storing in OCS inventory database on iTop.
I use Data collector for OCS inventory plugin to try to import my data.

When, I try to run the PHP script, I have an error message.

I will join my file conf :

<!-- iTop Application -->
    <default_org_id>Demo</default_org_id>
    <default_status>production</default_status>
    <PCCollection>yes</PCCollection>
    <ServerCollection>yes</ServerCollection>
    <VMCollection>yes</VMCollection>

    <!-- TeemIp options -->
    <collect_ips>yes</collect_ips>
    <default_ip_status>allocated</default_ip_status>
    <manage_ipv6>no</manage_ipv6>
    <default_view_name></default_view_name>

    <!-- Data synchro parameters -->
    <json_placeholders type="hash">
        <prefix>OCSng</prefix>
        <full_load_interval>604800</full_load_interval><!-- 7 days (in seconds): 7*24*60*60 -->
        <synchro_status>production</synchro_status>
        <delete_policy>update</delete_policy>l
        <delete_policy_update>status:obsolete</delete_policy_update>
        <delete_policy_retention>0</delete_policy_retention>
    </json_placeholders>

    <!-- Brand -->
    <OCSBrandCollector_query>SELECT DISTINCT SMANUFACTURER as primary_key,
        SMANUFACTURER as name
        FROM bios</OCSBrandCollector_query>

    <!-- OSFamily -->
    <OCSOSFamilyCollector_query>SELECT DISTINCT OSNAME as primary_key,
        OSNAME as name
        FROM hardware</OCSOSFamilyCollector_query>

    <!-- OSVersion -->
    <OCSOSVersionCollector_query>SELECT DISTINCT CONCAT(OSNAME,OSVERSION) as primary_key,
        OSNAME as osfamily_id,
        OSVERSION as name
        FROM hardware</OCSOSVersionCollector_query>

    <!-- Model for Servers -->
    <OCSServerModelCollector_query>SELECT DISTINCT CONCAT(SMANUFACTURER,SMODEL) AS primary_key,
        SMANUFACTURER as brand_id,
        SMODEL as name,
        'Server' As type
        FROM bios
            WHERE TYPE COLLATE utf8_general_ci NOT IN ('Notebook','Laptop')
            AND SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'</OCSServerModelCollector_query>

    <!-- Model for PC -->
    <OCSPCModelCollector_query>SELECT DISTINCT CONCAT(SMANUFACTURER,SMODEL) AS primary_key,
        SMANUFACTURER as brand_id,
        SMODEL as name,
        'PC' As type
        FROM bios
            WHERE TYPE COLLATE utf8_general_ci IN ('Notebook','Laptop')
            AND SMANUFACTURER NOT LIKE 'VMware%'</OCSPCModelCollector_query>

    <!-- PC - TeemIp NOT installed -->
    <OCSPCCollector_query>SELECT b.SSN as primary_key,
        h.ID as tickets_list,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        b.SMANUFACTURER as brand_id,
        b.SMODEL as model_id,
        b.SSN as serialnumber,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.TYPE COLLATE utf8_general_ci IN ('Notebook','Laptop')
            AND b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'</OCSPCCollector_query>

    <!-- PhysicalInterface for PC - TeemIp NOT installed -->
    <OCSPCPhysicalInterfaceCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        n.MACADDR as macaddress,
        n.IPADDRESS as ipaddress,
        n.IPMASK as ipmask,
        n.IPGATEWAY as ipgateway,
        h.NAME as connectableci_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'
            AND b.TYPE COLLATE utf8_general_ci IN ('Notebook','Laptop')</OCSPCPhysicalInterfaceCollector_query>

    <!-- PC - TeemIp installed -->
    <OCSPCTeemIpCollector_query>SELECT b.SSN as primary_key,
        h.ID as tickets_list,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        h.IPADDR as ipaddress_id,
        b.SMANUFACTURER as brand_id,
        b.SMODEL as model_id,
        b.SSN as serialnumber,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.TYPE COLLATE utf8_general_ci IN ('Notebook','Laptop')
            AND b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'</OCSPCTeemIpCollector_query>

    <!-- PhysicalInterface for PC - TeemIp installed -->
    <OCSPCPhysicalInterfaceTeemIpCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        n.MACADDR as macaddress,
        h.NAME as connectableci_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'
            AND b.TYPE COLLATE utf8_general_ci IN ('Notebook','Laptop')</OCSPCPhysicalInterfaceTeemIpCollector_query>

    <!-- Server - TeemIp NOT installed -->
    <OCSServerCollector_query>SELECT b.SSN as primary_key,
        h.ID as tickets_list,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        h.IPADDR as managementip,
        b.SMANUFACTURER as brand_id,
        b.SMODEL as model_id,
        b.SSN as serialnumber,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.TYPE COLLATE utf8_general_ci NOT IN ('Notebook','Laptop')
            AND b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'</OCSServerCollector_query>

    <!-- PhysicalInterface for Server - TeemIp NOT installed -->
    <OCSServerPhysicalInterfaceCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        n.MACADDR as macaddress,
        n.IPADDRESS as ipaddress,
        n.IPMASK as ipmask,
        n.IPGATEWAY as ipgateway,
        h.NAME as connectableci_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'
            AND b.TYPE COLLATE utf8_general_ci NOT IN ('Notebook','Laptop')</OCSServerPhysicalInterfaceCollector_query>

    <!-- Server - TeemIp installed -->
    <OCSServerTeemIpCollector_query>SELECT b.SSN as primary_key,
        h.ID as tickets_list,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        h.IPADDR as managementip_id,
        b.SMANUFACTURER as brand_id,
        b.SMODEL as model_id,
        b.SSN as serialnumber,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.TYPE COLLATE utf8_general_ci NOT IN ('Notebook','Laptop')
            AND b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'</OCSServerTeemIpCollector_query>

    <!-- PhysicalInterface for Server - TeemIp installed -->
    <OCSServerPhysicalInterfaceTeemIpCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        MACADDR as macaddress,
        h.NAME as connectableci_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci NOT LIKE 'VMware%'
            AND b.TYPE COLLATE utf8_general_ci NOT IN ('Notebook','Laptop')</OCSServerPhysicalInterfaceTeemIpCollector_query>

    <!-- VirtualMachine - TeemIp NOT installed -->
        <OCSVirtualMachineCollector_query>SELECT h.ID as primary_key,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        h.IPADDR as managementip,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci LIKE 'VMware%'</OCSVirtualMachineCollector_query>

    <!-- LogicalInterface for VirtualMachine - TeemIp NOT installed -->
    <OCSLogicalInterfaceCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        n.MACADDR as macaddress,
        n.IPADDRESS as ipaddress,
        n.IPMASK as ipmask,
        n.IPGATEWAY as ipgateway,
        h.NAME as virtualmachine_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci LIKE 'VMware%'</OCSLogicalInterfaceCollector_query>

    <!-- VirtualMachine - TeemIp installed -->
    <OCSVirtualMachineTeemIpCollector_query>SELECT h.ID as primary_key,
        h.ID as tickets_list,
        h.NAME as name,
        h.OSNAME as osfamily_id,
        h.OSVERSION as osversion_id,
        h.PROCESSORT as cpu,
        h.MEMORY as ram,
        h.IPADDR as managementip_id,
        '$default_status$' as status,
        '$default_org_id$' as org_id
        FROM hardware AS h JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci LIKE 'VMware%'</OCSVirtualMachineTeemIpCollector_query>

    <!-- LogicalInterface for VirtualMachine - TeemIp installed -->
    <OCSLogicalInterfaceTeemIpCollector_query>SELECT n.ID as primary_key,
        n.DESCRIPTION as name,
        IF(n.SPEED REGEXP '^[0-9]+ ', LEFT(n.SPEED, LOCATE(' ', n.SPEED)), n.SPEED) as speed,
        n.MACADDR as macaddress,
        h.NAME as virtualmachine_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE b.SMANUFACTURER COLLATE utf8_general_ci LIKE 'VMware%'</OCSLogicalInterfaceTeemIpCollector_query>

    <!-- IPv4Address - TeemIp installed -->
    <OCSIPv4AddressCollector_query>SELECT n.ID as primary_key,
        '$default_org_id$' as org_id,
        n.IPADDRESS as ip,
        '$default_ip_status$' as status,
        h.DNS as short_name
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE IF(n.IPADDRESS REGEXP '^[:]', false, true)</OCSIPv4AddressCollector_query>

    <!-- IPv4Address - TeemIp with Zone mgmt installed -->
    <OCSIPv4AddressWithZoneCollector_query>SELECT n.ID as primary_key,
        '$default_org_id$' as org_id,
        n.IPADDRESS as ip,
        '$default_ip_status$' as status,
        h.DNS as short_name,
        '$default_view_name$' as view_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
        WHERE IF(n.IPADDRESS REGEXP '^[:]', false, true)</OCSIPv4AddressWithZoneCollector_query>

    <!-- IPv6Address - TeemIp installed -->
    <OCSIPv6AddressCollector_query>SELECT n.ID as primary_key,
        '$default_org_id$' as org_id,
        n.IPADDRESS as ip,
        '$default_ip_status$' as status,
        h.DNS as short_name
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
            WHERE IF(n.IPADDRESS REGEXP '^[:]', true, false)</OCSIPv6AddressCollector_query>

    <!-- IPv6Address - TeemIp with Zone Mgmt installed -->
    <OCSIPv6AddressWithZoneCollector_query>SELECT n.ID as primary_key,
        '$default_org_id$' as org_id,
        n.IPADDRESS as ip,
        '$default_ip_status$' as status,
        h.DNS as short_name,
        '$default_view_name$' as view_id
        FROM networks AS n JOIN hardware AS h ON n.hardware_id=h.id JOIN bios AS b ON h.id=b.hardware_id
        WHERE IF(n.IPADDRESS REGEXP '^[:]', true, false)</OCSIPv6AddressWithZoneCollector_query>

    <!-- lnkIPInterfaceToIPAddress - TeemIp installed -->
    <OCSlnkIPInterfaceToIPAddressCollector_query>SELECT n.ID as primary_key,
        n.MACADDR as ipinterface_id,
        n.IPADDRESS as ipaddress_id
        FROM networks AS n</OCSlnkIPInterfaceToIPAddressCollector_query>

Console infos:

root@CopieTestServeriTop:/var/www/html/iTopC/extensions/ocsng-data-collector# php exec.php
[2021-05-04 10:16:05] [Info] TeemIp is NOT installed on remote iTop server
[2021-05-04 10:16:06] [Info] Contact to notify: 'Florian Vignes' <f.vignes@oieau.fr> (2).
[2021-05-04 10:16:07] [Info] Synchro User: 'admin' <my.email@foo.org> (1).
[2021-05-04 10:16:07] [Info] Ok, the Synchro Data Source 'OCSng:Brand' exists in iTop and is up to date
[2021-05-04 10:16:07] [Info] Ok, the Synchro Data Source 'OCSng:OSFamily' exists in iTop and is up to date
[2021-05-04 10:16:07] [Info] Ok, the Synchro Data Source 'OCSng:OSVersion' exists in iTop and is up to date
[2021-05-04 10:16:08] [Info] Ok, the Synchro Data Source 'OCSng:PCModel' exists in iTop and is up to date
[2021-05-04 10:16:08] [Info] Ok, the Synchro Data Source 'OCSng:PC' exists in iTop and is up to date
[2021-05-04 10:16:08] [Info] Ok, the Synchro Data Source 'OCSng:PCPhysicalInterface' exists in iTop and is up to date
[2021-05-04 10:16:09] [Info] Ok, the Synchro Data Source 'OCSng:ServerModel' exists in iTop and is up to date
[2021-05-04 10:16:09] [Info] Ok, the Synchro Data Source 'OCSng:Server' exists in iTop and is up to date
[2021-05-04 10:16:10] [Info] Ok, the Synchro Data Source 'OCSng:ServerPhysicalInterface' exists in iTop and is up to date
[2021-05-04 10:16:10] [Info] Ok, the Synchro Data Source 'OCSng:VirtualMachine' exists in iTop and is up to date
[2021-05-04 10:16:10] [Info] Ok, the Synchro Data Source 'OCSng:LogicalInterface' exists in iTop and is up to date
[2021-05-04 10:16:10] [Info] OCSBrandCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] Writing to file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSBrandCollector-1.csv'.
[2021-05-04 10:16:10] [Info] OCSBrandCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSOSFamilyCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] Writing to file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSOSFamilyCollector-1.csv'.
[2021-05-04 10:16:10] [Info] OCSOSFamilyCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSOSVersionCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] Writing to file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSOSVersionCollector-1.csv'.
[2021-05-04 10:16:10] [Info] OCSOSVersionCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSPCModelCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSPCModelCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSPCCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSPCCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSPCPhysicalInterfaceCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSPCPhysicalInterfaceCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSServerModelCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSServerModelCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSServerCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSServerCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSServerPhysicalInterfaceCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] OCSServerPhysicalInterfaceCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSVirtualMachineCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] Writing to file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSVirtualMachineCollector.raw-1.csv'.
[2021-05-04 10:16:10] [Info] OCSVirtualMachineCollector end of data collection.
[2021-05-04 10:16:10] [Info] OCSLogicalInterfaceCollector beginning of data collection...
[2021-05-04 10:16:10] [Info] Writing to file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSLogicalInterfaceCollector-1.csv'.
[2021-05-04 10:16:10] [Info] OCSLogicalInterfaceCollector end of data collection.
[2021-05-04 10:16:10] [Info] Uploading data file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSBrandCollector-1.csv'
[2021-05-04 10:16:10] [Info] Starting synchronization of the data source 'OCSng:Brand'...
[2021-05-04 10:16:11] [Info] Synchronization of data source 'OCSng:Brand' succeeded.
[2021-05-04 10:16:11] [Info] Uploading data file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSOSFamilyCollector-1.csv'
[2021-05-04 10:16:11] [Info] Starting synchronization of the data source 'OCSng:OSFamily'...
[2021-05-04 10:16:11] [Info] Synchronization of data source 'OCSng:OSFamily' succeeded.
[2021-05-04 10:16:11] [Info] Uploading data file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSOSVersionCollector-1.csv'
[2021-05-04 10:16:11] [Info] Starting synchronization of the data source 'OCSng:OSVersion'...
[2021-05-04 10:16:11] [Info] Synchronization of data source 'OCSng:OSVersion' succeeded.
[2021-05-04 10:16:11] [Info] Starting synchronization of the data source 'OCSng:PCModel'...
[2021-05-04 10:16:11] [Info] Synchronization of data source 'OCSng:PCModel' succeeded.
PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/iTopC/extensions/ocsng-data-collector/core/lookuptable.class.inc.php on line 60
[2021-05-04 10:16:12] [Info] Starting synchronization of the data source 'OCSng:PC'...
[2021-05-04 10:16:12] [Info] Synchronization of data source 'OCSng:PC' succeeded.
[2021-05-04 10:16:12] [Info] Starting synchronization of the data source 'OCSng:PCPhysicalInterface'...
[2021-05-04 10:16:12] [Info] Synchronization of data source 'OCSng:PCPhysicalInterface' succeeded.
[2021-05-04 10:16:12] [Info] Starting synchronization of the data source 'OCSng:ServerModel'...
[2021-05-04 10:16:12] [Info] Synchronization of data source 'OCSng:ServerModel' succeeded.
PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/iTopC/extensions/ocsng-data-collector/core/lookuptable.class.inc.php on line 60
[2021-05-04 10:16:12] [Info] Starting synchronization of the data source 'OCSng:Server'...
[2021-05-04 10:16:13] [Info] Synchronization of data source 'OCSng:Server' succeeded.
[2021-05-04 10:16:13] [Info] Starting synchronization of the data source 'OCSng:ServerPhysicalInterface'...
[2021-05-04 10:16:13] [Info] Synchronization of data source 'OCSng:ServerPhysicalInterface' succeeded.
[2021-05-04 10:16:13] [Info] Processing '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSVirtualMachineCollector.raw-1.csv'...
[2021-05-04 10:16:13] [Info] End of processing of '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSVirtualMachineCollector.raw-1.csv'...
[2021-05-04 10:16:13] [Info] Uploading data file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSVirtualMachineCollector-1.csv'
[2021-05-04 10:16:13] [Info] Starting synchronization of the data source 'OCSng:VirtualMachine'...
[2021-05-04 10:16:13] [Error] Synchronization of data source 'OCSng:VirtualMachine' answered: Objects creation errors: 1
[2021-05-04 10:16:13] [Info] Uploading data file '/var/www/html/iTopC/extensions/ocsng-data-collector//data/OCSLogicalInterfaceCollector-1.csv'
[2021-05-04 10:16:13] [Info] Starting synchronization of the data source 'OCSng:LogicalInterface'...
[2021-05-04 10:16:14] [Error] Synchronization of data source 'OCSng:LogicalInterface' answered: Objects reconciliation errors: 1

And errors messages :

'OCSng:VirtualMachine' answered: Objects creation errors: 1
'OCSng:LogicalInterface' answered: Objects reconciliation errors: 1

Any ideas?
thank you in advance for your help

[berserk<3]

Hello, I have exactly the same errors ( VirtualMachine / LogicalInterface) . The data from OCS is comming but only in the "configuration typology" menu...