Time to Review Licensing When Prices Increase
Updated 10 July 2023
On September 9, I explained the steps to convert a PowerShell script from the Azure AD cmdlets due to stop working on March 31, 2023 to equivalents from the Microsoft Graph SDK for PowerShell. I then followed up by noting some of the issues involved in using the SDK for interactive scripts. Given Microsoft’s announcement of price increases for Office 365 and Microsoft 365 licenses due in March 2022, it’s a good time to audit the set of licenses in a tenant and ask some questions about the distribution of licenses across accounts. To do that, we need an Entra ID licensing report.
Many examples of generating such a report exist based on the cmdlets which will stop working next June (here’s one version), so in this article I’ll go through the steps to generate a licensing report using SDK cmdlets.
Stage 1: Extract Licensing Data for the Tenant
The basic steps in generating a report are in two stages. First, we create two data (CSV) files containing:
- The product licenses (SKUs) used in the tenant.
- The service plans belonging to the product licenses. A service plan is something like Exchange Online Plan 2 or Teams which is bunded in a product license like Office 365 E3.
Because every tenant is different, we have some code (available from GitHub) to generate these files. We connect to the Graph and use the Get-MgSubscribedSku cmdlet to fetch the license data for the tenant. We then export the list of products (SKUs) to a CSV before looping through the products to extract the service plans and exporting them to a second CSV.
Connect-MgGraph
[Array]$Skus = Get-MgSubscribedSku
# Generate CSV of all product SKUs used in tenant
$Skus | Select SkuId, SkuPartNumber, DisplayName | Export-Csv -NoTypeInformation c:\temp\ListOfSkus.Csv
# Generate list of all service plans used in SKUs in tenant
$SPData = [System.Collections.Generic.List[Object]]::new()
ForEach ($S in $Skus) {
ForEach ($SP in $S.ServicePlans) {
$SPLine = [PSCustomObject][Ordered]@{
ServicePlanId = $SP.ServicePlanId
ServicePlanName = $SP.ServicePlanName
ServicePlanDisplayName = $SP.ServicePlanName }
$SPData.Add($SPLine)
}
}
$SPData | Sort ServicePlanId -Unique | Export-csv c:\Temp\ServicePlanData.csv -NoTypeInformation
Update: The Microsoft page detailing product names and license SKUs now offers the opportunity to download a CSV file containing the information needed by the report. I’ve updated the script in GitHub to generate the hash tables used by this report to use the file generated by Microsoft if it’s available.
This script generates two files. However, the files need some checking before we can use them because the generated data contains GUIDs to identify the licenses and service plans plus code names. For example, 6fd2c87f-b296-42f0-b197-1e91e994b900 is the GUID identifying Office 365 E3, which has a code name of ENTERPRISEPACK, while 3fb82609-8c27-4f7b-bd51-30634711ee67 is an example of a service plan GUID. This is code BPOS_S_TODO_3, the To Do service plan included in Office 365 E5.
We could use these values in the report, but it’s nicer to have user-friendly (or user-understandable) names. This information is in the DisplayName column, and if you download the Microsoft CSV file as described above and use the script to extract the information, the column is populated. However, you might still want to check the CSV file to make sure that the values in the DisplayName column are what you want to use. Figure 1 shows editing of the CSV file for service plan data.
After you’re finished editing the CSV files, they should both have three fields per record. Here’s an extract of the SKU information (top) and Service Plan information (bottom):
SKU information: SkuId SkuPartNumber DisplayName 078d2b04-f1bd-4111-bbd4-b4b1b354cef4 AAD_PREMIUM Azure AD Premium P1 84a661c4-e949-4bd2-a560-ed7766fcaf2b AAD_PREMIUM_P2 Azure AD Premium P2 c52ea49f-fe5d-4e95-93ba-1de91d380f89 RIGHTSMANAGEMENT Azure Information Protection P1 90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96 SMB_APPS Business Apps (free) Service Plan Information: ServicePlanId ServicePlanName ServicePlanDisplayName 041fe683-03e4-45b6-b1af-c0cdc516daee POWER_VIRTUAL_AGENTS_O365_P2 Power Virtual Agents for Office 365 P2 0683001c-0492-4d59-9515-d9a6426b5813 POWER_VIRTUAL_AGENTS_O365_P1 Power Virtual Agents for Office 365 P1 07699545-9485-468e-95b6-2fca3738be01 FLOW_O365_P3 Flow for Office 365 P3 0898bdbb-73b0-471a-81e5-20f1fe4dd66e KAIZALA_STANDALONE Kaizala Standalone 0f9b09cb-62d1-4ff4-9129-43f4996f83f4 FLOW_O365_P1 Flow for Office 365 P1
Interpreting the values used by Microsoft is an art into itself. The Product names and service plan licensing page is a valuable resource, but sometimes it’s a matter of guesswork based on your knowledge of the products and service plans in use.
Finally, after preparing the SKU and Service Plan information, rename the files to match the expected names used in the reporting script. These are:
- c:\temp\SkuDataComplete.csv: The product data.
- c:\temp\ServicePlanDataComplete.csv: The service plan data.
Of course, you can use whatever names you like if you update the script code to match.
Stage 2: Generate the Report
The steps involved in creating a report are straightforward.
- Connect to the Microsoft Graph as before. You’ll need to consent to the following permissions: Directory.AccessAsUser.All, Directory.ReadWrite.All, and AuditLog.Read.All.
- Check that the input data files are available and exit if not.
- Fetch the set of Entra ID user accounts using the Get-MgUser cmdlet.
- Loop through the set of user accounts.
- For each licensed account (some accounts like those used for resource or shared mailboxes don’t need licenses), extract the license data and check if any license has disabled service plans. Check the information against the input data files to get human-friendly names.
- Record the license information in a PowerShell list.
- After processing the user accounts, create a HTML report and CSV file containing the license information.
Here’s the main loop (you can download the complete script from GitHub):
ForEach ($User in $Users) {
If ([string]::IsNullOrWhiteSpace($User.AssignedLicenses) -eq $False) { # Only process account if it has some licenses
Write-Host "Processing" $User.DisplayName
[array]$LicenseInfo = $Null; [array]$DisabledPlans = $Null
ForEach ($License in $User.AssignedLicenses) {
If ($SkuHashTable.ContainsKey($License.SkuId) -eq $True) { # We found a match in the SKU hash table
$LicenseInfo += $SkuHashTable.Item($License.SkuId) }
Else { # Nothing doing, so output the SkuID
$LicenseInfo += $License }
# Report any disabled service plans in licenses
If ([string]::IsNullOrWhiteSpace($License.DisabledPlans) -eq $False ) { # Check if disabled service plans in a license
ForEach ($DisabledPlan in $License.DisabledPlans) { # Try and find what service plan is disabled
If ($ServicePlanHashTable.ContainsKey($DisabledPlan) -eq $True) { # We found a match in the Service Plans hash table
$DisabledPlans += $ServicePlanHashTable.Item($DisabledPlan) }
Else { # Nothing doing, so output the Service Plan ID
$DisabledPlans += $DisabledPlan }
} # End ForEach disabled plans
} # End if check for disabled plans
} # End Foreach Licenses
# Report information
[string]$DisabledPlans = $DisabledPlans -join ", "
[string]$LicenseInfo = $LicenseInfo -join (", ")
$ReportLine = [PSCustomObject][Ordered]@{
User = $User.DisplayName
UPN = $User.UserPrincipalName
Country = $User.Country
Department = $User.Department
Title = $User.JobTitle
Licenses = $LicenseInfo
"Disabled Plans" = $DisabledPlans }
$Report.Add($ReportLine)
} #end If account is licensed
Else { $UnlicensedAccounts++}
} # End ForEach Users
Figure 2 shows the license information as generated by the script. As you can see, we include the country and department properties from the user accounts to allow sorting of the information based on those properties. Because this is PowerShell code, it’s easy to add any property available in user accounts to the set included in the report.
Figure 3 shows the HTML version of the report.
Apart from anything else, reports like this also highlight deficiencies in user information stored in Entra ID. In Figure 3, you can see that several accounts lack a country property while some lack a job title. These deficiencies affect the information displayed in different places within Microsoft 365 such as user profile cards and the organization view for people available in Teams.
Update (November 23, 2022): I’ve included some code in the script to generate a new section in the HTML report to detail the SKU usage for the tenant.
Running the Report
Generating a licensing report could be something you do quarterly or semi-annually. If that’s the case, it’s probably OK to run the script using an interactive Graph SDK connection. On the other hand, if you want a more regular output, consider creating a new registered app in Entra ID to use to run the script (app-only access) or use Azure Automation.
Remember, this code is PowerShell and what I have written is there to explore and explain the principles behind generating a licensing report. Now that you know what to do, let your imagination run riot and let us know what kind of interesting ways you exploit license data.
The Real Person!
The Real Person!
Sorry sir. Thank you so much .
The Real Person!
The Real Person!
I am looking for O365 admin center under billing License subscriptions page to monitor through PowerShell script. Example License availability and consumed details. Please assist.
Like this? https://office365itpros.com/2023/07/06/microsoft-365-subscriptions-ps/
The Real Person!
The Real Person!
Thank you so much sir
The Real Person!
The Real Person!
Getting below error. Please help.
Get-MgSubscribedSku : One or more errors occurred.
At line:3 char:1
+ [array]$Skus = Get-MgSubscribedSku
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-MgSubscribedSku_List], AggregateException
+ FullyQualifiedErrorId : System.AggregateException,Microsoft.Graph.PowerShell.Cmdlets.GetMgSubscribedSku_List
You know, it’s always good to read the documentation for cmdlets and make sure that you understand the permissions required to run Graph cmdlets. It’s usually the problem.
https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.identity.directorymanagement/get-mgsubscribedsku?view=graph-powershell-1.0 lists the permissions…
The Real Person!
The Real Person!
Thank you sir
The Real Person!
The Real Person!
Sir Good Morning, I have tried it was working but Portal name and our script table is not matching showing different names. Please help and is there any way get the exact details.
I’m afraid I can’t help because I cannot see the data you’re working with or the errors you report. The code is PowerShell, so it is easily changed. The script is intended to be a learning tool rather than an off-the-shelf solution. I can’t provide online support to get the tool running in individual Microsoft 365 tenants.
The Real Person!
The Real Person!
Thanks for this Tony. Clearly helping many with their reporting.
Would it be possible to split each license assignment onto its own line in the output? For example, if a user had x5 licenses assigned, instead of all of them being bundled into one column in the output could you create x5 lines for each individual license assigned? Our current script does this and ideally we would have that same ability using the graph cmdlets.
Each license is a separate entity, so it would be possible to create a report where each assignment is on a separate line. It’s just PowerShell.
The Real Person!
The Real Person!
Hi Tony, thanks for your contributions.
In my case, I have 02 subscription plans with the same SKUPartNumber, which correspond to “O365_BUSINESS_PREMIUM”, those plans, one corresponds to NCE and the other to Prepaid added with KEY to the Tenant from different providers. However, the tenant returns me the total O365_BUSINESS_PREMIUM licenses, without differentiating which one was added with KEY and the other directly by the provider.
I have tried using PS commands but without positive results.
Have you had a similar case, or could you guide me how it could be done?
Sounds like licenses distributed by a CSP. I have no knowledge of the structure of these licenses because I have never worked with them. Looking at the output of Get-MgSubscribedSKU there is an ID made up of the tenant id and SKU id and an account id which is the tenant id. Maybe you could use those values to distinguish the plans.
The Real Person!
The Real Person!
Thanks for your response. That’s right, it has been distributed by a CSP. The curious thing is that with commands in PS it shows me the total of the SKUPartNumber, which corresponds to O365_BUSINESS_PREMIUN, and both in the tenant they are grouped that way even though they correspond to licenses entered by a CSP (290) licenses and by Prepaid Keys ( 28). Both have different expiration dates, but I cannot identify the users who have been assigned the 28 licenses, neither graphically in the tenant nor PS commands.
I have also consulted MS but without positive results.
And I try to identify the subscriptionIDs associated with the SKUpartNumber, but the results are empty, also trying with the expiration or renewal dates and the same, the results are empty.
At this point those 28 licenses are going to expire, and I have no knowledge of which user accounts it will affect.
i tray with many comands,
AccountObjectId : 8947c3dd-629f-4665-951d-c3681ba542a7
AccountSkuId : XXX:O365_BUSINESS_PREMIUM
ActiveUnits : 318
ConsumedUnits : 301
LockedOutUnits : 0
ServiceStatus : {Microsoft.Online.Administration.ServiceStatus, Microsoft.Online.Administration.ServiceStatus, Microsoft.Online.Administration.ServiceStatus, Microsoft.Online.Administration.ServiceStatus…}
SkuId : f245ecc8-75af-XXXXXXXXXXXXXXXXXXXXX
SkuPartNumber : O365_BUSINESS_PREMIUM
SubscriptionIds : {753cbb11-4c8b-XXXXXXXXXXXXXXXXX, 6132bdfa-738f-yyyyyyyyyyyyyyyyyyyy}
SuspendedUnits : 0
TargetClass : User
WarningUnits : 0
Sorry for the delay in replying. Your comment ended up in spam.
Have you tried to run Get-MgUser and fetching the assignedLicenses property for each account to check if the SKU part number is present? That’s how I might look for the 28 licenses.
Hi Tony,
While fetching data after I run the listofSKU script the displayname field stays empty. Am I missing something?
This is your script I use:
Connect-MgGraph
[Array]$Skus = Get-MgSubscribedSku
# Generate CSV of all product SKUs used in tenant
$Skus | Select SkuId, SkuPartNumber, DisplayName | Export-Csv -NoTypeInformation c:\temp\ListOfSkus.Csv
# Generate list of all service plans used in SKUs in tenant
$SPData = [System.Collections.Generic.List[Object]]::new()
ForEach ($S in $Skus) {
ForEach ($SP in $S.ServicePlans) {
$SPLine = [PSCustomObject][Ordered]@{
ServicePlanId = $SP.ServicePlanId
ServicePlanName = $SP.ServicePlanName
ServicePlanDisplayName = $SP.ServicePlanName }
$SPData.Add($SPLine)
}
}
$SPData | Sort ServicePlanId -Unique | Export-csv c:\Temp\ServicePlanData.csv -NoTypeInformation
There isn’t a display name property in the output. Lines in Service plan list look like those shown below. You need to amend the ServicePlanDisplayName property to insert berrer names, like replace M365_COPILOT_SHAREPOINT with Microsoft 365 Copilot for SharePoint.
$Spdata
ServicePlanId ServicePlanName ServicePlanDisplayName
————- ————— ———————-
113feb6c-3fe4-4440-bddc-54d774bf0318 EXCHANGE_S_FOUNDATION EXCHANGE_S_FOUNDATION
acffdce6-c30f-4dc2-81c0-372e33c515ec Microsoft Stream Microsoft Stream
fe6c28b3-d468-44ea-bbd0-a10a5167435c COPILOT_STUDIO_IN_COPILOT_FOR_M365 COPILOT_STUDIO_IN_COPILOT_FOR_M365
0aedf20c-091d-420b-aadf-30c042609612 M365_COPILOT_SHAREPOINT M365_COPILOT_SHAREPOINT
82d30987-df9b-4486-b146-198b21d164c7 GRAPH_CONNECTORS_COPILOT GRAPH_CONNECTORS_COPILOT
Hi, thanks for delivering such a well structured and effective script 🙂
My question is how should I edit the script so that it only shows the Licenses Office E3 and E1?
Well, you could limit the Get-MgUser command with a filter to find accounts that have Office 365 E3 or E1 licenses. That’s the obvious solution.
Thanks for the quick reply. I just executed the script (haven’t filtered for the E3 or E1 Licenses yet, so it’s still in the original format so to speak). On the first run it worked fine and created both the CSV and HTML Report. But now when I try to run it a second time it doesn’t work and I always get the following error:
Connect-MgGraph : A parameter cannot be found that matches parameter name ‘NoWelcome’.
At C:\Script\M365-Lizenzen-Report.ps1:52 char:92
+ … y.AccessAsUser.All, Directory.Read.All, AuditLog.Read.All” -NoWelcome
+ ~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Connect-MgGraph], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph
Connect-MgGraph : A parameter cannot be found that matches parameter name ‘ClientSecretCredential’.
At C:\Script\M365-Lizenzen-Report.ps1:87 char:37
+ Connect-MgGraph -TenantId $tenantID -ClientSecretCredential $ClientSe …
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Connect-MgGraph], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.Graph.PowerShell.Authentication.Cmdlets.ConnectMgGraph
Finding licensed user accounts…
Get-MgUser : Authentication needed, call Connect-MgGraph.
At C:\Script\M365-Lizenzen-Report.ps1:120 char:1
+ [Array]$Users = Get-MgUser -Filter “assignedLicenses/`$count ne 0 and …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-MgUser_List], AuthenticationException
+ FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Cmdlets.GetMgUser_List
No licensed user accounts found – exiting
Do you know what the problem could be? I have tested the Connect-MgGraph seperately and there is no problem with the authentication.
What version of the SDK are you using? the -NoWelcome parameter simply suppresses some welcome text. It’s not important. Try Connect-MgGraph without the -NoWelcome.
Thanks, yes now I just left out the -NoWelcome parameter and the error above doesn’t show anymore. But now I keep getting the error below. I have updated my Microsoft.Graph Module, installed the Microsoft.Graph.Authentication module manually, but this error still appears. It has some kind of problem with Get-MgUser_List command.
Finding licensed user accounts…
Get-MgUser : Could not load file or assembly ‘Microsoft.Graph.Authentication, Version=1.7.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.
At C:\Script\M365-Lizenzen-Report.ps1:120 char:1
+ [Array]$Users = Get-MgUser -Filter “assignedLicenses/`$count ne 0 and …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-MgUser_List], FileNotFoundException
+ FullyQualifiedErrorId : Microsoft.Graph.PowerShell.Cmdlets.GetMgUser_List
No licensed user accounts found – exiting
Do you have any Idea what could be causing this?
All of the modules in the Graph SDK must be at the same version. Install the complete Graph modules and try again.
Update-Module -Name Microsoft.Graph -Force -Scope AllUsers
Never mind, I have figured it out now. I had installed the new version but was still using the same Powershell Session. Just had to open a new one. Now it’s working as it should 🙂
I’ve been there… I share your pain.
Hello Tony, thanks so much for this article, the report is of great value. I have run it previously without issues, but now all I get in the Last Signin and Days Since Last Signin is unknown for all accounts. I am still running this with a global admin account, but is there maybe something wrong with the permissions of the Graph app in Entra?
Works for me:
[Array]$Users = Get-MgUser -Filter “assignedLicenses/`$count ne 0 and userType eq ‘Member'” `
-ConsistencyLevel eventual -CountVariable Records -All `
-Property id, displayName, userPrincipalName, country, department, assignedlicenses, `
licenseAssignmentStates, createdDateTime, jobTitle, signInActivity, companyName | `
Sort-Object DisplayName
$user = $users | Where-Object displayName -match ‘Tony Redmond’
$user.signinactivity
LastNonInteractiveSignInDateTime LastNonInteractiveSignInRequestId LastSignInDateTime LastSignInRequestId
——————————– ——————————— —————— ——————-
24/04/2024 09:53:39 5eb6b2e8-8710-489f-a2da-e859c6dd9700 24/04/2024 09:35:53 2125055d-f432-434b-9ca5-0b32fb042101
Fetching the sign in data requires the AuditLog.Read.All permission. Does the app have that permission?
Hi Tony, love your work! I’ve been using several iterations of this script for some years now, to great satisfaction.
One question I can’t seem to figure out how to accomplish. At the moment the script outputs one row per user and accumulates the licenses.
I’d like it to output one row per license, so if a user has 3 licenses assigned, it would be 3 rows in the output file. Ideally ii would then also have the cost per license calculated.
If you get a chance to look into this, wonderful 🙂 I will keep searching as well
Where the script generates the report ($ReportLine = [PSCustomObject][Ordered]@{ ), you’d need to split out the information in the $LicenseInfo variable so that instead of the single line, it generates a line for each license.
I might do this slightly differently. For each user, generate a line showing details of the user activity, pricing, etc. Then under that line, generate details for each license.
Hi, I would also like to add the company field; I added the line below but unfortunately it doesn’t work; what am I doing wrong?
Company = $User.CompanyName
You’ll need to fetch the CompanyName property when running Get-MgUser.
To help, I added the property to the set fetched by the script. You can download the updated version from GitHub.
Hi @Tony.
Thanks for the script, however I am looking automate this script.
Currently I need to track Microsoft Copilot License based on lastactivitydate 90 days and remove them from users account if it not used for more than 90 days.
Is this possible?
Thanks
Sai
Anything is possible if you have the data. How will you determine user inactivity?
Based on Last activity date for lets say copilot license. I can get these details from Usage Report. But is there any script which we can export results using Microsoft Graph or powershell?
Hi Tony,
I am using these scripts from UAE; everything is working except the cost of the license is showing zero to all.
SKU Id SKU Name Units Used Units Purchased Annual licensing cost
c5928f49-12ba-48f7-ada3-0d743a3601d5 Visio Online Plan 2 6 6 USD 0.00
3b555118-da6a-4418-894f-7df1e2096870 Microsoft 365 Business Basic 13 18 USD 0.00
c9d442fc-21fb-4bd7-89e0-a710d74987f6 2 2 USD 0.00
b30411f5-fea1-4a59-9ad9-3db7c7ead579 Power Apps per user plan 1 1 USD 0.00
3f9f06f5-3c31-472c-985f-62d9c10ec167 Power Pages vTrial for Makers 3 10000 USD 0.00
06ebc4ee-1bb5-47dd-8120-11324bc54e06 Microsoft 365 E5 150 155 USD 0.00
ad7a56e0-6903-4d13-94f3-5ad491e78960 Microsoft Defender Vulnerability Management Add-on 6 8 USD 0.00
6af4b3d6-14bb-4a2a-960c-6c902aad34f3 Microsoft Teams Rooms Basic 3 10 USD 0.00
a403ebcc-fae0-4ca2-8c8c-7a907fd6c235 Microsoft Fabric (Free) 15 1000000 USD 0.00
dcb1a3ae-b33f-4487-846a-a640262fadf4 Microsoft Power Apps Plan 2 Trial 1 10000 USD 0.00
bc946dac-7877-4271-b2f7-99d2db13cd2c Dynamics 365 Customer Voice Trial 1 1000000 USD 0.00
606b54a9-78d8-4298-ad8b-df6ef4481c80 Power Virtual Agents Viral Trial 7 10000 USD 0.00
3a256e9a-15b6-4092-b0dc-82993f4debc6 Dynamics 365 for Talent 1 10000 USD 0.00
f30db892-07e9-47e9-837c-80727f46fd3d Microsoft Power Automate Free 129 10000 USD 0.00
4b74a65c-8b4a-4fc8-9f6b-5177ed11ddfa Power Virtual Agent User License 6 10 USD 0.00
226ca751-f0a4-4232-9be5-73c02a92555e Windows 365 Enterprise 2 vCPU 4 GB 128 GB 1 1 USD 0.00
a929cd4d-8672-47c9-8664-159c1f322ba8 Microsoft Intune Suite 15 15 USD 0.00
1f2f344a-700d-42c9-9427-5cea1d5d7ba6 Microsoft Stream 6 1000000 USD 0.00
53818b1b-4a27-454b-8896-0dba576410e6 Project Plan 3 3 4 USD 0.00
5b631642-bd26-49fe-bd20-1daaa972ef80 Microsoft Power Apps for Developer 2 10000 USD 0.00
Please advise what am missing.
You have to insert the license costs (and the currency symbol) into the subscriptions file (the details are in the article). I have no idea what you pay Microsoft for individual products, so the script doesn’t guess what the costs might be. Open the file with Excel and add the data and all should work.
Thanks for your quick reply.
Beautiful, much appreciated. I altered a few things. The datetime format gave an error, changed to Get-Date -format “dd-MM-yyyy”. Also changed the encoding for csv and html export file to UTF8.
[string]$RunDate = Get-Date -format “dd-MMM-yyyy HH:mm:ss”
$rundate
07-Dec-2023 21:43:55
What kind of error did you see? It’s possible that this might be caused by the locale PowerShell is configured for on your workstation, but hey, it’s PowerShell, so you can change it…
Dears,
any script where we can retrieve the user name using a specific subscription on CSP
Because on admin center or on partner center we can only retrieve the user assigned to what type of licenses but we cannot see which subscription ID is affected
kindly help 🙂
As I don’t have a CSP to test with, I can’t say how the script would change in those circumstances. You’ll have to figure this out for yourself.
Hi Tony,
Great script – works like a charm!
I have been trying to seperate the assigned licenses from the “Direct assigned licenses” heading so that I can filter by license – e.g. a user may have “Microsoft Power Automate Free, Microsoft 365 Business Premium
“, another usere may have “Project Plan 3, Microsoft 365 Business Premium”, etc. – I want to filter by “Microsoft 365 Business Premium” for example.
Is this possible?
Darren
Do you mean that you want a report for just one selected license?
I want to seperate the licences into their own columns if possible – I cannot see a way to do this?
You mean like have separate columns for Office 365 E3, Office 365 E5, EM+S, etc?
If so, you’d extract the licenses from $user.assignedlicenses and use that information to populate separate properties in the output report.
Thats what I am trying to do
@AusSupport
Scheduling the script is relatively easy but you need to do some preparation for it to work:
– App registration in Azure
– (Self-signed) certificate to replace the interactive authentication
– Service account for running the script (regular user account with “Logon as a batch job” or better GMSA in Active Directory environments)
Too much to describe in detail within a comment section. 😉
Hey Tony,
I just wanted to leave you with a big THANK YOU for your good work! It saved me hours of trial and error (and cursing :-D) and works like a charm!
I added multi-customer support and app-only authentication so we can automatically generate reports for all our customers but this was only a small thing compared to your groundwork.
…and still works with Graph V2 (Microsoft released it at the end of July without big announcement or anything). 🙂
No worries. There’s a good book 😉 covering the SDK and all its ups and downs (see chapter 23 of https://gum.co/O365IT/).
Hi Ben, Any chance you could share this? Ive been trying to do the same in a multi tenant environment and I havn’t been able to get it figured out! Would love to see.
Hi Tony,
Thanks for the Script and we can save lots of E5 licenses. Can you direct me to schedule this report?
Use Azure Automation. There are a bunch of articles about this topic on this site, like https://practical365.com/microsoft-graph-sdk-powershell-azure-automation/
Hey Tony, You saved me an enormous amount of time. My tenant has almot 2 million user accounts and Ihave been battling non stop.
Your script helped me get this done in no time..
Big THanks
Terrific. We’re always glad when a Practical365.com article helps.
Hi Tony.. Thanks for all the great articles you are sharing. I am trying to schedule the script ReportUserAssignedLicenses-MgGraph.PS1 using Azure Automation Runbook and it is getting suspended after running for an hour. My tenant has around 80K licensed users and seems like some kind of throttling. The script works fine in my desktop though. In azure automation if i add Top 1000 instead of -All in the Get-Mguser line, it works in that case. Any possible workaround you can think of? Thanks in advance.
According to https://learn.microsoft.com/en-us/azure/automation/automation-runbook-execution#fair-share, an Azure Automation runbook can run for up to 3 hours. There might be some other throttling at play. At this point, I think I would split the 80K users into four 20K segments and process each segment in a separate runbook and then combine the runbook results.
Hi Tony Redmond,
I have the followed the steps and created the PowerShell scripts but when i try to run the scripts via task scheduler I am not getting any output but if i run it manually i am getting the output saved in the specific folder. Could you please on this . Is there any special permission needed to run the scripts for the MG-Graph Because for the other connection like connect-PNPonline,Microsoft teams,exchangeonline I can create the task in task scheduler and getting the out specially for the Connect-Mggraph i am having issue. Is there any other way to run these scripts automatically. Please help.
I use Azure Automation to run SDK scripts. It’s a much more secure and modern method than relying on the Windows Scheduler and we cover the topic extensively in articles like https://practical365.com/microsoft-graph-sdk-powershell-azure-automation/.
In this instance, I imagine that the account being used to run the job doesn’t have the right permissions to run the cmdlets. Running as a scheduled job like this means the job is probably using delegated permissions and it needs application permissions to retrieve data for accounts other than its own.
$Subscriptions= Get-MsolSubscription | foreach{
$SubscriptionName=$_.SKUPartNumber
$SubscribedOn=$_.DateCreated
$ExpiryDate=$_.NextLifeCycleDate
$Status=$_.Status
$TotalLicenses=$_.TotalLicenses
$Print=0
I want to migrate to Connect-MgGraph due to MFA Enabled but i am not getting the exact result as MsolSubscription in Get-MgSubscribedSku
The cmdlets are different so it’s natural that they generate different results. Dates (for license assignments) are not as dependable as you might think, as I explain here https://practical365.com/azure-ad-license-assignment-dates/
In this report i want to add the ProvisioningStatus means how and where to add please help on that
ProvisioningStatus is not currently supported by the Graph SDK. I believe that a change is coming that will allow this data to be retrieved. I can’t say when…
Hello, I’ve tried making some modifications to your script with out success to include assignment dates for some of the licenses like Visio Plan 2 and Project Plan 3.
I’m finding that the output is picking up the last Service Plan given for ‘AssignedPlans’ and not for the Service Plan SKU Id matching for these licenses.
Any help would be greatly appreciated.
Do you mean assignment data?
I don’t have these licenses to test with, but they should react in the same way as any other licenses. What modifications have you made to the script?
I’ve been trying to get the ‘AssignedDateTime’ value for E3 licenses as well.
This is what I’ve put together but I am finding that when it gets to the IF statement, it only flags the last attribute value which doesn’t match part of the IF statement and flicks over to the ELSE statement.
I have a feeling that I need to create a switch and list the service plans & ID’s to get the ‘AssignedDateTime’ for the one of the service plans in use for that license sku.
This is a cut down version of what I have been working on but just for the M365 E3 license(SPE_E3).
Connect-MgGraph -Scope “Directory.AccessAsUser.All, Directory.ReadWrite.All, AuditLog.Read.All”
Select-MgProfile beta
$licenses = Get-MgSubscribedSku
$E3SkuPartNumber = $licenses[16]
$Users = Get-MgUser -Filter “assignedLicenses/`$count ne 0 and userType eq ‘Member'” -ConsistencyLevel eventual -CountVariable Records -All | Sort DisplayName
$resultCollectionLicensesAssigned = @()
Foreach ($User in $Users) {
$UserAssignedLicense = $User.AssignedLicenses
$E3ServicePlans = $E3SkuPartNumber.ServicePlans
Foreach ($M365E3 in $User.AssignedPlans) {
If (($UserAssignedLicense.SkuId -match $E3SkuPartNumber.SkuId) -and ($E3ServicePlans.ServicePlanID -like $M365E3.ServicePlanId -and $M365E3.CapabilityStatus -eq “Enabled”)) {
Write-host “Finding Microsoft 365 E3 Assignment Date” -ForegroundColor Green
$E3Assigned = (Get-Date($M365E3.AssignedDateTime) -format g)
$E3Assigned
}
else {
$E3Assigned = “N/A”
}
}
$obj0 = $null
$obj0 = New-Object PSObject
$obj0 | Add-Member -Name User -MemberType NoteProperty -Value $User.DisplayName
$obj0 | Add-Member -Name UserPrincipalName -MemberType NoteProperty -Value $User.UserPrincipalName
$obj0 | Add-Member -Name E3Assigned -MemberType NoteProperty -Value $E3Assigned
$resultCollectionLicensesAssigned += $obj0
$resultCollectionLicensesAssigned
}
$resultCollectionLicensesAssigned | Export-Csv C:\temp\testing_licensedate.csv -NoTypeInformation
The AssignedDateTime property is in the AssignedPlans property, so you’re checking a service plan rather than a license. I guess you could look for a service plan that’s part of a specific license.
For example, to find the assigned date of an Office 365 E3 license, you could check for the Exchange_S_Enterprise service plan and do something like this:
$AssignedDate = $User.AssignedPlans | Where-Object {$_.CapabilityStatus -eq “Enabled” -and $_.ServicePlanId -eq “efb87545-963c-4e0d-99df-69c6916d9eb0”} | Select-Object -ExpandProperty AssignedDateTime
But maybe I don’t fully understand what you want to do.
That is exactly what I am trying to do. It is the AssignedDateTime attribute under AssignedPlans I am trying to get.
I basically need this attribute to determine how long a license has been assigned to them.
Looking at your other scripts I think I know where I am going wrong. I found ‘ReportIndividualApplicationLicenses.PS1’ to be helpful. I made some changes and created an array with the service plan ids and friendly names. Seems to work ok with Azure AD and need to switch to Graph.
There’s also a Graph API for license assignments: https://learn.microsoft.com/en-us/graph/api/resources/licenseassignmentstate?view=graph-rest-1.0
The problem that you face is that the license assignment date shown in these APIs is actually the timestamp when the license was last changed by an administrator or a background system process. Microsoft updates licenses all the time (for instance, to introduce the Viva Engage Core service plan – https://office365itpros.com/2023/02/20/viva-engage-core-service-plan/), and there is no differentiation between system-initiated changes and administrator changes. So if you want a definitive date for when a human assigned a license, you need to consult the audit log (https://office365itpros.com/2022/10/14/azure-ad-license-assignment-report/) as that’s the only source I know of where this information can be found.
Hey Tony, first of all thanks for all your interesting and helpful posts.
I allowed myself to tune your script for creating the Products and Service Plans a little bit to save the step for the manual adjustment of the DisplayName.
I would be honored if you could include that in your script. I already made a pull request on your GitHub repository.
Regards,
Matthias
Thanks so much for your contribution. I hadn’t realized that Microsoft makes a downloadable CSV file of all the product identifiers. That’s quite a discovery and it makes the process much easier. I approved the pull request and merged the change into my repro.
Hi Tony, this is a jewel of a resource for you to share. Thank you! 🙂
Having gone through your code example, I was wondering why you would need to use the scope “Directory.ReadWrite.All”. Could this not have been handled using the similar ” Directory.Read.All”?
Very possibly because the SDK service principal already had Directory.ReadWrite.All… I think you’ll do just fine with Directory.Read.All.
In one of my tenants I get weird results, many users get duplicate or quadruple rows, only a few users get a single row in the report.
I have tested the scripts in 3 different tenants and it works fine in two of them, the third gives dubble or quadruple rows of almost all the users.
Any idea why this is happening?
Thanks in advance!
No idea off the top of my head. You’d have to debug the script to find out what’s happening. For instance, if you run Get-MgUser and review the AssignedLicenses property, does it report correct data in the tenant you have problems in?
The Get-MgUser only returns unique users and no duplicated SkuId´s in the AssignedLicenses property, so the problem must be at a later point in the script.
Well, obviously I can’t debug a script running against your data (unless you give me admin permissions for your tenant, which would be a bad idea). I’m afraid that you’ll have to do some debugging to find out where the problem is. The nice thing about PowerShell is that you have all the code and can easily run individual lines of the script to see what the response might be.
If I build the $Report without “Account created” it doesn’t create duplicates and quadruples.
When I run it with “Account created” it gives errors on some users.
###########################################################################
Get-Date : Cannot bind parameter ‘Date’ to the target. Exception setting “Date”: “Cannot convert null to type “System.DateTime”.”
At line:48 char:42
+ … “Account created” = Get-Date($User.CreatedDateTime) -format …
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-Date], ParameterBindingException
+ FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.PowerShell.Commands.GetDateCommand
###########################################################################
And if I run
Get-MgUser -Filter “assignedLicenses/`$count ne 0 and userType eq ‘Member'” -ConsistencyLevel eventual -CountVariable Records -All | Select DisplayName,CreatedDateTime | `
Sort-Object DisplayName
I can clearly see that a bunch of users don’t have a CreatedDateTime value.
This somehow makes duplicates and quadruples in the $Report variable.
It’s odd to have accounts without a creation date. In any case, I added this to the script just before the line that says # Report Information
$AccountCreatedDate = $Null
If ($User.CreatedDateTime) {
$AccountCreatedDate = Get-Date($User.CreatedDateTime) -format g }
and changed the line that inserts the account creation date into the report
“Account created” = $AccountCreatedDate
You could try that.
The fix for accounts without creationdate worked perfectly, thanks a lot!
I got it, what I was doing wrong, was not reading this one completely. We need to rename these 2 files like mentioned and use these 2 in the script, the moment I did that it worked, thanks! In all you should have 3 csv files and one html in temp folder (if newly created) for this data after running the script.
It always pays to read the complete text of an article…
get-mguser
Id, displayname, mail,UserPrincipalName as a default properties
If I do select *license*; then Asigned Licenses with some GUID.
Hi Eetu,
I have the same problem. It returns Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense,
Using beta!
issue is with only Licenses, the disabled plans show the guid
What returns the value? Get-MgUser?
Is there a way in the HTML report information section at the bottom to list totals for each license type?
Well, you could do something like this and include the output in the report:
report | group licenses | sort count, name | ft count, name
Count Name
—– —-
1 Enterprise Mobility and Security E5, Office 365 E3, Viva Topics, Power BI Standard (free)
1 Microsoft Flow Free, Office 365 E5 without Audio Conferencing
1 Microsoft Flow Free, Rights Management Ad-Hoc, Office 365 E3, Viva Topics
1 Office 365 E3, Microsoft Flow Free
1 Office 365 E3, Viva Topics, Microsoft Flow Free, Rights Management Ad-Hoc
1 Office 365 E3, Viva Topics, Power BI Standard (free), Microsoft Flow Free, Enterprise Mobility and Security E5
1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free
1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free, Power BI Standard (free)
1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free, Power BI Standard (free), Enterpri…
1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Stream, Rights Management Ad-Hoc
1 Power BI Standard (free), Rights Management Ad-Hoc
1 Rights Management Ad-Hoc, Microsoft Teams Exploratory, Power BI Standard (free), Microsoft Flow Free, Microsof…
1 Viva Topics, Enterprise Mobility and Security E5, Office 365 E3
1 Viva Topics, Office 365 E3
2 Office 365 E3, Viva Topics
2 Office 365 E3, Viva Topics, Microsoft Flow Free
14 Office 365 E3
This is also possible as it gives you the overall count of consumed units per license.
Get-MgSubscribedSku | Format-Table SkuId, SkuPartNumber, ConsumedUnits
SkuId SkuPartNumber ConsumedUnits
—– ————- ————-
1f2f344a-700d-42c9-9427-5cea1d5d7ba6 STREAM 2
b05e124f-c7cc-45a0-a6aa-8cf78c946968 EMSPREMIUM 4
4016f256-b063-4864-816e-d818aad600c9 TOPIC_EXPERIENCES 14
6fd2c87f-b296-42f0-b197-1e91e994b900 ENTERPRISEPACK 25
f30db892-07e9-47e9-837c-80727f46fd3d FLOW_FREE 11
a403ebcc-fae0-4ca2-8c8c-7a907fd6c235 POWER_BI_STANDARD 6
26d45bd9-adf1-46cd-a9e1-51e9a5524128 ENTERPRISEPREMIUM_NOPSTNCONF 5
710779e8-3d4a-4c88-adb9-386c958d1fdf TEAMS_EXPLORATORY 1
90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96 SMB_APPS 0
093e8d14-a334-43d9-93e3-30589a8b47d0 RMSBASIC 0
8c4ce438-32a7-4ac5-91a6-e22ae08d9c8b RIGHTSMANAGEMENT_ADHOC 5
Just because it’s the holiday season and everyone is feeling jolly, I updated the report script to include a SKU summary usage section in the HTML output. Enjoy!
Hi Tony,
the script itself is working and is displaying (almost) all data perfectly fine. But the “Licences” column in the report file doesn’t show anything. User with multiple licences do have a “,” in their respective row. So the script does recognize the values but can’t display them. Any advice?
Did you create the lookup files for the license and service plan names?
Thanks for the fast reply!
Yes. I renamed the initially created files to SkuDataComplete & ServicePlanDataComplete.
If they have different names the script breaks with the correct error code “Can’t find the xxx Data File”.
Do this:
1. Connect to the Graph and Select-MgProfile Beta
2. Use Get-MgUser to populate the $User variable with details of a user who has some licenses.
3. Run this code (extracted from the script) to see what happens
If ([string]::IsNullOrWhiteSpace($User.AssignedLicenses) -eq $False) { # Only process account if it has some licenses
Write-Host “Processing” $User.DisplayName
[array]$LicenseInfo = $Null; [array]$DisabledPlans = $Null
ForEach ($License in $User.AssignedLicenses) {
If ($SkuHashTable.ContainsKey($License.SkuId) -eq $True) { # We found a match in the SKU hash table
$LicenseInfo += $SkuHashTable.Item($License.SkuId) }
Else { # Nothing doing, so output the SkuID
$LicenseInfo += $License }
# Report any disabled service plans in licenses
If ([string]::IsNullOrWhiteSpace($License.DisabledPlans) -eq $False ) { # Check if disabled service plans in a license
ForEach ($DisabledPlan in $License.DisabledPlans) { # Try and find what service plan is disabled
If ($ServicePlanHashTable.ContainsKey($DisabledPlan) -eq $True) { # We found a match in the Service Plans hash table
$DisabledPlans += $ServicePlanHashTable.Item($DisabledPlan) }
Else { # Nothing doing, so output the Service Plan ID
$DisabledPlans += $DisabledPlan }
} # End ForEach disabled plans
} # End if check for disabled plans
} # End Foreach Licenses
$LicenseInfo should store the license information. This is what I see when I run the code for a user:
Processing James Ryan
PS> $LicenseInfo
Office 365 E3
Viva Topics
Microsoft Flow
Basically, SkuDataComplete.csv is missing “DisplayName” column which is generated by the script which extracts licensing data for the tenant.
Please correct line 14 in “CreateCSVFilesForSKUsAndServicePlans.ps1”:
$Skus | Select SkuId, SkuPartNumber, DisplayName | Export-Csv -NoTypeInformation c:\Users\adminmxr\Downloads\SkuDataCompletev2.Csv
Aha. I see what happened. The text contains a warning that the file must be updated with display names but it’s not very clear. I’ve amended the text for the script (so that the column exists in the CSV file) and the descriptive text. Thanks!
Is there advice for this error? I both consented to the needed permissions directly in Graph AND an App Reg in Azure (don’t know if needed) –
Finding licensed Azure AD accounts…
Get-MgUser : Calling principal does not have required MSGraph permissions AuditLog.Read.All
You need to consent to the Graph AuditLog.Read.All permission.
Connect-MgGraph -Scope AuditLog.Read.All should do the trick.
Hi Tony,
thank you for the script: works like a charm!
Is there in your opinion a way to have in the output file the Enabled service plans instead of the Disabled ones?
Thank you.
MQ
I love the Office 365 for IT Pros eBook because I write something down there that I am bound to forget and then someone asks me a question like this… Here’s how to find the service plans assigned to a user account:
[array]$AllLicenses = Get-MgUserLicenseDetail -UserId Kim.Akers@Office365itpros.com | Select-Object -ExpandProperty ServicePlans | Sort-Object ServicePlanId -Unique
$AllLicenses | ? {$_.ProvisioningStatus = “Success”}
$AllLicenses
AppliesTo ProvisioningStatus ServicePlanId ServicePlanName
——— —————— ————- —————
User Success 041fe683-03e4-45b6-b1af-c0cdc516daee POWER_VIRTUAL_AGENTS_O365_P2
User Success 0feaeb32-d00e-4d66-bd5a-43b5b83db82c MCOSTANDARD
Company Success 113feb6c-3fe4-4440-bddc-54d774bf0318 EXCHANGE_S_FOUNDATION
User Success 14ab5db5-e6c4-4b20-b4bc-13e36fd2227f ATA
User Success 17ab22cd-a0b3-4536-910a-cb6eb12696c0 DYN365_CDS_VIRAL
User Success 199a5c09-e0ca-4e37-8f7c-b05d533e1ea2 MICROSOFTBOOKINGS
User Success 2049e525-b859-401b-b2a0-e0a31c4b1fe4 BI_AZURE_P0
User Success 2789c901-c14e-48ab-a76a-be334d9d793a FORMS_PLAN_E3
Company Success 2b815d45-56e4-4e3a-b65c-66cb9175b560 ContentExplorer_Standard
User Success 2e2ddb96-6af9-4b1d-a3f0-d6ecfd22edb2 ADALLOM_S_STANDALONE
User Success 31b4e2fc-4cd6-4e7d-9c1b-41407303bd66 PROJECT_O365_P2
User Success 33c4f319-9bdd-48d6-9c4d-410b750a4a5a MYANALYTICS_P2
User Success 41781fb2-bc02-4b7c-bd55-b576c07bb09d AAD_PREMIUM
User Success 43de0ff5-c92c-492b-9116-175376d08c38 OFFICESUBSCRIPTION
User Success 4ff01e01-1ba7-4d71-8cf8-ce96c3bbcf14 DYN365_CDS_O365_P2
User Success 50e68c76-46c6-4674-81f9-75456511b170 FLOW_P2_VIRAL
User Success 5136a095-5cf0-4aff-bec3-e84448b38ea5 MIP_S_CLP1
User Success 5689bec4-755d-4753-8b61-40975025187c RMS_S_PREMIUM2
User Success 57ff2da0-773e-42df-b2af-ffb7a2317929 TEAMS1
User Success 5dbe027f-2339-4123-9542-606e4d348a72 SHAREPOINTENTERPRISE
User Success 6c57d4b6-3b23-47a5-9bc9-69f17b4947b3 RMS_S_PREMIUM
User Success 7547a3fe-08ee-4ccb-b430-5077c5041653 YAMMER_ENTERPRISE
User Success 76846ad7-7776-4c40-a281-a386362dd1b9 FLOW_O365_P2
Company Success 882e1d05-acd1-4ccb-8708-6ee03664b117 INTUNE_O365
User Success 8a256a2b-b617-496d-b51b-e76466e88db0 MFA_PREMIUM
User Success 8c7d2df8-86f0-4902-b2ed-a0458298f3b3 Deskless
Company Success 94065c59-bc8e-4e8b-89e5-5138d471eaff MICROSOFT_SEARCH
User Success 94a54592-cd8b-425e-87c6-97868b000b91 WHITEBOARD_PLAN2
User Success 95b76021-6a53-4741-ab8b-1d1f3d66a95a CDS_O365_P2
User Success 9e700747-8b1d-45e5-ab8d-ef187ceec156 STREAM_O365_E3
User Success a23b959c-7ce8-4e57-9140-b90eb88a9e97 SWAY
User Success aebd3021-9f8f-4bf8-bbe3-0ed2f4f047a1 KAIZALA_O365_P3
User Success b737dad2-2f6c-4c65-90e3-ca563267e8b9 PROJECTWORKMANAGEMENT
User Success b74d57b2-58e9-484a-9731-aeccbba954f0 GRAPH_CONNECTORS_SEARCH_INDEX_TOPICEXP
User Success b76fb638-6ba6-402a-b9f9-83d28acb3d86 VIVA_LEARNING_SEEDED
User Success bea4c11e-220a-4e6d-8eb8-8ea15d019f90 RMS_S_ENTERPRISE
User Success c1ec4a95-1f05-45b3-a911-aa3fa01094f5 INTUNE_A
User Success c68f8d98-5534-41c8-bf36-22fa496fa792 POWERAPPS_O365_P2
User Success c815c93d-0759-4bb8-b857-bc921a71be83 CORTEX
User Success c87f142c-d1e9-4363-8630-aaea9c4d9ae5 BPOS_S_TODO_2
Company Success db4d623d-b514-490b-b7ef-8885eee514de Nucleus
User Success e95bec33-7c88-4a70-8e19-b10bd9d0c014 SHAREPOINTWAC
User Success eec0eb4f-6444-4f95-aba0-50c24d67f998 AAD_PREMIUM_P2
User Success efb87545-963c-4e0d-99df-69c6916d9eb0 EXCHANGE_S_ENTERPRISE
Hello,
Please kindly assist, how to run MS Visio usage report? My purpose is to know users, when they have last time used MS Visio in order to remove MS Visio licence from those, who are not using this app at all or use very rarely.
Thanks
Try https://office365itpros.com/2022/09/29/underused-accounts-report/
Hi Andrejs, i need same thing.
Did you found a solution?
Did you try the script I suggested?
Hi, great scripts thanks. At the moment the -Filter parameter seems broken, but removing that has worked for now.
Is it possible to run this at a partner tenant level and iterate through customer tenants?
Thanks again
There’s only one filter in the script and it works just fine. What filter are you referring to?
[Array]$Users = Get-MgUser -Filter “UserType eq ‘Member'” -All | Sort DisplayName
It is possible to run for multiple tenants. You’d need to connect to each tenant seperately to collect the data.
There appears to be an issue with the latest release that has broken the -Filter parameter:
We’ve traced the issue back to CSDL metadata that was used to generate the module. We’ve unlisted the broken versions as we wait for a fix to be made to the CSDL metadata in microsoftgraph/msgraph-metadata#208.
Please follow uninstallation steps below to remove the unlisted version, then reinstall the latest module (1.11.1)
So there’s no way to adapt the script to loop through each delegated tenant? That would be a real time-saver, not to mention only needing the partner credentials!
Thanks for the quick reply.
OK. I see the problem reported in https://github.com/microsoftgraph/msgraph-metadata/issues/208 (BTW, that’s a bug with an interim build that doesn’t occur in the current GA 1.11.1 release).
As to looping through delegated tenants, I am sure that this is possible. However, as I don’t have any delegated tenants to work with, I can’t be definite.
Hi Tony
Thanks for the script , the script mentioned in [https://office365itpros.com/2020/03/18/quick-and-easy-office-365-license-assignment-report/] is giving license status of the users, but users having multiple licenses getting displayed with their license in multiple rows.
user1 ——- ENTERPRISEPREMIUM
user1 ——- AAD_PREMIUM
Is it possible to display the users having multiple licenses to gets displayed in single row like below?
user1 ——- AAD_PREMIUM,ENTERPRISEPREMIUM
Of course. You can join license details together with a -Join to produce a string containing all of the license names. In fact, the script mentioned in this article does just that in lines like:
[string]$DisabledPlans = $DisabledPlans -join “, ”
[string]$LicenseInfo = $LicenseInfo -join (“, “)
Hi Tony for the above script, I can get Licenses in a single line
But for the below Script, I am not able to do this
$Report = [System.Collections.Generic.List[Object]]::new() # Create output file
$Skus = Get-AzureADSubscribedSku | Select Sku*, ConsumedUnits
ForEach ($Sku in $Skus) {
Write-Host “Processing license holders for” $Sku.SkuPartNumber
$SkuUsers = Get-AzureADUser -All $True | ? {$_.AssignedLicenses -Match $Sku.SkuId}
ForEach ($User in $SkuUsers) {
$ReportLine = [PSCustomObject] @{
User = $User.DisplayName
UPN = $User.UserPrincipalName
Department = $User.Department
Country = $User.Country
SKU = $Sku.SkuId
SKUName = $Sku.SkuPartNumber}
$Report.Add($ReportLine) }}
$Report | Sort User | Out-GridView
Hi Tony for the above script, I can get Licenses in a single line.
But for the below Script, I am not able to do this.
$Report = [System.Collections.Generic.List[Object]]::new()
$Users = Get-MsolUser -All | where {$_.isLicensed -eq $true}
Write-Host “Processing Users”
ForEach ($User in $Users) {
$SKUs = @(Get-MsolUser -UserPrincipalName $User.UserPrincipalName | Select -ExpandProperty Licenses)
ForEach ($Sku in $Skus) {
$Sku = $Sku.AccountSkuId.Split(“:”)[1]
Switch ($Sku) {
“AAD_PREMIUM” { $License = “Azure AD Premium P1” }
“AAD_PREMIUM_P2” { $License = “Azure AD Premium P2” }
“ENTERPRISEPREMIUM” { $License = “Enterprise Mobility & Security E5” }
“AADPREMIUM” { $License = “Azure AD Premium P2” }
“ENTERPRISEPACK” { $License = “Office 365 E3” }
“ENTERPRISEPREMIUM_NOPSTNCONF” { $License = “Office 365 E5 No PSTN” }
default { $License = “Unlicensed” }
} #End Switch
$ReportLine = [PSCustomObject][Ordered]@{
User = $User.UserPrincipalName
SKU = $Sku -join(“,”)
License = $License
Name = $User.DisplayName
Title = $User.Title
City = $User.City
Country = $User.UsageLocation
Department = $User.Department
CreatedOn = Get-Date($User.WhenCreated) -Format g}
$Report.Add($ReportLine) }
}
Please don’t use the old AzureAD and MSOL cmdlets. They will stop working in early 2023. Use the Microsoft Graph PowerShell SDK cmdlets instead as I explained in the article. I can’t help you with the old cmdlets.
Sure Tony, Thanks for update 🙂 will work with PowerShell SDK cmdlets.
Thanks Tony, I missed this part …. but thanks for mentioning and spending some time on this.
Hi Tony,
I am looking for a way with the powershell graph module to access the information about a license expiration date. We have prepaid licenses in our tenant an would like to include the expiration date in a report.
Do you know if there is a way to get that information via powershell. It is available in the Admin Center.
I don’t think this is currently possible. I have looked for this data in the past and couldn’t find it. Maybe the data will be available after Microsoft 365 moves to the new licensing platform on August 26.
Thanks for this Tony – you are a treasure to the community!
Glad that it helps…
Switching to new tech is always easier with a good guide… thanks Tony.
I’m not a big fan of maintaining text files, so I changed that section to retrieve the friendly names on the fly… unsupported APIs can be risky, but this is read only so if it breaks, I can always go back to a file.
Connect-AzAccount -Tenant “tenantname.onmicrosoft.com”
$resource = ‘74658136-14ec-4630-ad9b-26e160ff0fc6’;
$token1 = (Get-AzAccessToken -ResourceUrl $resource -TenantId (Get-AzContext).Tenant.Id.ToString()).token
$accountskus = Invoke-RestMethod ‘https://main.iam.ad.ext.azure.com/api/AccountSkus’ -Headers @{Authorization = “Bearer $($token1)”; “x-ms-client-request-id” = [guid]::NewGuid().ToString(); “x-ms-client-session-id” = [guid]::NewGuid().ToString()}
$ImportSkus = $accountskus | select skuid,accountskuid,name | sort skuid -unique
$ImportServicePlans = $accountskus | %{$_.servicestatuses|%{$_.serviceplan|select serviceplanid,servicename,displayname}} | sort serviceplanid -unique
$SkuHashTable = @{}
ForEach ($Line in $ImportSkus) { $SkuHashTable.Add([string]$Line.SkuId, [string]$Line.Name) }
$ServicePlanHashTable = @{}
ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.DisplayName) }
I’m glad the post was helpful. All I can do is explain the relevant principles. After that, it’s up to you to decide how to apply the principles in the context of your organization and its business requirements.
Any idea how to get the expiration dates of licenses such as (Get-MsolSubscription).NextLifecycleDate?
Not off the top of my head. But I shall ask.
Thank you for this. I am trying to determine when a specific license, in this case an E3 Security and Mobility license, was added for all users (less than 100).
You could check the date registered for a service plan included in EMS E3, like Azure AD Premium P1. I’ll look into this a tad more.
(Update): I checked things out a little more and came up with https://office365itpros.com/2021/11/10/find-when-azure-ad-user-accounts-receive-microsoft-365-licenses/
Thanks Tony, now it works like a charm.
I have the same output as Eetu, I’m connected to the beta endpoint.
The “licenses” row shows “Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense”.
After line 41 ( $SkuHashTable ) I get the following:
Exception calling “Add” with “2” argument(s): “Item has already been added. Key in dictionary: ” Key being added: ””
MethodInvocationException:
Line |
11 | … icePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [ …
Have you the file c:\temp\ServicePlanDataComplete.csv with Service Plan information? Have you changed it to add something which would cause a duplicate in the hash table (you can’t have a duplicate key – the SKU Id is the key).
The serviceplandatacomplete looks like this:
“SkuId”,”SkuPartNumber”,”Displayname”
“c5928f49-12ba-48f7-ada3-0d743a3601d5″,”VISIOCLIENT”,”VISIO ONLINE PLAN 2″
The SKUdatacomplete looks like this:
“ServicePlanId”,”ServicePlanName”,”DisplayName”
“604ec28a-ae18-4bc6-91b0-11da94504ba9″,”TEAMS_ADVCOMMS”,”Microsoft 365 Advanced Communications”
The script depends on being able to load the contents of two CSV files into hash tables. After loading, the $SkuHashTable should look like this:
Name Value
—- —–
4fb214cb-a430-4a91-9c91-497… Microsoft Teams Rooms Premium
dab7782a-93b1-4074-8bb1-0e6… Microsoft 365 Business Basic
c52ea49f-fe5d-4e95-93ba-1de… Azure Information Protection P1
6a0f6da5-0b87-4190-a6ae-9bb… Windows 10 Enterprise E3
4b585984-651b-448a-9e53-3b1… Office 365 F3
And the $ServicePlanHashTable should look like this:
Name Value
—- —–
95b76021-6a53-4741-ab8b-1d1… Common Data Services Office 365 P2
8a256a2b-b617-496d-b51b-e76… Multi-factor authentication premium
94065c59-bc8e-4e8b-89e5-513… Microsoft Search
afa73018-811e-46e9-988f-f75… Common Data Services for Office 365 P3
531ee2f8-b1cb-453b-9c21-d21… Excel Premium
6db1f1db-2b46-403f-be40-e39… Customer Key
31b4e2fc-4cd6-4e7d-9c1b-414… Project for Office 365 P2
6dc145d6-95dd-4191-b9c3-185… Communications DLP
8c098270-9dd4-4350-9b30-ba4… Microsoft Cloud App Security for Office 365
If the hash tables don’t load for some reason, the script won’t be able to resolve SKUIds into display names or Service Plan Ids into display names. The CSV files are loaded in these lines:
$ImportSkus = Import-CSV c:\temp\SkuDataComplete.csv
$ImportServicePlans = Import-CSV c:\temp\ServicePlanDataComplete.csv
$SkuHashTable = @{}
ForEach ($Line in $ImportSkus) { $SkuHashTable.Add([string]$Line.SkuId, [string]$Line.DisplayName) }
$ServicePlanHashTable = @{}
ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.ServicePlanDisplayName) }
Can you execute the lines outside the script (the code is straightforward PowerShell and doesn’t need to be connected to the Graph or anything else)?
I assume you built the CSV files using the code in the article?
The csv was built from the code in the article, I think the line ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.ServicePlanDisplayName) } should be $Line2.DisplayName) } at the end, as there is no ServicePlanDisplayName in the csv.
After running the 2 import commands I end up with this:
$SkuHashTable
Name Value
—- —–
Microsoft 365 Advanced Communications
$ServicePlanHashTable
Name Value
—- —–
VISIO ONLINE PLAN 2
Could it be the separation character? Mine is , (comma)
I see what happened. I changed the name of the field in my CSV file when I was editing the display names for service plans to make them more readable. I’ve updated the article to make this clear. The contents of the service plan CSV file should look like the following (4 lines of data plus headers shown):
ServicePlanId ServicePlanName ServicePlanDisplayName
041fe683-03e4-45b6-b1af-c0cdc516daee POWER_VIRTUAL_AGENTS_O365_P2 Power Virtual Agents for Office 365 P2
0683001c-0492-4d59-9515-d9a6426b5813 POWER_VIRTUAL_AGENTS_O365_P1 Power Virtual Agents for Office 365 P1
07699545-9485-468e-95b6-2fca3738be01 FLOW_O365_P3 Flow for Office 365 P3
0898bdbb-73b0-471a-81e5-20f1fe4dd66e KAIZALA_STANDALONE Kaizala Standalone
Hi Brenkster,
I have the same problem. It returns Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense,
What have you done to fix this?
Thanks,
NP
Are you sure you have connected using the beta profile? The V1.0 endpoint doesn’t return license information.
Hi, very nice script. I’m not sure what I’m doing wrong I followed these steps but on the report on the “Licenses” blade I can see only Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense, Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense. Instead of friendly DisplayName I created as you suggested.
Are you connected to the beta endpoint? The license data is not returned by the V1.0 endpoint.
Thanks! This worked, also I suggest to run one line at a time and then check the outcomes to see everything works as expected. 🙂
Good to hear that you’re up and running. As to the suggestion, once you’ve created and updated the CSV files, there should be little need to go near them and they can be left alone. They should load accurately every time!
Hi Eetu,
I have the same problem. It returns Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense,
What have you done to fix this?
Thanks,
NP