SQLCollector and JSONCollector

[Soji]

Hi guys,

I tried to synchronize the organization name from my database to itop.

I tried these 2 methods: SQL and JSON Collector but I always get the same error message for these 2 cases:
[Error] Empty Synchro Data Source definition for the collector 'OrgSQLCollector'
[Error] Exception: Cannot create Collector (empty JSON definition)

I followed this procedure:
https://www.itophub.io/wiki/page?id=extensions%3Aitop-data-collector-base#creating_the_json_definition_file

Here are my config files:
params.local.xml

<parameters>
<itop_url>http://localhost/</itop_url>
<itop_login>admin</itop_login>
<itop_password>password</itop_password>
<console_log_level>9</console_log_level>
<sql_engine>mysql</sql_engine>
<sql_host>192.168.1.1</sql_host>
<sql_database>company</sql_database>
<sql_login>u_itop</sql_login>
<sql_password>password</sql_password>
<orgsqlcollector_query>SELECT id as primary_key, name, parent_id FROM company</orgsqlcollector_query>
</parameters>

main.php
<?php
require_once(APPROOT.'collectors/orgcollector.class.inc.php');
Orchestrator::AddCollector(1, 'OrgSQLCollector');

orgcollector.class.inc.php
<?php
class OrgSQLCollector extends SQLCollector
{
}

I generated the json file with this command line after I created my data synchro on itop:
php toolkit/dump_tasks.php --task-name="OrgSQLCollector" > collectors/orgcollector.json

Did I forget a step?

Thanks for your help.

Regards,

[Soji]

I just try data synchro with CSVCollector but I got the same error message...

php exec.php --config_file=conf/params.local.xml --console_log_level=9

PHP Notice: Undefined variable: index in /root/Collector/mycollector/collectors/main.php on line 3
[2020-11-18 22:04:38] [Debug] OK, the required PHP version to run this application is 5.6.0. The current PHP version is 7.3.22.
[2020-11-18 22:04:38] [Debug] OK, the required extension 'simplexml' is installed (current version: 7.3.22 >= 0.1).
[2020-11-18 22:04:38] [Debug] OK, the required extension 'dom' is installed (current version: 20031129 >= 1.0).
[2020-11-18 22:04:38] [Debug] The following configuration files were loaded (in this order):

> 1. /root/Collector/mycollector/conf/params.distrib.xml
> 2. conf/params.local.xml

The resulting configuration is:

<parameters>
<itop_url>http://localhost/</itop_url>
<itop_login>admin</itop_login>
<itop_password>pass</itop_password>
<console_log_level>9</console_log_level>
<console_log_dateformat>[Y-m-d H:i:s]</console_log_dateformat>
<syslog_log_level>-1</syslog_log_level>
<data_path>%APPROOT%/data</data_path>
<max_chunk_size>1000</max_chunk_size>
<itop_synchro_timeout>600</itop_synchro_timeout>
<stop_on_synchro_error>no</stop_on_synchro_error>
<curl_options>
<curlopt_ssl_verifyhost>0</curlopt_ssl_verifyhost>
<curlopt_ssl_verifypeer>1</curlopt_ssl_verifypeer>
</curl_options>
<contact_to_notify>toto@toto.com</contact_to_notify>
<synchro_user>toto</synchro_user>
<json_placeholders>
<prefix>CSVModel</prefix>
<database_table_name>synchro_data_model_14</database_table_name>
<synchro_status>Implementation</synchro_status>
</json_placeholders>
<modelcollector>
<csv_file>data/Model.csv</csv_file>
<fields>
<primary_key>id</primary_key>
<name>name</name>
<brand_id>brand_id</brand_id>
<type>device type</type>
</fields>
<defaults>
<status>Active</status>
</defaults>
<ignored_columns type="array">
</ignored_columns></modelcollector>
</parameters>

[2020-11-18 22:04:38] [Error] Empty Synchro Data Source definition for the collector 'ModelCollector'
[2020-11-18 22:04:38] [Error] Exception: Cannot create Collector (empty JSON definition)

[Soji]

Hello, it's me again.

I succedeed to get the data synchro working with SQLCollector.

But I have a new problem with names that have an accent.
The db source and db target (itop) have a same charset UTF-8.

The problem is in the generation of the CSV file because it replaces the accents by special characters.
If i understand well, SQLCollector executes a SQL query, generates a CSV file and uploads it to the "synchro_data_organization_1" table (in my case) and finally copies it to the organization table. Is that right?

srvitop#cat data/myCollector-1.csv
primary_key;name;parent_id;code;status
1001;"Company S?rl";;1001;0

I got this error message:

`Import error 'Failed to issue SQL query: query = INSERT INTO synchro_data_organization_1` (`primary_key`, `name`, `parent_id`, `code`) VALUES ('1001', 'Company S?rl', '1', '1001'), mysql_errno = 1366, mysql_error = Incorrect string value: '\xE9l\xE9r\xE9s...' for column `itop`.`synchro_data_organization_1`.`name` at row 1' (continuing)...``

On the core/collector.class.inc.php, I changed

        protected function GetCharset()
        {
                return 'UTF-8';
        }

    by

protected function GetCharset()
{
return 'utf8';
}

After that, the sync works but it imports with this name "Company Srl" (without ? character)

How can I forced the csv file to use utf8?
I changed this parameter on config file:
'csv_file_default_charset' => 'utf8',

But anything change...

Any idea?

[odain]

Hello Soji,

how did you generate the CSV file with special characters?

did you try the CSV collector and provided the encoding parameter?
https://www.itophub.io/wiki/page?id=extensions%3Aitop-data-collector-base#csv_collector

for SQL /Json collectors can you please provide your setup to let us check?

Best regards
Olivier

[Soji]

Hello Odain,

Thanks for your answer.

The CSV file will generate by Collector class (core/collector.class.inc.php).

Here my configs:

collectors/myCollector.class.inc.php

<?php
class myCollector extends SQLCollector
{
}

collectors/main.php

<?php
require_once(APPROOT.'collectors/myCollector.class.inc.php');
Orchestrator::AddCollector($index++,'myCollector');

conf/params.distrib.xml

<?xml version="1.0" encoding="utf8"?>
<!-- Default values for parameters. Do NOT alter this file, use params.local.xml instead -->
<parameters>
  <itop_url>http://localhost/</itop_url>
  <itop_login>admin</itop_login>
  <itop_password>mypassword</itop_password>
  <contact_to_notify>soji@toto.com</contact_to_notify>
  <synchro_user>admin</synchro_user>
  <json_placeholders type="hash">
    <full_load_interval>60</full_load_interval>
  </json_placeholders>
  <sql_host>mydbserver</sql_host>
  <sql_database>mydb</sql_database>
  <sql_login>u_itop</sql_login>
  <sql_password>mypassword</sql_password>
  <myCollector_query>SELECT id as primary_key, name, partner_id as parent_id, id as code, status FROM mydb.customers</myCollector_query>
  <defaults>
        <status>active</status>
  </defaults>
  <myCollector_ignored_attributes type="array">
    <attribute>deliverymodel_id</attribute>
  </myCollector_ignored_attributes>

data/myCollector-1.csv

primary_key;name;parent_id;code;status
1000;"Company S?rl";;1000;0

mydbserver and db itop use the same charset.

MariaDB [(none)]> SELECT default_character_set_name, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = "itop";
+----------------------------+------------------------+
| default_character_set_name | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

MariaDB [(none)]> SELECT default_character_set_name, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA S WHERE schema_name = "mydb";
+----------------------------+------------------------+
| default_character_set_name | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

[Soji]

Problem solved with mysqlcollector.