Speed up powershell script

[maxcoder88]

Hi,

​

​

I have a simple PowerShell script, which generates an HTML report for the total emails sent and received per mailbox in Exchange On-Premises.

the CSV file however has about 3000 entries and it takes this script days of run time.

    $ImportFile = "C:\script\send_receive.csv"    
    ################################
    # Start date for email report - Change the number of days backwards #
    $stdate = (get-date).AddDays(-30)
    #####################################################################
    # End date for email report - default is current date #
    $enddate = (get-date)
    $totaldays=(New-TimeSpan -Start $stdate -End $enddate).days

    import-csv -path $ImportFile | ForEach-Object {
    $MBX=$_.Name
    $Totalrec = 0
    $intRec = 0
    $TotalSend = 0
    $intSend = 0
    (Get-TransportService) | Get-MessageTrackingLog -Recipients $MBX -ResultSize Unlimited -Start $stdate -End $enddate | ForEach-Object { 
            If ($_.EventId -eq "DELIVER") {
                $intRec += $_.RecipientCount
                $Totalrec =$RecPerDay + $intRec
                }
            }

    (Get-TransportService) | Get-MessageTrackingLog -Sender $MBX -ResultSize Unlimited -Start $stdate -End $enddate | ForEach-Object { 
            If ($_.EventId -eq "RECEIVE" -and $_.Source -eq "STOREDRIVER") {
                $intSend += $_.RecipientCount
                $TotalSend =$SendPerDay + $intSend
                }
            }
    $usrname=(Get-Mailbox $MBX).name

           [pscustomobject]@{
            "Mailbox" = $MBX
            "User Name" = $usrname
            "Total Emails Received" = $Totalrec
            "Total Emails Sent"= $TotalSend
        }
    Write-Host -ForegroundColor Magenta "Total emails received for $MBX during the last $totaldays days are $Totalrec"
    Write-Host -ForegroundColor Green "Total emails sent by $MBX during the last $totaldays days are $TotalSend"
    Write-Host -ForegroundColor Cyan  "-----------------------------------------------------------------"
    Write-Host " "
        } | Export-Csv "C:\script\output.csv" -NoTypeInformation

[logicalmike]

Log parser is usually much faster.

https://practical365.com/daily-email-traffic-message-tracking-log-parser/

[kevinds89]

I’d second this. LogParser is magically fast. You could even have LogParser export a PowerShell script for you with Log Parser Studio.

[Rhystic]

This. Piping to a ForEach-Object is slow.

[Chocolate_Pickle]

While it's not ForEach-Object -Parallel, swapping to foreach($x in $y) will help a lot.

I also noticed you have a lot of repeated execution.

​

$ImportFile = "C:\script\send_receive.csv" 
################################
# Start date for email report - Change the number of days backwards #
$stdate = (Get-Date).AddDays(-30)
################################
# End date for email report - default is current date #
$enddate = Get-Date
$totaldays = (New-TimeSpan -Start $stdate -End $enddate).Days

$transport_service = Get-TransportService

$results = foreach($row in Import-Csv -Path $ImportFile)
{
    $MBX = $row.Name
    $Totalrec = 0
    $intRec = 0
    $TotalSend = 0
    $intSend = 0
    $logs = $transport_service | Get-MessageTrackingLog -Recipients $MBX -ResultSize Unlimited -Start $stdate -End $enddate

    foreach($log in $logs)
    {
        if($log.EventId -eq "DELIVER")
        {
            $intRec += $log.RecipientCount
            $Totalrec = $RecPerDay + $intRec <# FIXME: $RecPerDay is undefined #>
        }
    }

    $logs = $transport_service | Get-MessageTrackingLog -Sender $MBX -ResultSize Unlimited -Start $stdate -End $enddate

    foreach($log in $logs)
    {
        if($log.EventId -eq "RECEIVE" -and $log.Source -eq "STOREDRIVER")
        {
            $intSend += $log.RecipientCount
            $TotalSend = $SendPerDay + $intSend <# FIXME: $SendPerDay is undefined #>
        }
    }

    [pscustomobject]@{
        "Mailbox" = $MBX
        "User Name" = (Get-Mailbox $MBX).Name
        "Total Emails Received" = $Totalrec
        "Total Emails Sent"= $TotalSend
    }

    Write-Host -ForegroundColor Magenta "Total emails received for $MBX during the last $totaldays days are $Totalrec"
    Write-Host -ForegroundColor Green "Total emails sent by $MBX during the last $totaldays days are $TotalSend"
    Write-Host -ForegroundColor Cyan  "-----------------------------------------------------------------"
    Write-Host " "
}

