Users and Shared mailboxes within one Excel file using powershell

Question

Hello Stack overflow community,

The past week I have been busy to make a way for my company to extract data from Azure AD and put this in a single .xslx file. But after repeatedly trying I couldnt find a common ground to make this happen.

The data I wanted to extract were users with licenses and shared mail boxes, so that out customers can see who they are still paying for.

I have used Msol mostly since it felt like the easiest way to achieve it (or im just stubborn) but the biggest problem is that I dont know or find anything that I can use to change foreach ($license in $licenses) .

With this piece of code I have gotten close but im not quite there yet. I also dont have any prior expiernce with Powershell before writing this code.

$groupOfUsers = Get-MsolUser -all | where { $_.IsLicensed -eq $True }

$o365Results = foreach ($user in $groupOfUsers) {

$licenses = $user.licenses.accountskuid
foreach ($license in $licenses) {
    [pscustomobject]@{
    Mailboxnaam = $user.Name
    Naam = $user.FirstName
    Achternaam = $user.LastName
    Alias = $user.Alias
    Database = $user.Database
    Provincie = $user.state         
    License = $license
 }
}
}
$groupOfUsers = Get-Mailbox -filter {recipienttypedetails -eq "SharedMailbox"}

$mailboxResults = foreach ($user in $groupOfUsers) 
{[pscustomobject]@{
    }
}

$combinedResults = $mailboxResults + $o365Results

$combinedResults | Export-Excel -Path C:\Temp\CombinedResults.xlsx -AutoSize -AutoFilter

I have been looking all over the internet but i cant find a place where i can compare certain pieces of code like $license. The end result we want is a single file where we can see the shared mailboxes and every active user.

I do want to mention that the values from both outputs are different from each other. This might mean that its not even possible.

Is there a way to make an .xlsx file containing the information of both outputs?

Answer 1

After Doug Maurer told me about worksheets i incorporated them into my code which immidiatly fixed my issue. Here is my code if anyone wants to take a look!

Connect-AzureAD Connect-MsolService Connect-ExchangeOnline


$groupOfUsers = Get-MsolUser -all | where { $_.IsLicensed -eq $True }

$results = foreach ($user in $groupOfUsers) {
    $licenses = $user.licenses.accountskuid
    foreach ($license in $licenses) {
        [pscustomobject]@{
            Voornaam = $user.FirstName
            Achternaam= $user.LastName
            License = $license
            Status = $user.state
        }
    } }

$results|Export-Excel -Path C:\Temp\CombinedResults.xlsx -AutoSize
-AutoFilter -WorksheetName 'Gebruikers'

$groupOfUsers = Get-Mailbox -filter {recipienttypedetails -eq "SharedMailbox"}

$results = foreach ($user in $groupOfUsers) {
    [pscustomobject]@{
        MailboxNaam = $user.Name
        Alias = $user.Alias
        Database = $user.Database
    } }

$results |Export-Excel -Path C:\Temp\CombinedResults.xlsx -AutoSize
-AutoFilter -WorksheetName 'Mailboxen'

Thanks again to Doug Maurer for setting me on the correct path!