I believe those are the custom attributes from Exchang. It should work fine.

Hello,
I meant extenstionAttributes synced from on-prem AD to Entra ID.
I’ll try do it myself. 🙂

What kind of extension attributes? The Entra ID directory extensions (https://practical365.com/directory-extensions-entra-d/) or the custom attributes synchronized from Exchange? In either case, I won’t add them to the script because every organization is different. However, you certainly can add whatever attribute you like by fetching the attributes in the Get-MgUser cmdlet and making sure that the data is output to the report.

Hi Tony,
I’ve come with another question…
Is it possible for you to add extensionAttributes in script? We use certain extensionAttributes to add cost center for each user… It would be huge to have every user with cost center exported just like department is exported in .csv and .html…

Indeed, I have 2 accounts where sign in data is not available. After your update it shows last sign in as “Unknown” and there is zero other errors. Thank you so much.

You must have some accounts where sign in data is not available… In any case, this problem is in the color formatting of the inactive user column, which depends on the number of daus since the last sign-in. I’ve added a check for the condition and all is well in V1.91, available from GitHub now.

Hello,
New version works flawless! There is no problems with departments anymore, if there is only one person in department now it shows number in “Accounts” 🙂

One last thing is that there are two errors at the end of script – https://imgur.com/QdVmOpN
They were here before – when I send you previous screenshots. They do not cause problems, but I attached them for your information.
Once more – thank you for this script, it is amazing.

TRy the version I just uploaded to GitHub. I didn’t cast the variables used for $departmentreport and $Countryreport as arrays. PowerShell can be funky about counts when only a single record is returned and the receiving variable isn’t an array… I also changed the match statement to a more conventional Where-Object comparison. Everything seems to work here:

$DepartmentReport

Department Accounts Costs AverageCost
———- ——– —– ———–
Business Development 2 US $ 735.60 US $ 367.80
CEO Office 1 US $ 277.80 US $ 277.80
Cloud Engineering 1 US $ 694.08 US $ 694.08
Cocaine 1 US $ 457.80 US $ 457.80
Editorial 1 US $ 277.80 US $ 277.80
Global HQ 2 US $ 1,451.88 US $ 725.94
Group HQ 1 US $ 277.80 US $ 277.80
Information Security 1 US $ 277.80 US $ 277.80
Information Technology 11 US $ 3,790.56 US $ 344.60
IT Operations 1 US $ 277.80 US $ 277.80
Marketing 1 US $ 277.80 US $ 277.80
Planning & Action 1 US $ 277.80 US $ 277.80
Project Management 2 US $ 735.60 US $ 367.80
Research and Development 2 US $ 555.60 US $ 277.80
Sales 3 US $ 833.40 US $ 277.80
Security Research 1 US $ 457.80 US $ 457.80
Services 1 US $ 694.08 US $ 694.08
Systems Operations 3 US $ 555.60 US $ 185.20

Hello Tony,
No, of course not. We have like ~40 different department names.
https://imgur.com/oWFZvnd – please see attachment.

When I run [array]$Departments = $Users.Department | Sort-Object -Unique there is every department listed.

Also – error screenshot – https://imgur.com/a/PBkAke5
There are lots of those errors, I guess it runs vs every licensed user account.

The list of departments is derived from the list of users…

[array]$Departments = $Users.Department | Sort-Object -Unique

If there’s only two departments, do you have only two unique values for department names?

I’ve run piece of script you’ve pasted and I’ve checked $Reports – it returns every user with department, UPN, Title, etc.
When I run $DepartmentRecords, there are only 2 entries on this list.

The error is coming when the report script attempts to calculate the cost of licenses assigned to individual departments:

$DepartmentReport = [System.Collections.Generic.List[Object]]::new()
ForEach ($Department in $Departments) {
$DepartmentRecords = $Report | Where-Object {$_.Department -eq $Department}
$DepartmentReportLine = [PSCustomObject][Ordered]@{
Department = $Department
Accounts = $DepartmentRecords.count
Costs = (“{0} {1}” -f $Currency, (‘{0:N2}’ -f ($DepartmentRecords | Measure-Object UserCosts -Sum).Sum))
AverageCost = (“{0} {1}” -f $Currency, (‘{0:N2}’ -f ($DepartmentRecords | Measure-Object UserCosts -Average).Average))
}
$DepartmentReport.Add($DepartmentReportLine)
}

You could check that the departments are correctly captured in the $Report list. You could then check that the $DepartmentRecords list is correct. If that list is not populated, the computations can’t work.

If you want to export your $Report to a CSV file and upload it to somewhere that I can check it, I will have a look.

I checked and there is whole bunch of department names. What I noticed, this error occurs only if I put price in “SkuDataComplete.csv” file. Without price for MS licenses, this error does not appear.

Department name in this error is really long, might it be a problem?

What values exist in the department property for user accounts? Type $Users.Department to see…

I don’t see this error here, so it’s likely due to something specific to your tenant.

And in .html file under “License Cost by Department” if there is only 1 person in department html shows nothing in “Accounts” tab.

If you will find to answer, I will be really grateful, thank you for your amazing work. 🙂

You’ve got to populate the CSV file with the prices your organization pays Microsoft for licenses. I have no knowledge of what you pay per license.

There are details in the script telling you how to generate the SKU and service plan CSVs. The original article (on Practical365.com) explains how by running a script (also available from GitHub). I’ve tweaked the text to make this more obvious.

There’s also details in the script:

# This step depends on the availability of some CSV files generated to hold information about the product licenses used in the tenant and
# the service plans in those licenses. See https://github.com/12Knocksinna/Office365itpros/blob/master/CreateCSVFilesForSKUsAndServicePlans.PS1
# for code to generate the CSVs. After the files are created, you need to edit them to add the display names for the SKUs and plans.
# Build Hash of Skus for lookup so that we report user-friendly display names – you need to create these CSV files from SKU and service plan
# data in your tenant.