Common-SQL-Requests¶

GLPI 0.80¶

Different physical machines jumping on the same entry in the DB¶

SELECT
  distinct l1.items_id
FROM
  glpi_logs AS l1,
  glpi_logs AS l2,
  glpi_logs AS l3
WHERE
    l1.id_search_option=1
  AND
     l2.id_search_option=1
  AND
     l3.id_search_option=1
  AND
     l1.itemtype='Computer'
  AND
     l2.itemtype='Computer'
  AND
     l3.itemtype='Computer'
  AND
     l1.items_id=l2.items_id
  AND
     l2.items_id=l3.items_id
  AND
    l1.new_value<>l2.new_value
  AND
    l2.new_value<>l3.new_value
  AND
    (
    l1.user_name='massocsimport'
     OR
    l1.user_name='Plugin_FusionInventory'
     OR
    l1.user_name=''
    )
  AND
    (
    l2.user_name='massocsimport'
     OR
    l2.user_name='Plugin_FusionInventory'
     OR
    l2.user_name=''
    )
  AND
    (
    l3.user_name='massocsimport'
     OR
    l3.user_name='Plugin_FusionInventory'
     OR
    l3.user_name=''
    );

Serial changes on the same machine¶

SELECT
  distinct l1.items_id
FROM
  glpi_logs AS l1,
  glpi_logs AS l2,
  glpi_logs AS l3
WHERE
    l1.id_search_option=5
  AND
     l2.id_search_option=5
  AND
     l3.id_search_option=5
  AND
     l1.itemtype='Computer'
  AND
     l2.itemtype='Computer'
  AND
     l3.itemtype='Computer'
  AND
     l1.items_id=l2.items_id
  AND
     l2.items_id=l3.items_id
  AND
    l1.new_value<>l2.new_value
  AND
    l2.new_value<>l3.new_value
  AND
    (
    l1.user_name='massocsimport'
     OR
    l1.user_name='Plugin_FusionInventory'
     OR
    l1.user_name=''
    )
  AND
    (
    l2.user_name='massocsimport'
     OR
    l2.user_name='Plugin_FusionInventory'
     OR
    l2.user_name=''
    )
  AND
    (
    l3.user_name='massocsimport'
     OR
    l3.user_name='Plugin_FusionInventory'
     OR
    l3.user_name=''
    );

Machines where FusionInvenroy changed the serial number
(replace $date by the date you want)¶

SELECT items_id, old_value, new_value FROM `glpi_logs`
          WHERE itemtype = 'Computer' AND id_search_option = '5'
            AND date_mod >= '$date'
               AND old_value != new_value
                  AND old_value != ''
                     AND new_value != ''
                        AND user_name = 'Plugin_FusionInventory'
                           ORDER BY date_mod ASC

Purge duplicated network interfaces generated by massocsimport (?) [SLOW!]¶

DELETE FROM glpi_computers_devicenetworkcards
  WHERE id
    IN (
      SELECT id
        FROM (
          SELECT id
            FROM glpi_computers_devicenetworkcards
              WHERE id NOT
                IN (SELECT glpi_computers_devicenetworkcards.id
                  FROM glpi_computers_devicenetworkcards
                    GROUP BY computers_id, devicenetworkcards_id, specificity)) AS TMPTABLE);