Licence usage report: one figure per country, evidence per seat
- The problem
- Licences assigned for years and never reviewed; nobody could say who still used what.
- What I built
- A monthly report per country with a verdict and its evidence for every seat.
- What came of it
- A defensible reclaim list, and a standing answer to "do we still need all of these?"
Licensing is the part of Microsoft 365 where mistakes compound quietly. Assigning a licence is one click. Reclaiming one depends on an offboarding process working correctly every single time, and offboarding is the process most likely to have a gap in it. People leave and the account lingers. Service accounts and shared mailboxes never went through the procedure in the first place. Someone was given a seat for a project that ended two years ago.
None of that surfaces as an error, it surfaces as a bill.
Split the estate before you write a single query
The first useful move is dividing licences into two groups, because the two behave nothing alike when you take them away.
Add-on licences (Copilot, Power BI Pro, Visio, Project, Planner) are the easy half. Nothing lives inside them. If nobody has opened the application in months, remove the seat; the worst case is a ticket you can answer in a minute.
Base licences, the ones carrying a mailbox and a OneDrive, are a different animal. Removing one is not withdrawing a feature, it is deciding what happens to someone’s data. That needs a written decision before it needs a script. The realistic options, each with a cost hiding behind it:
Archive it, or convert to a shared mailbox plus a SharePoint site. Simple, but storage only ever grows, and storage is money on a delay.
Export a PST, store it somewhere that matches the sensitivity of the content, and delete it after a defined retention period.
Move it to cold storage, far cheaper than leaving it in the productivity platform, drop a small text file with the pointer in its place, and delete the copy if nobody asks for it inside a set window.
There is no universally correct choice. What matters is that the organisation has written down which one applies and when: before the first reclaim, not after the first complaint.
The report
With that settled, the deliverable was a report. The admin centre already shows usage per product, so the raw data was never missing; what nobody had was the join to the licence holders, per country, with a verdict on every row: one CSV per country, one row per seat, keep, confirm with the owner, or reclaim. Country IT leads had been asked for years to say who still needed what, with no data to answer from. Now they get a file and a figure.
The first run produced a clean, alarming result: around 40% of Visio and Planner seats showed no usage at all. Reclaiming them would have been the easiest saving of the year.
It was also wrong, and working out why is what made the report worth trusting.
Two gaps that produce the same false conclusion
Usage data came from the Microsoft 365 usage reports in Graph, joined to licence holders by user principal name. Two assumptions sit inside that sentence, and neither had been tested. Both are worth checking in your own tenant today.
Desktop Visio and Project, at the time, left no trace I could find in the cloud. The usage data I had only spoke of the web app. Someone opening Visio on their laptop every morning, on a file from a network share, never showed up. For those two products, “unused” and “impossible to measure” were indistinguishable back then.
Both gaps fail the same way: a silent empty result, read as a negative finding.
The fix is a column, not a patch
Rather than repair the join and move on, the report gained a column: UsageSource. Every row now states where its verdict came from: usage report, sign-in log, endpoint telemetry, or not collected. A reader can separate a seat confirmed dormant from a seat nobody can see yet.
For desktop Visio and Project I had no API, so I wrote one: a small read-only script, run on the device through Intune remediations, reading the Office recent-files list and the Windows launch counter and returning a last-used date per application. Device coverage was reported next to it, because a device the script never reached is not evidence either.
That script is retired now, and it should be. The platform moved: the Visio Activity and Project Activity reports in the admin centre carry a Desktop column per user (did that person open the desktop app at least once in the window, over 7 to 180 days) with a filter that separates Visio Plan 1 and 2 seats from the seeded usage some subscriptions include. That is more precise than anything read off a laptop. Two things to know before you trust it: the Visio report can take up to 72 hours to appear, and user names are hidden by default in usage reports, which is the first gap in this post all over again. Writing the script was right when there was no answer; keeping it once there was one would not have been.
What survived
With the join repaired and the desktop signal added, the reclaimable share settled at roughly a third of add-on seats: a few on dormant accounts, most with the application confirmed unused, the rest waiting on an owner’s confirmation. A comparable share stays marked usage unknown, and the report says so instead of folding it into either column.
Smaller than the first number, and defensible in front of the person whose licence is being removed, which the first one was not.
The report runs every month now, so “do we still need all of these?” has a standing answer instead of an annual scramble.