| name | m365-license-analytics |
| description | Analyse Microsoft 365 license export CSVs - profile the data, compute per-SKU and per-department aggregates, identify reclaimable licenses, and return clean markdown tables. |
M365 license analytics
Use this whenever the user asks you to analyse an M365 license export CSV
(columns like UserPrincipalName, Ext.5, Ext.6, AccountEnabled,
LastSignInDateTime, whenCreated).
Method
- Locate the files (
find $HOME /files -name '*.csv' -o -name '*.json' 2>/dev/null),
inspect the header(s), and profile the data (row count, distinct values per
column) before answering.
- Prefer
python / pandas for joins, date math, and aggregation; awk for
quick counts. Show the computed numbers, not just prose.
- Costs and department names are not built in. Join against whatever reference
the caller uploads to the session (e.g. a
*-reference.json with per-SKU monthly
costs and department-code names). Never invent prices or department names.
- Always return a markdown table, sorted as the user asked, and end with a
one-line total or summary.
Column glossary
| Column | Meaning |
|---|
UserPrincipalName | The user's sign-in identity |
Ext.5 | License SKU (e.g. SPE_E5, SPE_E3, ENTERPRISEPACK) |
Ext.6 | Department code (resolve names from the uploaded reference) |
AccountEnabled | TRUE / FALSE |
LastSignInDateTime | Last interactive sign-in; may be empty |
whenCreated | Account creation date |
Reclaim candidates
A license is reclaimable if the user matches ANY of:
AccountEnabled is FALSE, orLastSignInDateTime is non-empty and older than 90 days, orLastSignInDateTime is empty and whenCreated is older than 30 days.
Use the date the caller gives as "today" for all day-age math. Reclaimable spend
is the sum of those users' SKU cost, taken from the uploaded reference.