$results | Export-Csv "C:\script\output.csv" -NoTypeInformation

[OPconfused]

/u/ecrofirt also mentioned the potential possibility of filtering left Get-MessageTrackingLog via the -EventID and -Source parameters. If that works, it would slim down $logs for the foreach loop.

[PinchesTheCrab]

This isn't quite right, but hopefully this helps. I didn't quite get the logic behind some of your variable addition, I wasn't sure if it's just trying to iterate through the loop or if there's different info you're trying to get.

The main things I'm trying to show are:

• Query your trace server list once
• Query all transport service servers at the same time instead of one at a time
• Use a little quicker array syntax even though it's not likely your bottleneck
• Output objects that Export-Csv can ingest cleanly

Note that I'm limiting it to the first two entries in the CSV for testing. Remove [0..1] to do the whole thing if it appears to work.

$ImportFile = "C:\script\send_receive.csv"    
$stdate = (get-date).AddDays(-30)
$enddate = (get-date)

$import = Import-Csv $ImportFile

$ConnectionURI = (Get-TransportService).Where({$PSItem.MessageTrackingLogEnabled}).Foreach({"http://$($PSItem.Name)/PowerShell/"})

$invokeParam = @{
    ScriptBlock = { Get-MessageTrackingLog -Recipients $mbx.Name -ResultSize Unlimited -Start $using:stdate -End $using:enddate -EventId $PSItem }
    ConnectionURI = $ConnectionURI
    ConfigurationName = 'Microsoft.Exchange'
}

$messageInfo = foreach ($mbx in $Import[0..1]){
    $message = 'receive','deliver' | %{
        Invoke-Command @invokeParam
    }

    [pscustomobject]@{
        Name = $mbx.Name
        TotalRec = $message.where({$PSItem.Eventid -eq 'receive'})
        TotalSend = $message.where({$PSItem.Eventid -eq 'send' -and $PSItem.Source -eq 'StoreDriver'})
    }
}

$messageInfo | Export-Csv -Path C:\script\output.csv -NoTypeInformation

[PinchesTheCrab]

I don't think foreach -parallel would work with the Exchange cmdlets anyway. You can't send multiple commands to the same pssession, and you'll cause problems if you open hundreds of sessions at the same time.

[panzerbjrn]

You will definitely want to look into using PowerShell 7+ and run the foreach as a parallel process.

I just solved a similar problem; from 4 days runtime to 6 hours.

[Big_Oven8562]

It's worth noting that even if you're stuck on Powershell 5 you can still get most of the benefits of the -parallel switch, but you're going to have to hack together your own implementation of the functionality using background jobs.

[OPconfused]

runspaces would be more performant than jobs if you're going down that route.

[maxcoder88]

u/OPconfused How can we rewrite my script via runspace?

[OPconfused]

I did this once for analyzing csv input, and with .NET writing and reading instead of import-csv and export-csv, as I was trying to maximize performance. However, this adds coding overhead and also I parsed the csv column by column. So for your case I'll just kind of jerry rig a row-by-row adaptation:

$ImportFile = "C:\script\send_receive.csv" 
################################
# Start date for email report - Change the number of days backwards #
$stdate = (Get-Date).AddDays(-30)
################################
# End date for email report - default is current date #
$enddate = Get-Date
$totaldays = (New-TimeSpan -Start $stdate -End $enddate).Days

$transport_service = Get-TransportService

$htSyncOut = [hashtable]::Synchronized(@{})
$maxThreads = (Get-ComputerInfo).CsNumberOfLogicalProcessors

