NexTool Solutions

A vendor audit can cost a fortune, but what breaks license management in GLPI is rarely the price of the license - it is the install the system counts against the wrong software. On GLPI estates we sustain for clients, the compliance report almost always exists; what is missing is trusting it. This guide shows how to model licenses, why the overage number comes out wrong, and how to tune reconciliation and alerts so an audit is never a surprise.

The data model that underpins everything

Before any report, understand how GLPI links the pieces. Four native tables matter:

• glpi_softwares - the software itself (one row per name).
• glpi_softwareversions - the versions of each software.
• glpi_softwarelicenses - the purchased licenses, with quantity (number field), type, expiry date (expire) and supplier.
• glpi_items_softwareversions - each detected install, linking an item (computer, VM) to a version.

Compliance comes from crossing the sum of number in glpi_softwarelicenses with the row count in glpi_items_softwareversions, grouped by software. If either side points to the wrong glpi_softwares, the math closes neat and false.

Register the license with the right field

Under Management > Licenses (or the Licenses tab inside the software), record every purchase. The steps we standardize:

1. Link it to the correct software - a license belongs to a specific glpi_softwares. Bought Office, but inventory created "Microsoft Office" and "Microsoft 365 Apps" as two softwares? The license is orphaned from half the installs.
2. Quantity in number - the total seats purchased. Here is the first trap: number = -1 means an unlimited license (freeware, site license with no cap). Leaving it blank or 0 zeroes out the basis for comparison.
3. License type - OEM, Volume, Subscription, etc. (glpi_softwarelicensetypes dropdown). It is not decoration: it defines the real compliance rule.
4. Expiry (expire) - fill it for subscription and Software Assurance; leave it empty for perpetual.
5. Affect the license to the installs - registering is not enough. You must allocate the license to the items that consume it (the license items tab) so GLPI counts consumption. Many estates have a license recorded and no allocation: the report shows everything available for something already over the limit.

The field mistake that zeroes the overage: fragmented names

On sustained estates, the number-one reason for "compliance shows zero overage, but I know I am over" is not a report bug - it is name fragmentation. GLPI inventory creates one glpi_softwares per exact name reported by the agent. So "Google Chrome", "Google Chrome (64-bit)" and "Google Chrome" with one extra space become three distinct softwares, each with its own install count. You tie the license to one of them, and the other two show up as "no license recorded" - or, worse, "compliant", because each has few installs. The global math never adds up.

The fix is not in the report, it is at the entry point: the Software Dictionary (Administration > Rules > Software dictionary) normalizes the name before it is written to glpi_softwares. A rule that rewrites "Google Chrome (64-bit)" to "Google Chrome" merges the installs into a single software, and then the license covers everything. The detail only operators know: the dictionary rewrites only on the next inventory entry, so after creating the rules you must reprocess inventory (or wait for the next agent cycle) for the old installs to migrate. Applying the rule and checking the report the same minute is misleading: nothing has changed yet.

The query that shows the real overage

While the dictionary is not tuned, the compliance screen mixes in noise. We run straight against the database the query that lists, per software, installs against licenses - already excluding unlimited ones (number = -1) from the sum:

-- Software with more installs than licenses (compliance overage).
-- number = -1 means an unlimited license: we exclude it from the sum.
SELECT s.name AS software,
       COALESCE(lic.licenses, 0)   AS licenses,
       COALESCE(inst.installs, 0)  AS installs,
       COALESCE(inst.installs, 0) - COALESCE(lic.licenses, 0) AS overage
FROM glpi_softwares s
LEFT JOIN (
    SELECT softwares_id, SUM(number) AS licenses
    FROM glpi_softwarelicenses
    WHERE is_deleted = 0 AND number > 0
    GROUP BY softwares_id
) lic  ON lic.softwares_id = s.id
LEFT JOIN (
    SELECT sv.softwares_id, COUNT(*) AS installs
    FROM glpi_items_softwareversions isv
    JOIN glpi_softwareversions sv ON sv.id = isv.softwareversions_id
    WHERE isv.is_deleted = 0
    GROUP BY sv.softwares_id
) inst ON inst.softwares_id = s.id
WHERE s.is_deleted = 0
  AND COALESCE(inst.installs, 0) > COALESCE(lic.licenses, 0)
ORDER BY overage DESC;

The result is the shopping list: every row with a positive overage is fine risk in an audit. Cross-check with the dictionary before you start buying - half the "overage" is usually the same software counted under two names.

Expiry alerts that actually fire

An expired subscription license is money burned or a service cut off. GLPI alerts, but the alert has three switches that all need to be on - and the common mistake is thinking that filling the expire field is enough:

• On the entity (Administration > Entities, notifications tab): enable the license alarms and set the lead time (internal fields use_licenses_alert and send_licenses_alert_before_delay).
• On the notification (Setup > Notifications): the software license notification must be active, with a recipient and a template defined.
• On the cron: the automatic action that scans expiries only runs if the GLPI cron runs. In production that means an operating-system cron, not the internal mode that depends on user traffic.

The external cron config we leave at every client:

# Crontab of the PHP user (www-data/apache): triggers GLPI automatic actions,
# including the license-expiry alert. Without this external cron, the alert only runs
# "piggybacking" on user traffic - and in a window with no access, it never fires.
* * * * * www-data /usr/bin/php /var/www/glpi/front/cron.php &>/dev/null

Regardless of the native alert, every Monday we send an expiry report straight from the database, so we do not depend on the notification alone:

-- Licenses expiring in the next 90 days, most urgent first.
SELECT s.name  AS software,
       sl.name AS license,
       sl.number,
       sl.expire,
       DATEDIFF(sl.expire, CURDATE()) AS days_to_expire
FROM glpi_softwarelicenses sl
JOIN glpi_softwares s ON s.id = sl.softwares_id
WHERE sl.is_deleted = 0
  AND sl.expire IS NOT NULL
  AND sl.expire <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)
ORDER BY sl.expire ASC;

Decision matrix by license type

The license type changes how you model it in GLPI and where the risk sits. The matrix we use when designing:

Reconciliation is a routine, not a project

Software compliance is not a photo, it is a movie: the estate changes every week. The rhythm that works in sustainment is quarterly for full reconciliation (run the overage query, review the dictionary, check allocations) and monthly for expiries. Prioritize the vendors that actually audit - Microsoft, Adobe, Oracle and Autodesk enforce, and the bill for a non-compliance they discover is orders of magnitude larger than the cost of keeping the data clean. The goal is not a pretty report; it is being able to answer "how many licenses of X do I have and how many do I use?" with a number you trust.

Want your software estate under control, with a reconciled license base and alerts that actually fire? Discover NexTool's GLPI sustainment.

Reviewed by the NexTool Solutions team.