Search code examples
powershellexchange-online

Exporting a Report from Exchange-Online

I am trying to put a script together that does a series of things...

Lookup all accounts over 40GB Look into those accounts and report back all folders/subfolders over 1GB.

I currently build it by hand and it looks as follows.

enter image description here

The current script I am using is as follow.

# Path to the CSV file
$ExportPath = "C:\EXO\40GBFile.csv"

# Get mailboxes with total item size greater than 40GB
$Mailboxes = Get-EXOMailbox -ResultSize Unlimited | Get-EXOMailboxStatistics | Where-Object { $_.TotalItemSize.Value.ToBytes() -gt "40GB" } | Select-Object DisplayName

foreach ($Mailbox in $Mailboxes) {
    # Get detailed folder statistics for the mailbox
    $MailboxStats = Get-EXOMailboxFolderStatistics -Identity $Mailbox.DisplayName

    # Sort folders based on total size and select the top 5
    $TopFolders = $MailboxStats | Sort-Object TotalFolderSize -Descending | Select-Object -First 5

    # Create a custom object with the desired properties
    $OutputObject = [PSCustomObject]@{
        DisplayName = $Mailbox.DisplayName
        ItemCount = $Mailbox.ItemCount
        TotalItemSize = $Mailbox.TotalItemSize
        TopFolders = $TopFolders | Select-Object FolderName, ItemsInFolder, TotalFolderSize
    }

    # Output to console (optional)
    Write-Output $OutputObject

    # Export to CSV
    $OutputObject | Export-Csv -Path $ExportPath -Append -NoTypeInformation
}

Write-Host "Export complete. Results are saved to: $ExportPath"

Unfortunately it doesn't pick up the right data and comes through blank on all fields not the user and when showing in PowerShell it looks like the following.

enter image description here

I have tried a couple different approaches and most of them error or come out blank in one form or another... This one appears to have been the closest I have come. I do know that the above does not do size, but I could not get that to work and thought top 5 largest folders should work if all else fails.

Solution

  • This line here kills your rich mailbox item with an object that has a single property, displayname.

    $Mailboxes = Get-EXOMailbox -ResultSize Unlimited | Get-EXOMailboxStatistics | Where-Object { $_.TotalItemSize.Value.ToBytes() -gt "40GB" } | Select-Object DisplayName

    That's what Select-Object Displayname is doing. Then later you try to extract $mailbox.items but that is not a valid member. I recommend you break the commands down one at a time and make sure it has the data you expect. Run the command and then look at $mailboxes, you'll see just displayname. Removing the Select-Object on the end will be a great step in the right direction.

    The next issue is $Mailboxstats does not have a property TotalFileSize If you look at members, you'll see it's filesize.

    $Mailboxstats | Get-Member

    It's starting to feel like this script was written by AI. If not, where did you get the idea to sort by TotalFileSize? Maybe there is some incorrect documentation out there.

    Finally, the next issue is something you'll need to consider and decide how to handle. The topfolders will be another set of objects, this won't export to csv properly. You'll need to determine if you want to try and store all these folders in one property and if so, how to represent that data in a single field per user.