Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Software table is missing in the Database #451

Closed
kiwib1b opened this issue Jan 24, 2024 · 2 comments
Closed

Software table is missing in the Database #451

kiwib1b opened this issue Jan 24, 2024 · 2 comments

Comments

@kiwib1b
Copy link

kiwib1b commented Jan 24, 2024 •
edited

General informations

Operating system : Red Hat Enterprise Linux release 9.3 (Plow)

Server informations

Perl version : perl 5, version 32, subversion 1 (v5.32.1) built for x86_64-linux-thread-multi
Mysql / Mariadb / Percona version : mariadb-server-10.5.22-1.el9_2.x86_64

OCS Inventory informations

Ocs server version : ocsinventory-server-2.12.1-1

Problem's description

I have a recently installed OCS server but agent dont works correctly. I am using both the official agent and fusion inventory.

With fusion inventory it gives me the following error:

Tue Jan 23 12:39:28 2024;2002695;318;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;session;missing
Tue Jan 23 12:39:28 2024;2002695;114;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;inventory;no_session
Tue Jan 23 12:39:28 2024;2002695;104;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;inventory;incoming
Tue Jan 23 12:39:28 2024;2002695;528;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;accountinfos;missing
Tue Jan 23 12:39:28 2024;2002695;1001;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;ipdiscover;checking if is enabled
Tue Jan 23 12:39:28 2024;2002695;1001;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;ipdiscover;checking if parameters are OK
Tue Jan 23 12:39:28 2024;2002695;1001;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;ipdiscover;processing
Tue Jan 23 12:39:28 2024;2002695;1001;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;ipdiscover;checking if ipdiscover group capabilities are enabled
Tue Jan 23 12:39:28 2024;2002695;1001;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;ipdiscover;checking user agent
Tue Jan 23 12:39:28 2024;2002695;301;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;duplicate;error to delete hardware_id from software
Tue Jan 23 12:39:28 2024;2002695;517;vsphere-gj-2024-01-23-12-38-39;10.228.7.12;FusionInventory-Injector;duplicate;replacing_error

It duplicates my entries because it is unable to delete duplicates since the software table is missing

With the OCS agent (OCS-NG_WINDOWS_AGENT_v2.10.1.0) on a Windows server it does not even register:

Tue Jan 23 15:36:44 2024;2003917;100;HV-PV-DU000044-2024-01-23-14-18-03;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;prolog;accepted
Tue Jan 23 15:36:44 2024;2003917;311;HV-PV-DU000044-2024-01-23-14-18-03;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;session;started
Tue Jan 23 15:37:06 2024;2003917;319;HV-PV-DU000044-2024-01-23-14-18-03;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;session;found
Tue Jan 23 15:37:06 2024;2003917;104;HV-PV-DU000044-2024-01-23-14-18-03;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;inventory;incoming
Tue Jan 23 15:37:06 2024;2003917;515;HV-PV-DU000044-2024-01-23-14-18-03;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;end;error
Tue Jan 23 15:38:31 2024;2003917;103;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;prolog;new_deviceid
Tue Jan 23 15:38:31 2024;2003917;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if is enabled
Tue Jan 23 15:38:31 2024;2003917;103;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;snmp;error: agent must communicate using https to be able to get SNMP communities (ony affects OCS unix agent) !!

if I disable it from collecting software information it registers but it duplicates itself and does not delete like fusion inventory:

Tue Jan 23 15:39:06 2024;2003700;100;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;prolog;accepted
Tue Jan 23 15:39:06 2024;2003700;311;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;session;started
Tue Jan 23 15:39:19 2024;2003702;319;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;session;found
Tue Jan 23 15:39:19 2024;2003702;104;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;inventory;incoming
Tue Jan 23 15:39:19 2024;2003702;113;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;inventory;u:drives
Tue Jan 23 15:39:19 2024;2003702;113;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;inventory;u:bios
Tue Jan 23 15:39:19 2024;2003702;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if is enabled
Tue Jan 23 15:39:19 2024;2003702;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if parameters are OK
Tue Jan 23 15:39:19 2024;2003702;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;processing
Tue Jan 23 15:39:19 2024;2003702;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if ipdiscover group capabilities are enabled
Tue Jan 23 15:39:19 2024;2003702;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking user agent
Tue Jan 23 15:39:19 2024;2003702;320;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;session;end
Tue Jan 23 15:39:19 2024;2003702;301;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;duplicate;error to delete hardware_id from software
Tue Jan 23 15:39:19 2024;2003702;517;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;duplicate;replacing_error
Tue Jan 23 15:39:19 2024;2003702;101;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;inventory;transmitted
Tue Jan 23 15:44:09 2024;2003917;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if is enabled
Tue Jan 23 15:44:09 2024;2003917;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if parameters are OK
Tue Jan 23 15:44:09 2024;2003917;1001;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;ipdiscover;checking if computer is able to be elected
Tue Jan 23 15:44:09 2024;2003917;103;HV-PV-DU000044-2024-01-23-15-38-31;10.228.13.208;OCS-NG_WINDOWS_AGENT_v2.10.1.0;snmp;error: agent must communicate using https to be able to get SNMP communities (ony affects OCS unix agent) !!