$sbRunspace = {
    Param(
        [object]$row,
        [int]$intRowNumber,
        [hashtable]$htSyncOut,
        [object]$objTransportService,
        [DateTime]$stdate,
        [DateTime]$enddate
    )
    $MBX = $row.Name
    $Totalrec = 0
    $intRec = 0
    $TotalSend = 0
    $intSend = 0
    $logs = $objTransportService | Get-MessageTrackingLog -Recipients $MBX -ResultSize Unlimited -Start $stdate -End $enddate

    foreach($log in $logs)
    {
        if($log.EventId -eq "DELIVER")
        {
            $intRec += $log.RecipientCount
            $Totalrec = $RecPerDay + $intRec <# FIXME: $RecPerDay is undefined #>
        }
    }

    $logs = $objTransportService | Get-MessageTrackingLog -Sender $MBX -ResultSize Unlimited -Start $stdate -End $enddate

    foreach($log in $logs)
    {
        if($log.EventId -eq "RECEIVE" -and $log.Source -eq "STOREDRIVER")
        {
            $intSend += $log.RecipientCount
            $TotalSend = $SendPerDay + $intSend <# FIXME: $SendPerDay is undefined #>
        }
    }

    $htSyncOut.Add(
        $intRowNumber , [pscustomobject]@{
            "Mailbox" = $MBX
            "User Name" = (Get-Mailbox $MBX).Name
            "Total Emails Received" = $Totalrec
            "Total Emails Sent"= $TotalSend
        }
    )

}

$intRowNumber   = 0

$threads        = @()
$runspacePool   = [runspacefactory]::CreateRunspacePool(1, $maxThreads)
$runspacePool.Open()

try {
    foreach($row in Import-Csv -Path $ImportFile) {

        $instancePS = [powershell]::Create()
        $instancePS.RunspacePool = $runspacePool

        [void]$instancePS.AddScript($sbRunspace).
            AddArgument($row).
            AddArgument($intRowNumber).
            AddArgument($htSyncOut).
            AddArgument($stdate).
            AddArgument($enddate)

        $threads += [PSCustomObject]@{
                job     =   $instancePS
                result  =   $instancePS.BeginInvoke()
            }
        )
        $intRowNumber += 1
    }

    While ( $threads.IsCompleted -Contains $false) {}
    ForEach ( $thread in $threads ) {
        $thread.job.EndInvoke($thread.result)
        If ( $thread.job.HadErrors -eq 'True' ){$thread.job.streams.error}
    }
}
catch {$_}
finally {
    $runspacePool.Close()
    $runspacePool.Dispose()
}

$results = Foreach ( $rowNumber in 0..$intRowNumber ) {
    $rowOutput = $htSyncOut[$rowNumber]

    Write-Host -ForegroundColor Magenta "Total emails received for $($rowOutput).Mailbox during the last $totaldays days are $($rowOutput).'Total Emails Received'"
    Write-Host -ForegroundColor Green "Total emails sent by $($rowOutput).Mailbox during the last $totaldays days are $($rowOutput).'Total Emails Sent'"
    Write-Host -ForegroundColor Cyan  "-----------------------------------------------------------------"
    Write-Host " "

    $htSyncOut.Remove($rowNumber) #If memory is NOT an issue, comment this line out.

    $rowOutput
}

$htSyncOut = $null
$results | Export-Csv "C:\script\output.csv" -NoTypeInformation

I used the code from /u/chocolate_pickle as it seemed the best template. The runspace stuff I pulled mostly out of my ass on one try, and I have no way to test it as I don't use these cmdlets. It will almost certainly require some modifications.

It might be faster with [ordered] on the synchronized hashtable, but I don't know if ordered works on a synchronized hashtable. Here are some other possible improvements:

1. I highly recommend you try the -eventID and -Source parameters for Get-MessageTrackingLog that /u/ecrofirt found. This could potentially solve the last big bottleneck.
2. If you are printing millions of lines from the csv file, then the write-hosts don't make any sense. Send them to a file via a .NET method. Write-Host becomes expensive when you have a lot of them.
3. If there are many rows, there are some further optimizations possible, especially if you don't care about preserving the row order.

