Improve Software.pm

[charleneauger]

Important notes

Please, don't mistake OCSInventory-server with OCSInventory-ocsreports :

• OCSInventory-server : Communication server that manage the inventory
• OCSInventory-ocsreports : Web interface of OCS Inventory

General informations

Operating system :

Server informations

Perl version : Mysql / Mariadb / Percona version :

Status

READY

Description

• Remove delete all software function
• Check if software is already in db : if true : skip insert and save the id if false : insert and save the id
• Check if software category is 0 if true : skip if false : check if software already in this category in db else delete/insert
• Delete all software not in id saved with the hardware_id

[github-actions[bot]]

🦙 MegaLinter status: ✅ SUCCESS

Descriptor	Linter	Files	Fixed	Errors	Elapsed time
✅ ACTION	actionlint	3		0	0.03s
✅ BASH	bash-exec	3		0	0.01s
✅ BASH	shfmt	3	0	0	0.02s
✅ JSON	eslint-plugin-jsonc	3	0	0	1.12s
✅ JSON	jsonlint	3		0	0.2s
✅ JSON	prettier	3	0	0	0.47s
✅ MARKDOWN	markdownlint	5	0	0	0.36s
✅ MARKDOWN	markdown-link-check	5		0	7.82s
✅ MARKDOWN	markdown-table-formatter	5	0	0	0.25s
✅ OPENAPI	spectral	1		0	1.69s
✅ POWERSHELL	powershell	2		0	2.49s
✅ REPOSITORY	dustilock	yes		no	0.01s
✅ REPOSITORY	gitleaks	yes		no	6.53s
✅ REPOSITORY	git_diff	yes		no	0.01s
✅ REPOSITORY	secretlint	yes		no	0.83s
✅ REPOSITORY	syft	yes		no	0.46s
✅ REPOSITORY	trivy	yes		no	2.42s
✅ YAML	prettier	4	0	0	0.7s
✅ YAML	yamllint	4		0	0.29s

See errors details in artifact MegaLinter reports on CI Job page

You could have the same capabilities but better runtime performances if you use a MegaLinter flavor:

• oxsecurity/megalinter/flavors/dotnet@v6 (56 linters)

_MegaLinter is graciously provided by _

[jjmg84]

Hi, i see new errors DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl/5.30.0/Apache/Ocsinventory/Server/Inventory/Software.pm line 61.

and in mysql i see too many queryes

This generate too many concurrent connections (1517) and get mysql server slow.

is it normal?

[charleneauger]

Hi @jjmg84 ,

Thanks for your feedback !

• Can you tell me the number of agents you have as well as the average number of simultaneous connections on the /ocsinventory ?
• Can you provide me with the specs of your OCS server and MySQL server (if it's a split infra) ?

Best regards, Charlene

[jjmg84]

Hi, i have +- 17000 computers on OCS, 8 core 64gb ocs server machine, 12core, 16gb mariadb server machine

I think i have troubleshoted the problem, i saw that are many conections that are launching a query to a network table:

SELECT HARDWARE_ID,DESCRIPTION,MACADDR FROM networks WHERE MACADDR=? AND HARDWARE_ID<>?

All of them with a mac of 00:00:00:00:00:00 , i searched and this come from the duplicate.pm, i commented out this:

...and one MAC of this machine

for(@{$result->{CONTENT}->{NETWORKS}}){

$request = $dbh->prepare('SELECT HARDWARE_ID,DESCRIPTION,MACADDR FROM networks WHERE MACADDR=? AND HARDWARE_ID<>?');

$request->execute($_->{MACADDR}, $DeviceID);

while($row = $request->fetchrow_hashref()){

if(!&_already_in_array($row->{'MACADDR'}, \@bad_mac)){

$exist->{$row->{'HARDWARE_ID'}}->{'MACADDRESS'}++;

}

}

}

And now ocs work like a charm, we are observing but now the cpu load on mariadb has lowed and all seems to run fine

[charleneauger]

@jjmg84,

I merged the next PR #395 this morning regarding the macaddress :-)

Regards Charlene

[jjmg84]

@jjmg84,

I merged the next PR #395 this morning regarding the macaddress :-)

Regards Charlene

Wow, this is efficiency :) Thanks !!