CSV file could have just one column with header named “ID”.
ID is SamAccountName of a user.
License groups names should be part of DN (DistinguishedName) – line 13 and 14 (in this case we will use 2 license groups).
You will need Import-Excel power shell module to create report.
Clear-Host
Import-Module ActiveDirectory
Set-ADServerSettings -ViewEntireForest $true
# Global variables
$date = Get-Date -Format ddmmyy-hhmmss
$file = "$env:USERPROFILE\Desktop\list.csv"
$list = Import-CSV -Path $file
$export = "$env:USERPROFILE\Desktop\LicReport\LicenseReport-$date.xlsx"
$logs = "$env:USERPROFILE\Desktop\LicReport\Logs\LicenseReport-$date.txt"
# License groups - DN
$E3group = "CN=EXC-CLOUD-Office365_Licensetype_Exchange_Online-E3*"
$P2group = "CN=EXC-CLOUD-Office365_Licensetype_Exchange_Online_P2*"
### --- FUNCTION --- ###
$data = foreach ($u in $list) {
$userID = $($u.ID)
$E3license = "NO"
$P2license = "NO"
$NFuser = "-"
$license = "-"
$UPN = "-"
Write-Progress -Activity "Working on: $($u.ID)"
$UPN = (Get-ADUser $($u.ID)).UserPrincipalName
Try {
If ((Get-ADUser -identity $userID -Properties memberof).memberof -like $E3group) {
Write-Output "$userID - E3 license is assigned" | Out-File -FilePath $logs -Append
$E3license = "YES"
}
ElseIf ((Get-ADUser -identity $userID -Properties memberof).memberof -like $P2group) {
Write-Output "$userID - P2 license is assigned" | Out-File -FilePath $logs -Append
$P2license = "YES"
}
ElseIf ($null -eq (Get-ADUser -Identity $userID)) {
Write-Output "$userID - User not found" | Out-File -FilePath $logs -Append
$NFuser = "User Not Found"
}
Else {
Write-Output "$userID - NO license is assigned" | Out-File -FilePath $logs -Append
$license = "YES"
}
}
Catch {
Write-Output "$userID - User not found" | Out-File -FilePath $logs -Append
$license = "User not found"
}
[PSCustomObject]@{
ID = $userID
UPN = $UPN
E3_License = $E3license
P2_License = $P2license
NoLicense = $license
UserNotFound = $NFuser
}
}
# Export data to Excel sheet
$data | Export-Excel -path $export -FreezeTopRow -BoldTopRow -AutoSize -AutoFilter
Write-Host "Script finished." -ForegroundColor Green
License report will look like this.