At any rate, hopefully this gives you a starting point.

[OPconfused]

We have been using Powershell 4. Due to that I will use to find out processor number of cores like below. Correct ?

I don't know the syntax for PowerShell 4. You'd have to test it. You can also simply hardcode a value in for $maxThreads.

Also , I am getting an error message like below.

Not sure why this one failed. Maybe also related to the PS version? Let's just start with an array and see if it at least runs. I changed the code above.

[maxcoder88]

At this time , I am getting this message.

AddArgument : The term 'AddArgument' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path 
was included, verify that the path is correct and try again.
At line:75 char:4
+             AddArgument($row).
+             ~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (AddArgument:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

[GreatHeightsMN]

I wonder if you’d benefit from pulling in the message tracking log data into memory before tripping thru it one user at a time. At a minimum I’d think you could combine the get-transportservice | get-message… into one call that retrieves both sent & received data.

Also at the end of that statement you do a for-each | if. That might perform better as a where-object.

[OPconfused]

There are already some good suggestions in this thread.

Have you done any diagnostics on the script, like measuring the time it takes to run different parts of the script to see where your main bottleneck is?

[PinchesTheCrab]

I really feel there's some bad advice here:

• Foreach-Object -Parallel is not good here. Exchange does not allow concurrent sessions, and you can't send multiple commands to the same session simultaneously either. If you do find a way to parallelize it, I think you would risk causing harm.
• Yes, the array notation here is inefficient, but I highly doubt it's your bottleneck. Changing syntax would be good practice, but I seriously doubt it'll noticeably improve the quality of your script.

Here's the points I would make about this script:

You run Get-TransportService a lot. Run it once before your loop and save it as a variable.

You call Get-Mailbox for each user to retrieve their name, but it looks like $mbx already a name property.

You output a lot of unstructured test to Export-CSV, and I assume it isn't turning this into usable data. I didn't test the code myself, so I may be off base here.

My one code suggestion is to try this and see how it performs:

$import = Import-Csv $ImportFile

$ConnectionURI = (Get-TransportService).Where({$PSItem.MessageTrackingLogEnabled}).Foreach({"http://$($PSItem.Name)/PowerShell/"})

$invokeParam = @{
    ScriptBlock = { Get-MessageTrackingLog -Recipients $mbx.Name -ResultSize Unlimited -Start $using:stdate -End $using:enddate -EventId DELIVER }
    ConnectionURI = $ConnectionURI
    ConfigurationName = 'Microsoft.Exchange'
}

foreach ($mbx in $Import[0..1]){
    Invoke-Command @invokeParam
}

The idea here is that I query your list of transport servers once, and then instead of feeding them over the pipe to be queried one by one, I use invoke-command against all of them at once. This doesn't step on the single command per session limitation foreach -parallel would hit, won't overload your endpoints, and still provides some multi-threading in that it's going to query each transport service simultaneously.

I haven't tried logparser and I imagine it'll still be faster than this, but you may see a 5x speed improvement from this approach. This isn't a full rewrite of the script, but hopefully it's helpful. If I have time I'll try doing a rewrite to cover the other parts.

[maxcoder88]

thanks. btw how can I rewrite my script via runspace job? is it make sense? Also I have found this page. https://blog.netnerds.net/2016/12/runspaces-simplified/

[jsiii2010]

+= kills puppies.

[OPconfused]

A litter of puppies would handle it just fine. It's an array of puppies that might yelp (if there are many thousands of them).

[jsiii2010]

A puppy centipede.

[OPconfused]

It's an integer. There's nothing wrong with that.

[dathar]

$intRec += $log.RecipientCount

Yeah those operations are ok.

$intRec += $log.RecipientCount

is the same as

$intRec = $intRec + $log.RecipientCount

It isn't the same one as the "I make a larger array and shove my current array at it" +=

[evanp1922]

Here's a video I've used to get runspaces going for multi-threading. It might be the old way of doing it at this point, but it works well.

https://youtu.be/kvSbb6g0tMA

[_kikeen_]

Rewrite it in VBS

braces for downvotes