The problem with everything is that the software table is missing from the database, which generates duplicates and nothing is recorded if I activate it to collect the software information. I don't know why when I installed this table it wasn't created.

MariaDB [ocsinventory]> show tables;
+---------------------------+
| Tables_in_ocsinventory    |
+---------------------------+
| accesslog                 |
| accountinfo               |
| accountinfo_config        |
| archive                   |
| assets_categories         |
| auth_attempt              |
| batteries                 |
| bios                      |
| blacklist_macaddresses    |
| blacklist_serials         |
| blacklist_subnet          |
| config                    |
| config_ldap               |
| conntrack                 |
| controllers               |
| cpus                      |
| cve_search                |
| cve_search_computer       |
| cve_search_correspondance |
| cve_search_history        |
| deleted_equiv             |
| deploy                    |
| devices                   |
| devicetype                |
| dico_ignored              |
| dico_soft                 |
| download_affect_rules     |
| download_available        |
| download_enable           |
| download_history          |
| download_servers          |
| downloadwk_conf_values    |
| downloadwk_fields         |
| downloadwk_history        |
| downloadwk_pack           |
| downloadwk_statut_request |
| downloadwk_tab_values     |
| drives                    |
| engine_mutex              |
| engine_persistent         |
| extensions                |
| files                     |
| groups                    |
| groups_cache              |
| hardware                  |
| hardware_osname_cache     |
| history                   |
| inputs                    |
| itmgmt_comments           |
| javainfo                  |
| journallog                |
| languages                 |
| layouts                   |
| local_groups              |
| local_users               |
| locks                     |
| memories                  |
| modems                    |
| monitors                  |
| netmap                    |
| network_devices           |
| networks                  |
| notification              |
| notification_config       |
| operators                 |
| ports                     |
| printers                  |
| prolog_conntrack          |
| regconfig                 |
| registry                  |
| registry_name_cache       |
| registry_regvalue_cache   |
| reports_notifications     |
| repository                |
| saas                      |
| saas_exp                  |
| save_query                |
| schedule_wol              |
| sim                       |
| slots                     |
| snmp_accountinfo          |
| snmp_communities          |
| snmp_configs              |
| snmp_default              |
| snmp_labels               |
| snmp_mibs                 |
| snmp_types                |
| snmp_types_conditions     |
| software_categories       |
| software_categories_link  |
| software_category_exp     |
| software_link             |
| software_name             |
| software_publisher        |
| software_version          |
| softwares_name_cache      |
| sounds                    |
| ssl_store                 |
| storages                  |
| subnet                    |
| tags                      |
| temp_files                |
| usbdevices                |
| videos                    |
| virtualmachines           |
+---------------------------+
105 rows in set (0.000 sec)
@kiwib1b kiwib1b changed the title I am missing tables in the DB Software table is missing in the Database Jan 24, 2024
@kiwib1b
Copy link
Author

kiwib1b commented Jan 25, 2024

To solve the problem we have reviewed the creation of the Database and launched the creation of the missing software table with the following result:

MariaDB [ocsinventory]> CREATE TABLE `software` (
    ->   `ID` bigint NOT NULL AUTO_INCREMENT,
    ->   `HARDWARE_ID` int NOT NULL,
    ->   `NAME_ID` int NOT NULL,
    ->   `PUBLISHER_ID` int NOT NULL,
    ->   `VERSION_ID` int NOT NULL,
    ->   `FOLDER` text,
    ->   `COMMENTS` text,
    ->   `FILENAME` varchar(255) DEFAULT NULL,
    ->   `FILESIZE` int DEFAULT '0',
    ->   `SOURCE` int DEFAULT NULL,
    ->   `GUID` varchar(255) DEFAULT NULL,
    ->   `LANGUAGE` varchar(255) DEFAULT NULL,
    ->   `INSTALLDATE` datetime DEFAULT NULL,
    ->   `BITSWIDTH` int DEFAULT NULL,
    ->   `ARCHITECTURE` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `HARDWARE_ID` (`HARDWARE_ID`),
    ->   KEY `NAME_ID` (`NAME_ID`),
    ->   KEY `PUBLISHER_ID` (`PUBLISHER_ID`),
    ->   KEY `VERSION_ID` (`VERSION_ID`)
    ->   KEY `HARDWARE_ID_2` (`HARDWARE_ID`, `NAME_ID`, `VERSION_ID`) USING BTREE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'KEY `HARDWARE_ID_2` (`HARDWARE_ID`, `NAME_ID`, `VERSION_ID`) USING BTREE
) EN...' at line 22

It seems that the problem is here it fails to create the table during installation

We create the table following what is included in the installation script except for the creation of the KEYs, which we do one by one and it is done correctly

This solves all the errors we had in the agent registration

@Lea9250
Copy link
Contributor

Lea9250 commented Apr 19, 2024

Hi @kiwib1b,

This has been fixed with PR OCSInventory-NG/OCSInventory-ocsreports#1590, which was merged into our master branch on the ocsreports repository. Thank you for reporting the issue.

Regards,
Léa

@Lea9250 Lea9250 closed this as completed Apr 19, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@kiwib1b @Lea9250