Generating a combined CSV from multiple sources (currently using 3 separate steps)

Copper Contributor

Hi Guys, 

 

PowerShell Newbie here :). I have a script (@Harm_Veenstra helped me a lot in this post - to get this piece done) to collect field values from multiple sources referring to a source CSV file. But I'm struggling to combine all these in to a single script that I can run one go. 

 

Sample Source CSV file: 

VidRocksKay_0-1662124345248.png

 

Sample Final Output after 3rd script: 

VidRocksKay_1-1662124653755.png

 

Step1: generates a CSV with values filled from  Office 365 MSolUser command 

 

Connect-MSolService

$Import = Import-Csv "D:\Data\Automation\Source\Batch1-run1.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, RecipientTypeDetails, Alias, ArchiveStatus, ArchiveState, ArchiveQuota, ArchiveDatabase, ArchiveDatabaseGuid, AutoExpandingArchiveEnabled | Export-Csv -NoTypeInformation "D:\Data\Automation\Output\CombinedOutputWithMsol-Batch1.csv" -Delimiter ',' -Encoding UTF8

 

 

Step2: Then Using above generated CSV as Source and fill the rest of the fields using ExchangeOnline commands

 

 

Connect-ExchangeOnline

$Import = Import-Csv "D:\Data\Automation\Output\CombinedOutputWithMsol-Batch1.csv" -Delimiter ','
$Total = @()
foreach ($item in $Import) {
    $EXO = Get-EXOMailbox -Identity $item.UserPrincipalName 

    $exouser = [PSCustomObject]@{
        'RecipientTypeDetails' = $EXO.RecipientTypeDetails
        'Alias'                = $EXO.Alias
        'UserPrincipalName'    = $item.UserPrincipalName
        'IsLicensed'           = $item.IsLicensed
        'BlockCredential'      = $item.BlockCredential
        'FirstName'            = $item.FirstName
        'LastName'             = $item.LastName
        'ArchiveID'            = $item.ArchiveID
        'DisplayName'          = $item.DisplayName
    }
    $Total += $exouser
}
$Total | Select-Object DisplayName, ArchiveID, UserPrincipalName, IsLicensed, BlockCredential, FirstName, LastName, RecipientTypeDetails, Alias, ArchiveStatus, ArchiveState, ArchiveQuota, ArchiveDatabase, ArchiveDatabaseGuid, AutoExpandingArchiveEnabled | Export-Csv -NoTypeInformation "D:\Data3-31072022\Automation\Output\CombinedOutputWithEXO-Batch1.csv" -Delimiter ',' -Encoding UTF8

 

 

Step3: Then as the final step running this to fetch Exchange On-premise fields 

 

$Import = Import-Csv "D:\Data\Automation\Output\CombinedOutputWithEXO-Batch1.csv" -Delimiter ','
$Total = @()
foreach ($item in $Import) {
    $EX = Get-Mailbox -Identity $item.DisplayName 

    $exuser = [PSCustomObject]@{
        'ArchiveStatus'               = $EX.ArchiveStatus
        'ArchiveState'                = $EX.ArchiveState
        'ArchiveQuota'                = $EX.ArchiveQuota
        'ArchiveDatabase'             = $EX.ArchiveDatabase
        'ArchiveDatabaseGuid'         = $EX.ArchiveDatabaseGuid
        'AutoExpandingArchiveEnabled' = $EX.AutoExpandingArchiveEnabled
        'RecipientTypeDetails'        = $item.RecipientTypeDetails
        'Alias'                       = $item.Alias
        'UserPrincipalName'           = $item.UserPrincipalName
        'IsLicensed'                  = $item.IsLicensed
        'BlockCredential'             = $item.BlockCredential
        'FirstName'                   = $item.FirstName
        'LastName'                    = $item.LastName
        'ArchiveID'                   = $item.ArchiveID
        'DisplayName'                 = $item.DisplayName
    }
    $Total += $exuser
}

$Total | Select-Object DisplayName, ArchiveID, UserPrincipalName, IsLicensed, BlockCredential, FirstName, LastName, RecipientTypeDetails, Alias, ArchiveStatus, ArchiveState, ArchiveQuota, ArchiveDatabase, ArchiveDatabaseGuid, AutoExpandingArchiveEnabled  | Export-Csv -NoTypeInformation "D:\Data\Automation\Output\CombinedOutputWithEXOAndArchive-Batch1.csv" -Delimiter ',' -Encoding UTF8

 

 

Appreciate if any of you can help out enhancing this script to combine these 3 levels in to one single script to generate the CSV with all fields efficiently ! 

 

Thanks heaps in advance folks !

 

1 Reply
Here you go:
https://github.com/Raindrops-dev/RAIN-TechCommunityContributions/blob/main/Get-CombinedExchangeGraph...

Sadly I'm unable to test the Exchange Onpremise part as I don't have an Exchange Onpremise server to test with.

I replaced the MSOL params with Graph API because of the soon upcoming deprecation: https://github.com/Raindrops-dev/RAIN-TechCommunityContributions/blob/main/Get-CombinedExchangeGraph...

The license processing is a bit complicated since there is no IsLicensed but it gives a list of GUIDs of the various licenses assigned, so I checked if that parameter was empty to create the IsLicensed variable.