More efficient way to retrieve Office365 data via Powershell

Question

I am trying to get mailbox statistics from Office 365. This is the current script:

# Get credentials
$O365Creds = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList "reports@o365.example.com",$SecurePassword

# Create session
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionURI https://ps.outlook.com/powershell -Credential $O365Creds -Authentication Basic -AllowRedirection
Import-PSSession $O365Session -AllowClobber

# Create report
Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | FT @{n="UserID";e={(Get-Mailbox $_.LegacyDN).Name}},LastLogonTime | Out-File -FilePath o365_logons.csv -Encoding utf8 -append

Looking at the memory usage, seems like Get-Mailbox -ResultSize Unlimited is loaded into memory before pipping it; over 1GB of memory usage. Most of the time it just times out. This is terribly inefficient since I am only interested in two columns.

Anyone have any suggestions on how to complete this task in a more efficient way?

How many mailboxes? Are you just wanting lastlogontime of each user? — TheCleaner, Jun 24 '14 at 13:12
Belmin, your script is honestly no different overall than this one: http://gallery.technet.microsoft.com/office/Export-Office-365-User-7fc0b73f and Technet there recommends not running it on more than 3,000 mailboxes. I think you might have to consider breaking up the script to grab A-E, F-K, etc. — TheCleaner, Jun 24 '14 at 13:26
Any ideas on how to break it up? I was looking for a way to do it with `-ResultSize n` but not sure how to get the 2nd batch of `n`. — Belmin Fernandez, Jun 24 '14 at 13:53
You would filter for names that begin with A*. The ResultSize parameter is useless, as you can't really page with it... — MichelZ, Jun 24 '14 at 14:00

score 2 · Accepted Answer · answered Jun 24 '14 at 16:52

Per TheCleaner and MichelZ, I have modified the script to page the query by letter ranges:

# Create credentials
$O365Creds = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList "reports@o365.example.com",$SecurePassword

# Create session
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365Creds -Authentication Basic -AllowRedirection
Import-PSSession $O365Session -AllowClobber

# Initiate file
$CSVExport = "o365_logons.csv"
If (Test-Path $CSVExport){
    Remove-Item $CSVExport
}
"UserID,LastLogonTime" | Out-File -FilePath $CSVExport

# Loop through alphabet
foreach ($letter1 in [char]'a'..[char]'z') {
    foreach ($letter2 in [char]'a'..[char]'z') {
        $AccountNames = Get-Mailbox -Filter "{SamAccountName -like '$([char]$letter1)$([char]$letter2)*'}" -ResultSize Unlimited | Select -Expand Name

        # Skip if no accounts
        if (!$AccountNames) {
            Continue
        }

        foreach ($account in $AccountNames) {

            ## Some last logon could be null, using ForEach as workaround
            $last_logon = Get-MailboxStatistics -Identity $account | ForEach { $_.LastLogonTime }

            ## Print to CSV file
            $account,$last_logon -Join ','| Out-File -Append -FilePath $CSVExport
        }
    }
}

Will do a test run over night.

If anyone has any recommendations on how to make this more efficient or elegant, please comment.

score 1 · Answer 2 · answered Sep 15 '16 at 00:12

What I would do is this:

# Get credentials
$O365Creds = New-Object -Typename System.Management.Automation.PSCredential -ArgumentList "reports@o365.example.com",$SecurePassword

# Create session
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionURI https://ps.outlook.com/powershell -Credential $O365Creds -Authentication Basic -AllowRedirection
Import-PSSession $O365Session -AllowClobber

# Create report

$Mailboxes = Get-Mailbox -Resultsize Unlimited

foreach ($mailbox in $Mailboxes) {
    $mailboxstats = Get-MailboxStatistics
    $mailboxstats | Add-Member -MemberType NoteProperty UserID -Value $mailbox.Name
    $mailboxstats | Export-CSV o365_logons.csv -NoTypeInformation -Append
    }

The original code takes a stream of objects, and for each one simultaneously strips it from being an object, takes one or two properties, pulls in another object for one property, combines all of that into an object, then immediately sends it to format-table, which strips it from being an object. Then uses out-file to send to a CSV.

Instead, this code takes the array of mailbox objects into an array. Then looping through them pulls the Mailboxstats object for each one, adds the property from the mailbox (UserID) to the stats object, and dumps the whole thing into a CSV using Export-CSV.

Even if you don't use the whole mailbox stats object, you should at least use the Foreach-Object loop, build a custom object with the data you want, and then export to CSV using Export-CSV.

The foreach loop, as used here, sends one object to the scriptblock at a time, thus reducing amount of data in the pipeline.

Building a custom object to gather the data you want from multiple sources, including other objects, is a powerful tool.

Export-CSV is the right tool to export a csv. Out-File seems clunky and fraught with danger.

More efficient way to retrieve Office365 data via Powershell

2 Answers2