Public/Configuration/Export-Userlist.ps1
|
function Export-UserList { param ( [string]$TenantId ) Write-ModuleLog -Message "Exporting user list for tenant $TenantId" -Level Info Connect-CustomerGraph -CustomerTenantId $TenantId # Download and import SKU to Product Display name mappings Write-ModuleLog -Message "Downloading SKU mapping file" -Level Info $skuMappingUrl = "https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv" $skuMappingFile = "SKU_Mapping.csv" Invoke-WebRequest -Uri $skuMappingUrl -OutFile $skuMappingFile $skuMappings = Import-Csv -Path $skuMappingFile Write-ModuleLog -Message "Fetching all users" -Level Info $users = Get-MgUser -All -Property id, userType, userPrincipalName, displayName, companyName, mobilePhone, businessPhones, jobTitle, createdDateTime, Department, Country | Where-Object { $_.UserType -eq "Member" } # Define function to map SKU to Product Display Name function Get-ProductDisplayName { param( [string]$skuId ) $productDisplayName = $skuMappings | Where-Object { $_.GUID -eq $skuId } | Select-Object -ExpandProperty "Product_Display_Name" -First 1 return $productDisplayName } Write-ModuleLog -Message "Processing user data" -Level Info # Construct CSV header $header = "UserPrincipalName", "DisplayName", "Company", "Department", "Country", "Licenses", "MobilePhone", "PhoneNumber", "Title", "CreatedAt" $output = @() # Iterate through users and retrieve required information foreach ($user in $users) { $userPrincipalName = $user.UserPrincipalName $displayName = $user.DisplayName $company = $user.CompanyName $department = $user.Department $country = $user.Country $mobilePhone = $user.MobilePhone $phoneNumber = $user.BusinessPhones $title = $user.JobTitle $createdAt = $user.CreatedDateTime # Retrieve license details $licenseDetails = Get-MgUserLicenseDetail -UserId $user.Id $licenses = @() foreach ($licenseDetail in $licenseDetails) { $licenseName = Get-ProductDisplayName -skuId $licenseDetail.SkuId $licenses += $licenseName } # Convert phone numbers array to string $phoneNumberString = $phoneNumber -join ", " # Format the output $entry = [PSCustomObject]@{ "UserPrincipalName" = $userPrincipalName "DisplayName" = $displayName "Company" = $company "Department" = $department "Country" = $country "Licenses" = $licenses -join ", " "MobilePhone" = $mobilePhone "PhoneNumber" = $phoneNumberString "Title" = $title "CreatedAt" = $createdAt } $output += $entry } $Today = (Get-Date).ToString("dd-MM-yyyy") $desktopDir = [Environment]::GetFolderPath("Desktop") # Export the data to CSV $Path = "$($Global:Tenant.DefaultDomainName)_$($Today).xlsx" $outPath = Join-Path -Path $desktopDir -ChildPath $Path #Remove if exists if (Test-Path -Path $outPath) { Remove-Item -Path $outPath -Force } $xls = Export-Excel -InputObject $output -Path $outPath -WorksheetName "Users $($Today)" -AutoSize -AutoFilter -FreezeTopRow -BoldTopRow -PassThru Set-ExcelColumn -ExcelPackage $xls -Column 8 -NumberFormat "dd/MM/yyyy" -WorksheetName "Users $($Today)" Close-ExcelPackage $xls #Get full path to the file $Path = (Resolve-Path -Path $outPath).Path Remove-Item -Path $skuMappingFile -Force Write-ModuleLog -Message "Exported user list for tenant $TenantId to $Path" -Level Info } |