Forum GLPI-Project

falco2001

Member

Registered: 2016-02-11

Posts: 256

[GLPI 9.5.6] Suppression logicels avec 0 installation

Bonjour,
Avec la requête ci-dessous je peux supprimer les id des imprimantes sans installation.
Mais vu le nombre de jointure et comme les tables n'ont pas delete on cascade , pouvez-vous me dire si c'est cohérent de faire ceci.
A mon sens il va avoir des id orphelins dans les tables de jointures ?
Avez-vous une solution ?
Bien sûr on peut la faire par GLPI via l'interface mais je voudrais le faire en automatique en sql ... Merci

CREATE table AA_LOGICIELS_ZERO_INSTALL 
SELECT DISTINCT
    `glpi_softwares`.`id` AS id,
    `glpi_softwares`.`name` AS `ITEM_Software_1`,
    
     COUNT(DISTINCT `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92`.`id`) AS `ITEM_Software_72`

FROM
    `glpi_softwares`
        LEFT JOIN
    `glpi_entities` ON (`glpi_softwares`.`entities_id` = `glpi_entities`.`id`)
        LEFT JOIN
    `glpi_manufacturers` ON (`glpi_softwares`.`manufacturers_id` = `glpi_manufacturers`.`id`)
        LEFT JOIN
    `glpi_softwareversions` ON (`glpi_softwares`.`id` = `glpi_softwareversions`.`softwares_id`)
        LEFT JOIN
    `glpi_softwarelicenses` AS `glpi_softwarelicenses_daf59b6b5fae84097745ab089c081619` ON (`glpi_softwares`.`id` = `glpi_softwarelicenses_daf59b6b5fae84097745ab089c081619`.`softwares_id`
        AND `glpi_softwarelicenses_daf59b6b5fae84097745ab089c081619`.`is_template` = 0
        AND (`glpi_softwarelicenses_daf59b6b5fae84097745ab089c081619`.`expire` IS NULL
        OR `glpi_softwarelicenses_daf59b6b5fae84097745ab089c081619`.`expire` > NOW()))
        LEFT JOIN
    `glpi_operatingsystems` AS `glpi_operatingsystems_0a35c270152be19b5c8a485502badcd7` ON (`glpi_softwareversions`.`operatingsystems_id` = `glpi_operatingsystems_0a35c270152be19b5c8a485502badcd7`.`id`)
        LEFT JOIN
    `glpi_items_softwareversions` AS `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92` ON (`glpi_softwareversions`.`id` = `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92`.`softwareversions_id`
        AND `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92`.`is_deleted_item` = 0
        AND `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92`.`is_deleted` = 0
        AND `glpi_items_softwareversions_65f50a68dce7dc96c5d9edcbb29b8e92`.`is_template_item` = 0)
        LEFT JOIN
    `glpi_locations` ON (`glpi_softwares`.`locations_id` = `glpi_locations`.`id`)
WHERE
    `glpi_softwares`.`is_deleted` = 0
        AND `glpi_softwares`.`is_template` = 0
GROUP BY `glpi_softwares`.`id`
HAVING (`ITEM_Software_72` = 0)
ORDER BY `id`;
DELETE from glpi_softwares where id in (select id from AA_LOGICIELS_ZERO_INSTALL where id=528499);
DROP table IF Exists   AA_LOGICIELS_ZERO_INSTALL 

Last edited by falco2001 (2022-10-05 09:36:43)