Aug 29 2022 05:52 AM - edited Aug 29 2022 05:57 AM
Hi Guys,
I have a requirement to fetch Office 365 users based on a CSV file column (Display Name) and finally update the rest of the columns in it (UPN, First Name, Last Name, IsLicensed etc..) or, export as a brand new consolidated CSV. This is simply because my source CSV have limited info (comes from a different system) and we need it to be filled with more info from O365.
I am a very beginner to PowerShell and have tired the following but it doesn't fetch O365 field values (see the result below)
-----------------------------------------------------------------------------------
$Import = @(Import-Csv D:\Data-31072022\Automation\Source2.csv)
$DisplayName = $Import | Select-Object DisplayName
foreach ($item in $Import) {
$MS = Get-MsolUser -SearchString $_.DisplayName
New-Object PSObject -Property @{
'UserPrincipalName' = $MS.UserPrincipalName
'IsLicensed' = $MS.IsLicensed
'BlockCredential' = $MS.BlockCredential
'FirstName' = $MS.FirstName
'LastName' = $MS.LastName
'ArchiveID' = $item.ArchiveID
'DisplayName' = $item.DisplayName
}
} Select-Object ArchiveName,ArchiveID,UserPrincipalName,IsLicensed,BlockCredential,FirstName,LastName | Export-Csv -NoTypeInformation "D:\Data-31072022\Automation\NewOutput.csv"
--------------------------------------------------------------------------------------------
Result: no values returned for Msol user query
Script
Source CSV
Really appreciate if any of you can shed some lights here guys ! Thanks so much!
Aug 29 2022 01:10 PM
Solution@VidRocksKay I changed your script a little bit :)
$Import = Import-Csv D:\temp\users.csv -Delimiter ';'
$Total = @()
foreach ($item in $Import) {
$MS = Get-MsolUser -SearchString $Item.DisplayName
$msoluser = [PSCustomObject]@{
'UserPrincipalName' = $MS.UserPrincipalName
'IsLicensed' = $MS.IsLicensed
'BlockCredential' = $MS.BlockCredential
'FirstName' = $MS.FirstName
'LastName' = $MS.LastName
'ArchiveID' = $item.ArchiveID
'DisplayName' = $item.DisplayName
}
$Total += $msoluser
}
$Total | Select-Object DisplayName, ArchiveID, UserPrincipalName, IsLicensed, BlockCredential, FirstName, LastName | Export-Csv -NoTypeInformation "D:\Temp\NewOutput.csv" -Delimiter ';' -Encoding UTF8
Changed it to a pscustomobject and added every item to a Total variable and saved it to a .csv (Used ; as delimiter, for you it could be a , ) You also had a ArchiveName but I didn't see it in your screenshot, so I removed if for testing but you could add it again :)
My users.csv file:
Displayname; ArchiveID
Alex Wilber;1
Allan Deyoung;2
I ran this in my CDX environment for these two users, Excel output:
Aug 29 2022 10:14 PM - edited Aug 29 2022 10:17 PM
@Harm_Veenstra
Thank you so very much and this worked like charm ! Helped me a lot getting this sorted.
If you happen to spare some time, will you kindly be able to help me with adding another source or two combining to the same script? (i.e. ExchangeOnline/Exchange On-Premise in a Hybrid scenario)
##Exchange Online (Hybrid)-----------------------------
$EXO = Get-EXOMailbox -Identity $item.UserPrincipalName
$exouser = [PSCustomObject]@{
'RecipientTypeDetails' = $EXO.RecipientTypeDetails
'Alias' = $EXO.Alias
#Exchange On-Premise (Hybrid)-----------------
foreach ($item in $Import) {
$EX = Get-Mailbox -Identity $item.UserPrincipalName
'ArchiveStatus' = $EX.ArchiveStatus
'ArchiveState' = $EX.ArchiveState
Thank you heaps again :) !!
Aug 30 2022 03:52 AM
Sep 02 2022 06:10 AM