Licence usage report: one figure per country, evidence per seat

Every tenant I have worked in has the same problem: licences get assigned and never reviewed. Base licences (F1, E3, Exchange Online) and the paid add-ons on top of them (Copilot, Power BI Pro, Visio, Project, Planner) pile up over years. Somebody eventually asks “do we still need all of these?”, and nobody has the data to answer.

So I built a report. One CSV per country, one row per seat, and for each seat a verdict: keep, confirm with the owner, or reclaim. The add-ons alone came to about 900 seats, roughly €190,000 a year, so the stakes were clear.

The first number

The first version came back with a clean, alarming figure: about 340 Visio and 180 Project seats with no usage at all, over 500 in total. Reclaiming them would have been the easiest saving of the year.

It was also wrong, and finding out why made the report worth trusting.

Two gaps, one false conclusion

The usage data came from the Microsoft 365 usage reports in Graph, joined to licence holders by user principal name. Two things had never been checked.

The tenant hides user details in reports. There’s a privacy setting that hashes user names in every usage report. With it on, the join on UPN silently matched nothing, and “no match” was being read as “no activity”. Nothing failed. The report simply had fewer rows than it should have, and no row said so.

Desktop Visio and Project leave no trace in the cloud. The only signal Microsoft exposes is a sign-in to the web app. Someone who opens Visio on their PC every day, with a file from a network share, never produces it. For those two products, “unused” and “unmeasurable” looked identical.

What changed

Rather than patch the join and move on, the report gained a column: UsageSource. Every row now says where its verdict came from: usage report, sign-in log, endpoint telemetry, or not collected. A reader can tell a confirmed dormant seat from a seat we simply cannot see yet.

For desktop Visio and Project, there is no API, so I wrote one. A small read-only script runs on the device through Intune remediations and reads two local signals: the Office recent-files list and the Windows launch counter. It returns a last-used date per app, which joins back to the licence holder. Device coverage is reported alongside, because a device the script never reached is not evidence either.

The number that survived

With the join fixed and the desktop signal added, the reclaimable set settled at about 300 seats across more than 20 countries, roughly €60,000 a year: a few dozen on dormant accounts, most with the application confirmed unused, the rest awaiting an owner’s confirmation. Another 300 or so remain marked usage unknown, and the report says so rather than folding them into either column.

Smaller than 500. Defensible to the person whose licence is being removed, which the first number was not.

What I took from it

The rule I now apply to every report: absence of data is not evidence of absence. If a source didn’t load, the rows that depend on it say unknown, never zero. It costs one column and it is the difference between a number you can defend and a number that looks good.