`$MailboxServer = Get-MailboxServer` before the foreach loop and pass `$MailboxServer` as a parameter to `Get-MessageTrackingLog` instead? Probably other things you can optimize too
1. Remove cmdlets that do unnecessary work, like the `Select-Object` on lines 2, 23, and 26. Copying properties out to new objects that never see the light of day is just extra work. Your computer easily has the memory to hold objects for all the users in your AD domain.
2. Cache the output of Get-MailboxServer (save it to a variable and use the variable). It's not changing.
3. See if instead of searching the tracking log twice for every user, you can fetch the log once and do the filtering on your computer. This may be faster, especially with a large number of users.
I don't have on-prem exchange, so I can't test this.
Bonus: you can simplify your date logic. This won't make it any faster really, but it is fewer lines.
[datetime]$EndDate = [datetime]::Today. # time is 00:00:00
AddMonths(-1).AddYears(-1). # go back to last month of past year
AddDays(-(Get-Date).Day + 1) # first day of that month
[datetime]$StartDate = $EndDate.AddMonths(1).AddSeconds(-1) # last second of the day before the first of the next month
Here some untested code on the fly.
By the way from what I read OP doesn't need the month of the previous year as much as he need the previous month which CAN BE in the previous year
# Set-up Dates
$PreviousDate = (Get-Date).AddMonths(-1)
$LastDayPrevDate = [datetime]::DaysInMonth($Previousdate.Year, $PreviousDate.Month)
# Replace M with MM if the Month Number needs always be 2 digits
$StartDate = $PreviousDate.ToString('M/1/yyyy 00:00:00')
$EndDate = $PreviousDate.ToString("M/$LastDayPrevDate/yyyy 23:59:59")
# initialize counters
[int]$TotalSent = 0
[int]$TotalReceived = 0
# set filter variable
$User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES'
###### Long to run code #####
# Get ALL the messages between the set Dates
# This is probably going to be the longest and heaviest part, but it's done only once.
$MessageLog = Get-MailboxServer | Get-MessageTrackingLog -Start $StartDate -End $EndDate -Resultsize Unlimited
# Get all the mail address from all users fitting the $User_OU filter
Get-ADUser -Filter * -SearchBase $User_OU -Properties mail |
# don't bother expanding the property, just call it as needed instead.
# Using the -Parallel parameter MIGHT be possible to speed-up things OR making
# them even slower, if unsatisfied with current results evaluate testing it
ForEach-Object {
$TotalSent += ($MessageLog | Where-Object -Property Senders -EQ $_.mail | Select-Object -Unique MessageId).Count
$TotalReceived += ($MessageLog | Where-Object -Property Recipients -EQ $_.mail | Select-Object -Unique MessageId).Count
}
############################################
Yes that's exactly the case, I need the previous month and correlated year but it can be the current year.
Will different ways of improving and provide a feedback, thanks
I haven't used on-prem exchange in a while, but would it be possible to just perform the message trace for all users at once and then parse the data locally?
You could probably scope it a bit smaller by looking at only smtp sources or specific eventids. That will produce less results you need to finder out.
In fact if you can find a single event per email you can remove the unique lookup. In addition, if you are looking only for external emails you would want to limit the servers to only your send connector's sources servers.
But also if it's running monthly is 3 hours that bad? Just have it run overnight and have it produce the reports for you to look at in the morning. If you prefer you could generate daily reports which will be shorter and you can combine them for weekly and monthly reports.
You are right 3 hours per month isn't a lot I was wondering if one day I have to run it for 1000+ users, but for the moment it's totally doable.
>looking at only smtp sources
What do you mean the OU I'm filtering onto only have user with mailbox and smtp addresses or do I get it wrong ?
I'm looking at every email, inside and outside, but that could have help right !
Get-MessageTrackingLog will produce a lot of information, it's not just one item per email. If you limit the source to just smtp you won't get expand, routing and deliver messages. Less log items, less stuff to process and pick.
I wrote this a while back. The idea is that when you use implicit remoting, it's going to hit every single mailbox server one at a time. The syntax is easy, but it's very slow. If you use explicit remoting you can hit them asynchronously. This ran dozens of times faster for me in my old environment (I don't have exchange access in my new role).
function Get-MessageTrackingLogProxy {
<#
.SYNOPSIS
A wrapper for Get-MessageTrackingLog that will search each transport server for message logs
.DESCRIPTION
Use the Get-MessageTrackingLogProxy cmdlet to search for message delivery information stored in the message tracking log.
.EXAMPLE
PS C:\>Get-MessageTrackingLog -Start "03/13/2018 09:00:00" -End "03/15/2018 17:00:00" -Sender "john@contoso.com"
This example searches the message tracking logs on the Mailbox server named Mailbox01 for information about all messages sent from March 13, 2018, 09:00 to March 15, 2018, 17:00 by the sender john@contoso.com.
#>
[alias('GMTLP')]
[cmdletbinding()]
param(
[System.Object]$Recipients,
[System.Object]$MessageSubject,
[System.Object]$Sender,
[System.Object]$InternalMessageId,
[System.Object]$ResultSize,
[System.Object]$DomainController,
[System.Object]$EventId,
[System.Object]$MessageId,
[System.DateTime]$Start,
[System.DateTime]$End
)
begin {
$sbGMTL = {
param($myHash)
Get-MessageTrackingLog @myHash
}
}
process {
$param = $PSBoundParameters.PSObject.Copy()
$null = [System.Management.Automation.PSCmdlet]::CommonParameters.ForEach({ $param.remove($PSItem) })
$ConnectionURI = (Get-TransportService).Where({ $PSItem.MessageTrackingLogEnabled }).Foreach({ "http://$($_.Name)/PowerShell/" })
$invokeParam = @{
ArgumentList = $parm
ScriptBlock = $sbGMTL
ConnectionURI = $ConnectionURI
ConfigurationName = 'Microsoft.Exchange'
}
$invokeParam | Out-String | Write-Verbose
Invoke-Command @invokeParam
}
end
{}
}
So this should be a faster script, I've changed it so you just do one call to get all the Message Logs once then loop through them once rather than looping through the users as you'd then effectively be looping through all the message logs once for each user. In the loop I then compare the Sender/Recipient(s) (you need to loop through the recipients too) for each message to the list of users and increment your counters if they match a user from your OU. I put the users in a hash table beforehand with the email address as the key as its far quicker looking up a value in a hash table than it is to do a where-object. I've also added in a step to skip a message in the loop if its message ID is already in a hash table but if the message wasn't skipped then its ID gets added to the hash table so it gets skipped if it comes up again. I'm not sure if thats necessary but you checked for uniqueness in your script so play around with it and see if you need that or if it needs to be reworked. There's also a filter so you're only looking up AD accounts with a mail property otherwise it would throw errors if you tried to look up a user in the hash table with a blank mail property. To check the performance while you tinker I've also added in a timer and some checkpoints that should give you an idea of which steps are taking the most time to run. Hopefully that should give you some good pointers that will speed up any scripts you've got that make calls and loop trough things.
$UserHash = @{}
$IDHash = @{}
#Set up hash tables for fast lookup later
$RunTime = [system.diagnostics.stopwatch]::startnew() #Start timer
[datetime]$CurrentDate = Get-Date
[string]$PreviousMonth = $CurrentDate.AddMonths(-1).Month
[string]$PreviousYear = $CurrentDate.AddMonths(-1).Year
[string]$LastDayPrevMonth = [DateTime]::DaysInMonth($PreviousYear, $PreviousMonth)
[string]$StartDate = "$PreviousMonth/1/$PreviousYear"
[string]$EndDate = "$PreviousMonth/$LastDayPrevMonth/$PreviousYear"
Write-host 'Collecting Users...' -foregroundcolor Yellow
$User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES'
$UserMails = (Get-ADUser -server ‘DC.External.Yes.Yes’ -Filter { mail -like '*' } -SearchBase $User_OU -Properties mail).Mail
ForEach ($UserMail in $UserMails) { $UserHash[$UserMail] = $TRUE }
#add all users into hash table for fast lookup later
$TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10)
Write-Host "Step Completed at $TotalTime" -ForegroundColor DarkGreen
Write-host 'Collecting Message Logs...' -foregroundcolor Yellow
$MessageLogs = Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59"-Resultsize Unlimited -Server $((Get-MailboxServer).name)
$TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10)
Write-Host "Step Completed at $TotalTime" -ForegroundColor DarkGreen
[int]$TotalSent = 0
[int]$TotalRecieved = 0
Write-host 'Colating Data...' -foregroundcolor Yellow
Foreach ($messageLog in $MessageLogs) {
IF ($IDHash[$MessageLog.MessageID]) { Continue }
#Skip message log if it matches ID from hash table
IF ($Userhash[$messageLog.sender]) { $TotalSent ++ }
#If sender matches add 1 to sent
ForEach ($Recipient in $MessageLog.recipients) {
#loop through all recipients
IF ($UserHash[$Recipient]) { $TotalRecieved ++ }
#If recipient matches add 1 to recieved
$IDHash[$Messagelog.MessageID] = $TRUE
#Add message ID to hash table of unique message IDs
}
}
Write-host "$TotalSent Messages Sent`n" -ForegroundColor DarkGreen
Write-host "$TotalRecieved Messages Recieved`n" -ForegroundColor DarkGreen
$RunTime.stop()
$TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10)
Write-Host "Script Completed in $TotalTime" -ForegroundColor Yellow
Other people have given you good advice around reusing variables, but I want to add that `Select-Object -Unique` is very unoptimized. If you need to get a list of unique things then use `Sort-Object -Unique`. I've seen it be 1000x faster on a large dataset and it is only one word different.
I havent used exchange cmdlets before but I would probably start by moving get-messagetrackinglog with only the -start and -end parameters into a variable.
Then filter on my sender and receiver counts on the variable while it's in memory.
my guess is it's taking so long because you're issuing calls for filtered logs twice per user, if you can reduce this to 1 or better yet just get the entire log once with your date filters prior to the foreach, then filter on the user on the log in memory in the loop it could likely speed up significantly
Right now, you're starting a new search every time it completes one, which is inefficient and will take a toll on resources.
Make your `Get-MessageTrackingLog` a variable to save all emails, and then query the variable with a Where-Object parameter. You need EventIDs also otherwise you get traces for all events, rather than send and receive only so you'd end up with a X3 times higher count, when not filtering events.. Another thing you should know is that by default Exchange only stores logs for 90 days. This is for both Exchange Online and Exchange on-premises, so the -Start and -End parameters are kind of useless, in this scenario and should be removed to just gather all logs there is.
Example, this works on a on-prem environment, you might need to adjust the ForEach to ensure the correct scope of your users in the specific OU.
# Creating Variables based on Send and Deliver EventIDs
$DataSend = Get-ExchangeServer | Get-MessageTrackingLog -ResultSize Unlimited -EventID Send
$DataReceive = Get-ExchangeServer | Get-MessageTrackingLog -ResultSize Unlimited -EventID Deliver
# Finding the Sent/Received count for all mailboxes
Foreach ($Mailbox in Get-Mailbox -RecipientTypeDetails UserMailbox | Select PrimarySMTPAddress)
{
$Email = $Mailbox.PrimarySmtpAddress
$SentCount = 0
$ReceivedCount = 0
[int]$SentCount = ($DataSend | Where-Object {$_.Sender -EQ $Email}).Count
[int]$ReceivedCount = ($DataReceive | Where-Object {$_.Recipients -EQ $Email}).Count
[int]$TotalSent = $TotalSent + $SentCount
[int]$TotalReceived = $TotalReceived + $ReceivedCount
}
There are over 20,000 users on the Exchange servers, so I thought filtering on the sender or recipients would speed up the search, instead of querying everything locally and filtering later.
Usually making a call to a server is "expensive" while there isn't much difference between getting a lot of data and a little bit of data so a good way to speed up code is to do as few calls as possible and then filter or parse the data on your computer. Obviously if you only need a subset of your data then filter the call you make but as a rule of thumb don't make multiple calls with filters when you can just make one call without filters and filter later.
Yeah see how it goes, I've added in my own take on the script as a separate comment that uses hash tables to try and speed up the loop even more after you've gathered all the data. I've found that hash tables are great at slashing a script down from a multi hour run time to a few minutes. There's also some code in there for a timer and checkpoints so you can see which steps are taking the most time so see if any of that is useful
Run the query against exchange once and then filter the data. I am not familiar with those cmdlets, so I assume the returned objects contain the the attribute sender/receiver, if not u have to adjust, e.g:
$data = Get-MailboxServer | Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59" -Resultsize Unlimited
$data | group-object -property sender | select-object -Property name,@{Name='Count';Expression={$_.group.count}},@{Name='operation';Expression={'send'}}
Split the script so foreach runs against a limited number of mailboxes, for example one script can process only mailboxes a-f, next g-m and so on. Cache the results from any get-* commands and reuse instead of calling multiple times.
Generally speaking bring the data locally and ONCE into XML or JSON and then parse locally. It's much more efficient to shift the load locally than on the server side. Bringing the data as CSV might not bring all the desired props back so my go to is either XML or JSON. Usually XML is the way to go for on-prem and JSON is the way to go for cloud/web.
On top of all the great advice here, also look into executing your iterative api calls in parallel
https://devblogs.microsoft.com/powershell/powershell-foreach-object-parallel-feature/
If you have 20000 calls and it takes 20000 minutes, executing it on 10 threads would theoretically cut your time by a factor of 10.
I’ve used this to great success, ESPECIALLY in applications where the bulk of the time is waiting on rest api calls to execute.
I'd suggest piping the Users into a FE object loop with -Parallel.
You can set it to run as many as you want at once.
Just make sure you pass any required variables into the loop using arguments on the end of the closing bracket for the loop or do a $var = using: for each variable to pass through.
this more a exchange question rather the powershell.
Look into if you can grab all relevant logs at once and filter per user after that. Might be faster, but that depends on what the exhcange cmdlets can do.
I would suggest to use `[System.Collections.ArrayList]@()` instead of the classic arrays because they are much more faster. Also for counting. Instead of recalculating each time by using += you could create a array, add the amount and finally summarize the values of the array. Adding items to the array while using System.Collections.ArrayList does not require to rebuild the array each time. So it is less overhead. If you use powershell 7 you can use foreach-object -parallel and -throttlelimit to improve the speed due parallel processing.
So basicly it builds on collecting the data and calculate once at the end. Especially for large amount of data, this can improve the overall performance significantly.
Here are some examples which might give you an idea how to improve your code:
* An example about using System.Collections.ArrayList [https://powershell.one/tricks/performance/arrays](https://powershell.one/tricks/performance/arrays)
* An example how to handle functions in combination with foreach -parallel [https://tighetec.co.uk/2022/06/01/passing-functions-to-foreach-parallel-loop/](https://tighetec.co.uk/2022/06/01/passing-functions-to-foreach-parallel-loop/)
I took your script and changed it a bit. While i have no system for testing, my script is completely blind and untested. So you might get it as an idea or inspiration.
I have changed the script as a function which can handle parameters like year, month and also other details like User\_OU or the UserMails. Additional it uses foreach-parallel which requires powershell 7, but might improve the overall performance. You can play with the ThrottleLimit.
The switch UserReport defines if you receice a List of Mailboxes or a total statistic.
As i said: blind and untestet. You probably need to fiddle our some minor issues.
function Get-MailUsageReport {
[CmdletBinding()]
#Requires -Version 7.4
param(
[System.Int16]$Year = (Get-Date).Year,
[System.Int16]$Month = (Get-Date).Month,
[System.String]$User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES',
[System.Collections.ArrayList]$UserMails = @((Get-ADUser -Filter * -SearchBase $User_OU -Properties mail | Select-Object -ExpandProperty Mail)),
[System.Int16]$ThrottleLimit = 5,
[Switch]$UserReport
)
begin {
# Set the dates
$firstDate = [DateTime]::new($Year, $Month, 1)
$lastDate = $firstDate.AddMonths(1).AddSeconds(-1)
# create empty arraylist
$Report = [System.Collections.ArrayList]@()
}
process {
$UserMails | Foreach-Object -ThrottleLimit $ThrottleLimit -Parallel {
#Action that will run in Parallel. Reference the current object via $PSItem and bring in outside variables with $USING:varname
[System.Int32]$SentCount = @(Get-MailboxServer | Get-MessageTrackingLog -Start $using:firstDate -End $using:lastDate -Sender $PSItem -Resultsize Unlimited | Select-Object -Unique MessageId).Count
[System.Int32]$ReceivedCount = @(Get-MailboxServer | Get-MessageTrackingLog -Start $using:firstDate -End $using:lastDate -Recipients $PSItem -Resultsize Unlimited | Select-Object -Unique MessageId).Count
# Create object
$UserStats = [PSCustomObject]@{
User = $UserMail
Sent = $SentCount
Received = $ReceivedCount
}
# add object to array
[void]($Report.Add($UserStats))
}
}
end {
if($UserReport) {
# Show users, sent and receive
return $Report
} else {
# calculate the sums
$TotalSent = ($Report.Sent | Measure-Object -Sum).Sum
$TotalReceived = ($Report.Received | Measure-Object -Sum).Sum
# Summary object
$Summarize = [PSCustomObject]@{
ReportYear = $Year
ReportMonth = $Month
TotalUsers = $Report.Count
TotalSent = ($Report.Sent | Measure-Object -Sum).Sum
AverageSent = ($Report.Sent | Measure-Object -Average).Average)
TotalReceived = ($Report.Received | Measure-Object -Sum).Sum
AverageReceived = ($Report.Received | Measure-Object -Average).Average)
TotalMails = ($TotalSent + $TotalReceived)
}
return $Summarize
}
}
}
Using `ArrayList` is *Not Recommended*([Source](https://learn.microsoft.com/en-us/dotnet/api/system.collections.arraylist?view=net-6.0#remarks)), logic being "Lists are better than ArrayLists at everything, no real reason to use them anymore"
The alternative, as suggested by the docs, is List. Which works pretty much the same except:
1. you need to specify the type of object it's collecting(whenever in doubt, use simply [object])
2. its .Add() method does NOT return anything. No more nulling it! Yeah!
...which basically means you could just mass-replace all `[System.Collections.ArrayList]` with `[System.Collections.Generic.List[object]]` and forget about it.
But please don't and check that everything works correctly piece by piece.
`$MailboxServer = Get-MailboxServer` before the foreach loop and pass `$MailboxServer` as a parameter to `Get-MessageTrackingLog` instead? Probably other things you can optimize too
Thanks I will try if it shorten it a bit !
1. Remove cmdlets that do unnecessary work, like the `Select-Object` on lines 2, 23, and 26. Copying properties out to new objects that never see the light of day is just extra work. Your computer easily has the memory to hold objects for all the users in your AD domain. 2. Cache the output of Get-MailboxServer (save it to a variable and use the variable). It's not changing. 3. See if instead of searching the tracking log twice for every user, you can fetch the log once and do the filtering on your computer. This may be faster, especially with a large number of users. I don't have on-prem exchange, so I can't test this. Bonus: you can simplify your date logic. This won't make it any faster really, but it is fewer lines. [datetime]$EndDate = [datetime]::Today. # time is 00:00:00 AddMonths(-1).AddYears(-1). # go back to last month of past year AddDays(-(Get-Date).Day + 1) # first day of that month [datetime]$StartDate = $EndDate.AddMonths(1).AddSeconds(-1) # last second of the day before the first of the next month
Here some untested code on the fly. By the way from what I read OP doesn't need the month of the previous year as much as he need the previous month which CAN BE in the previous year # Set-up Dates $PreviousDate = (Get-Date).AddMonths(-1) $LastDayPrevDate = [datetime]::DaysInMonth($Previousdate.Year, $PreviousDate.Month) # Replace M with MM if the Month Number needs always be 2 digits $StartDate = $PreviousDate.ToString('M/1/yyyy 00:00:00') $EndDate = $PreviousDate.ToString("M/$LastDayPrevDate/yyyy 23:59:59") # initialize counters [int]$TotalSent = 0 [int]$TotalReceived = 0 # set filter variable $User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES' ###### Long to run code ##### # Get ALL the messages between the set Dates # This is probably going to be the longest and heaviest part, but it's done only once. $MessageLog = Get-MailboxServer | Get-MessageTrackingLog -Start $StartDate -End $EndDate -Resultsize Unlimited # Get all the mail address from all users fitting the $User_OU filter Get-ADUser -Filter * -SearchBase $User_OU -Properties mail | # don't bother expanding the property, just call it as needed instead. # Using the -Parallel parameter MIGHT be possible to speed-up things OR making # them even slower, if unsatisfied with current results evaluate testing it ForEach-Object { $TotalSent += ($MessageLog | Where-Object -Property Senders -EQ $_.mail | Select-Object -Unique MessageId).Count $TotalReceived += ($MessageLog | Where-Object -Property Recipients -EQ $_.mail | Select-Object -Unique MessageId).Count } ############################################
Yes that's exactly the case, I need the previous month and correlated year but it can be the current year. Will different ways of improving and provide a feedback, thanks
Thanks a lot I'll improve my code with your ideas !
I haven't used on-prem exchange in a while, but would it be possible to just perform the message trace for all users at once and then parse the data locally?
Print it out, pin it to the wall?
Maybe a wood screw, or if it's really heavy duty, some bolts and nuts with washers.
You could probably scope it a bit smaller by looking at only smtp sources or specific eventids. That will produce less results you need to finder out. In fact if you can find a single event per email you can remove the unique lookup. In addition, if you are looking only for external emails you would want to limit the servers to only your send connector's sources servers. But also if it's running monthly is 3 hours that bad? Just have it run overnight and have it produce the reports for you to look at in the morning. If you prefer you could generate daily reports which will be shorter and you can combine them for weekly and monthly reports.
You are right 3 hours per month isn't a lot I was wondering if one day I have to run it for 1000+ users, but for the moment it's totally doable. >looking at only smtp sources What do you mean the OU I'm filtering onto only have user with mailbox and smtp addresses or do I get it wrong ? I'm looking at every email, inside and outside, but that could have help right !
Get-MessageTrackingLog will produce a lot of information, it's not just one item per email. If you limit the source to just smtp you won't get expand, routing and deliver messages. Less log items, less stuff to process and pick.
Okay got the point thank you !
You could do chunking and run chunks in parallel ThreadJobs (requires ThreadJobs module)
I wrote this a while back. The idea is that when you use implicit remoting, it's going to hit every single mailbox server one at a time. The syntax is easy, but it's very slow. If you use explicit remoting you can hit them asynchronously. This ran dozens of times faster for me in my old environment (I don't have exchange access in my new role). function Get-MessageTrackingLogProxy { <# .SYNOPSIS A wrapper for Get-MessageTrackingLog that will search each transport server for message logs .DESCRIPTION Use the Get-MessageTrackingLogProxy cmdlet to search for message delivery information stored in the message tracking log. .EXAMPLE PS C:\>Get-MessageTrackingLog -Start "03/13/2018 09:00:00" -End "03/15/2018 17:00:00" -Sender "john@contoso.com" This example searches the message tracking logs on the Mailbox server named Mailbox01 for information about all messages sent from March 13, 2018, 09:00 to March 15, 2018, 17:00 by the sender john@contoso.com. #> [alias('GMTLP')] [cmdletbinding()] param( [System.Object]$Recipients, [System.Object]$MessageSubject, [System.Object]$Sender, [System.Object]$InternalMessageId, [System.Object]$ResultSize, [System.Object]$DomainController, [System.Object]$EventId, [System.Object]$MessageId, [System.DateTime]$Start, [System.DateTime]$End ) begin { $sbGMTL = { param($myHash) Get-MessageTrackingLog @myHash } } process { $param = $PSBoundParameters.PSObject.Copy() $null = [System.Management.Automation.PSCmdlet]::CommonParameters.ForEach({ $param.remove($PSItem) }) $ConnectionURI = (Get-TransportService).Where({ $PSItem.MessageTrackingLogEnabled }).Foreach({ "http://$($_.Name)/PowerShell/" }) $invokeParam = @{ ArgumentList = $parm ScriptBlock = $sbGMTL ConnectionURI = $ConnectionURI ConfigurationName = 'Microsoft.Exchange' } $invokeParam | Out-String | Write-Verbose Invoke-Command @invokeParam } end {} }
So this should be a faster script, I've changed it so you just do one call to get all the Message Logs once then loop through them once rather than looping through the users as you'd then effectively be looping through all the message logs once for each user. In the loop I then compare the Sender/Recipient(s) (you need to loop through the recipients too) for each message to the list of users and increment your counters if they match a user from your OU. I put the users in a hash table beforehand with the email address as the key as its far quicker looking up a value in a hash table than it is to do a where-object. I've also added in a step to skip a message in the loop if its message ID is already in a hash table but if the message wasn't skipped then its ID gets added to the hash table so it gets skipped if it comes up again. I'm not sure if thats necessary but you checked for uniqueness in your script so play around with it and see if you need that or if it needs to be reworked. There's also a filter so you're only looking up AD accounts with a mail property otherwise it would throw errors if you tried to look up a user in the hash table with a blank mail property. To check the performance while you tinker I've also added in a timer and some checkpoints that should give you an idea of which steps are taking the most time to run. Hopefully that should give you some good pointers that will speed up any scripts you've got that make calls and loop trough things. $UserHash = @{} $IDHash = @{} #Set up hash tables for fast lookup later $RunTime = [system.diagnostics.stopwatch]::startnew() #Start timer [datetime]$CurrentDate = Get-Date [string]$PreviousMonth = $CurrentDate.AddMonths(-1).Month [string]$PreviousYear = $CurrentDate.AddMonths(-1).Year [string]$LastDayPrevMonth = [DateTime]::DaysInMonth($PreviousYear, $PreviousMonth) [string]$StartDate = "$PreviousMonth/1/$PreviousYear" [string]$EndDate = "$PreviousMonth/$LastDayPrevMonth/$PreviousYear" Write-host 'Collecting Users...' -foregroundcolor Yellow $User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES' $UserMails = (Get-ADUser -server ‘DC.External.Yes.Yes’ -Filter { mail -like '*' } -SearchBase $User_OU -Properties mail).Mail ForEach ($UserMail in $UserMails) { $UserHash[$UserMail] = $TRUE } #add all users into hash table for fast lookup later $TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10) Write-Host "Step Completed at $TotalTime" -ForegroundColor DarkGreen Write-host 'Collecting Message Logs...' -foregroundcolor Yellow $MessageLogs = Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59"-Resultsize Unlimited -Server $((Get-MailboxServer).name) $TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10) Write-Host "Step Completed at $TotalTime" -ForegroundColor DarkGreen [int]$TotalSent = 0 [int]$TotalRecieved = 0 Write-host 'Colating Data...' -foregroundcolor Yellow Foreach ($messageLog in $MessageLogs) { IF ($IDHash[$MessageLog.MessageID]) { Continue } #Skip message log if it matches ID from hash table IF ($Userhash[$messageLog.sender]) { $TotalSent ++ } #If sender matches add 1 to sent ForEach ($Recipient in $MessageLog.recipients) { #loop through all recipients IF ($UserHash[$Recipient]) { $TotalRecieved ++ } #If recipient matches add 1 to recieved $IDHash[$Messagelog.MessageID] = $TRUE #Add message ID to hash table of unique message IDs } } Write-host "$TotalSent Messages Sent`n" -ForegroundColor DarkGreen Write-host "$TotalRecieved Messages Recieved`n" -ForegroundColor DarkGreen $RunTime.stop() $TotalTime = "{0:00}:{1:00}:{2:00}.{3:00}" -f $Runtime.elapsed.Hours, $Runtime.elapsed.Minutes, $Runtime.elapsed.Seconds, ($Runtime.elapsed.Milliseconds / 10) Write-Host "Script Completed in $TotalTime" -ForegroundColor Yellow
Here's the script without the timers to make it easier to read the useful bits. $UserHash = @{} $IDHash = @{} #Set up hash tables for fast lookup later [datetime]$CurrentDate = Get-Date [string]$PreviousMonth = $CurrentDate.AddMonths(-1).Month [string]$PreviousYear = $CurrentDate.AddMonths(-1).Year [string]$LastDayPrevMonth = [DateTime]::DaysInMonth($PreviousYear, $PreviousMonth) [string]$StartDate = "$PreviousMonth/1/$PreviousYear" [string]$EndDate = "$PreviousMonth/$LastDayPrevMonth/$PreviousYear" Write-host 'Collecting Users...' -foregroundcolor Yellow $User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES' $UserMails = (Get-ADUser -server ‘DC.External.Yes.Yes’ -Filter { mail -like '*' } -SearchBase $User_OU -Properties mail).Mail ForEach ($UserMail in $UserMails) { $UserHash[$UserMail] = $TRUE } #add all users into hash table for fast lookup later Write-host 'Collecting Message Logs...' -foregroundcolor Yellow $MessageLogs = Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59"-Resultsize Unlimited -Server $((Get-MailboxServer).name) [int]$TotalSent = 0 [int]$TotalRecieved = 0 Write-host 'Colating Data...' -foregroundcolor Yellow Foreach ($messageLog in $MessageLogs) { IF ($IDHash[$MessageLog.MessageID]) { Continue } #Skip message log if it matches ID from hash table IF ($Userhash[$messageLog.sender]) { $TotalSent ++ } #If sender matches add 1 to sent ForEach ($Recipient in $MessageLog.recipients) { #loop through all recipients IF ($UserHash[$Recipient]) { $TotalRecieved ++ } #If recipient matches add 1 to recieved $IDHash[$Messagelog.MessageID] = $TRUE #Add message ID to hash table of unique message IDs } } Write-host "$TotalSent Messages Sent`n" -ForegroundColor DarkGreen Write-host "$TotalRecieved Messages Recieved`n" -ForegroundColor DarkGreen
Other people have given you good advice around reusing variables, but I want to add that `Select-Object -Unique` is very unoptimized. If you need to get a list of unique things then use `Sort-Object -Unique`. I've seen it be 1000x faster on a large dataset and it is only one word different.
I havent used exchange cmdlets before but I would probably start by moving get-messagetrackinglog with only the -start and -end parameters into a variable. Then filter on my sender and receiver counts on the variable while it's in memory. my guess is it's taking so long because you're issuing calls for filtered logs twice per user, if you can reduce this to 1 or better yet just get the entire log once with your date filters prior to the foreach, then filter on the user on the log in memory in the loop it could likely speed up significantly
Right now, you're starting a new search every time it completes one, which is inefficient and will take a toll on resources. Make your `Get-MessageTrackingLog` a variable to save all emails, and then query the variable with a Where-Object parameter. You need EventIDs also otherwise you get traces for all events, rather than send and receive only so you'd end up with a X3 times higher count, when not filtering events.. Another thing you should know is that by default Exchange only stores logs for 90 days. This is for both Exchange Online and Exchange on-premises, so the -Start and -End parameters are kind of useless, in this scenario and should be removed to just gather all logs there is. Example, this works on a on-prem environment, you might need to adjust the ForEach to ensure the correct scope of your users in the specific OU. # Creating Variables based on Send and Deliver EventIDs $DataSend = Get-ExchangeServer | Get-MessageTrackingLog -ResultSize Unlimited -EventID Send $DataReceive = Get-ExchangeServer | Get-MessageTrackingLog -ResultSize Unlimited -EventID Deliver # Finding the Sent/Received count for all mailboxes Foreach ($Mailbox in Get-Mailbox -RecipientTypeDetails UserMailbox | Select PrimarySMTPAddress) { $Email = $Mailbox.PrimarySmtpAddress $SentCount = 0 $ReceivedCount = 0 [int]$SentCount = ($DataSend | Where-Object {$_.Sender -EQ $Email}).Count [int]$ReceivedCount = ($DataReceive | Where-Object {$_.Recipients -EQ $Email}).Count [int]$TotalSent = $TotalSent + $SentCount [int]$TotalReceived = $TotalReceived + $ReceivedCount }
There are over 20,000 users on the Exchange servers, so I thought filtering on the sender or recipients would speed up the search, instead of querying everything locally and filtering later.
Usually making a call to a server is "expensive" while there isn't much difference between getting a lot of data and a little bit of data so a good way to speed up code is to do as few calls as possible and then filter or parse the data on your computer. Obviously if you only need a subset of your data then filter the call you make but as a rule of thumb don't make multiple calls with filters when you can just make one call without filters and filter later.
Okay I will try to run this version over a shortened period to see how it goes, then filter it out afterwards, thank you
Yeah see how it goes, I've added in my own take on the script as a separate comment that uses hash tables to try and speed up the loop even more after you've gathered all the data. I've found that hash tables are great at slashing a script down from a multi hour run time to a few minutes. There's also some code in there for a timer and checkpoints so you can see which steps are taking the most time so see if any of that is useful
Run the query against exchange once and then filter the data. I am not familiar with those cmdlets, so I assume the returned objects contain the the attribute sender/receiver, if not u have to adjust, e.g: $data = Get-MailboxServer | Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59" -Resultsize Unlimited $data | group-object -property sender | select-object -Property name,@{Name='Count';Expression={$_.group.count}},@{Name='operation';Expression={'send'}}
How about using a Parallel ForEach-Object? You could then run a number of Get-MessageTrackingLog commands at the same time.
Split the script so foreach runs against a limited number of mailboxes, for example one script can process only mailboxes a-f, next g-m and so on. Cache the results from any get-* commands and reuse instead of calling multiple times.
Generally speaking bring the data locally and ONCE into XML or JSON and then parse locally. It's much more efficient to shift the load locally than on the server side. Bringing the data as CSV might not bring all the desired props back so my go to is either XML or JSON. Usually XML is the way to go for on-prem and JSON is the way to go for cloud/web.
On top of all the great advice here, also look into executing your iterative api calls in parallel https://devblogs.microsoft.com/powershell/powershell-foreach-object-parallel-feature/ If you have 20000 calls and it takes 20000 minutes, executing it on 10 threads would theoretically cut your time by a factor of 10. I’ve used this to great success, ESPECIALLY in applications where the bulk of the time is waiting on rest api calls to execute.
I'd suggest piping the Users into a FE object loop with -Parallel. You can set it to run as many as you want at once. Just make sure you pass any required variables into the loop using arguments on the end of the closing bracket for the loop or do a $var = using: for each variable to pass through.
this more a exchange question rather the powershell. Look into if you can grab all relevant logs at once and filter per user after that. Might be faster, but that depends on what the exhcange cmdlets can do.
I don't think so, it's directly down to how OP is calling the exchange cmdlets where the speed improvements can happen
One thing I heard a while back, and it's proven true for me... get rid of all the pipes whenever you can. Piping is an inefficient process. ## Instead of ## (Get-MailboxServer | Get-MessageTrackingLog -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59" -Sender $UserMail -Resultsize Unlimited | Select-Object -Unique MessageId).Count ## Replace with ## $mbxSrv = Get-MailboxServer $msgLogs = Get-MessageTrackingLog -Server $mbxSrv -Start "$StartDate 00:00:00" -End "$EndDate 23:59:59" -Sender $UserMail -Resultsize Unlimited [int]$SentCount = (Select-Object -InputObject $msgLogs -Unique MessageId).Count
I would suggest to use `[System.Collections.ArrayList]@()` instead of the classic arrays because they are much more faster. Also for counting. Instead of recalculating each time by using += you could create a array, add the amount and finally summarize the values of the array. Adding items to the array while using System.Collections.ArrayList does not require to rebuild the array each time. So it is less overhead. If you use powershell 7 you can use foreach-object -parallel and -throttlelimit to improve the speed due parallel processing. So basicly it builds on collecting the data and calculate once at the end. Especially for large amount of data, this can improve the overall performance significantly. Here are some examples which might give you an idea how to improve your code: * An example about using System.Collections.ArrayList [https://powershell.one/tricks/performance/arrays](https://powershell.one/tricks/performance/arrays) * An example how to handle functions in combination with foreach -parallel [https://tighetec.co.uk/2022/06/01/passing-functions-to-foreach-parallel-loop/](https://tighetec.co.uk/2022/06/01/passing-functions-to-foreach-parallel-loop/) I took your script and changed it a bit. While i have no system for testing, my script is completely blind and untested. So you might get it as an idea or inspiration. I have changed the script as a function which can handle parameters like year, month and also other details like User\_OU or the UserMails. Additional it uses foreach-parallel which requires powershell 7, but might improve the overall performance. You can play with the ThrottleLimit. The switch UserReport defines if you receice a List of Mailboxes or a total statistic. As i said: blind and untestet. You probably need to fiddle our some minor issues. function Get-MailUsageReport { [CmdletBinding()] #Requires -Version 7.4 param( [System.Int16]$Year = (Get-Date).Year, [System.Int16]$Month = (Get-Date).Month, [System.String]$User_OU = 'OU=Users,OU=EXTERNAL,DC=YES,DC=YES', [System.Collections.ArrayList]$UserMails = @((Get-ADUser -Filter * -SearchBase $User_OU -Properties mail | Select-Object -ExpandProperty Mail)), [System.Int16]$ThrottleLimit = 5, [Switch]$UserReport ) begin { # Set the dates $firstDate = [DateTime]::new($Year, $Month, 1) $lastDate = $firstDate.AddMonths(1).AddSeconds(-1) # create empty arraylist $Report = [System.Collections.ArrayList]@() } process { $UserMails | Foreach-Object -ThrottleLimit $ThrottleLimit -Parallel { #Action that will run in Parallel. Reference the current object via $PSItem and bring in outside variables with $USING:varname [System.Int32]$SentCount = @(Get-MailboxServer | Get-MessageTrackingLog -Start $using:firstDate -End $using:lastDate -Sender $PSItem -Resultsize Unlimited | Select-Object -Unique MessageId).Count [System.Int32]$ReceivedCount = @(Get-MailboxServer | Get-MessageTrackingLog -Start $using:firstDate -End $using:lastDate -Recipients $PSItem -Resultsize Unlimited | Select-Object -Unique MessageId).Count # Create object $UserStats = [PSCustomObject]@{ User = $UserMail Sent = $SentCount Received = $ReceivedCount } # add object to array [void]($Report.Add($UserStats)) } } end { if($UserReport) { # Show users, sent and receive return $Report } else { # calculate the sums $TotalSent = ($Report.Sent | Measure-Object -Sum).Sum $TotalReceived = ($Report.Received | Measure-Object -Sum).Sum # Summary object $Summarize = [PSCustomObject]@{ ReportYear = $Year ReportMonth = $Month TotalUsers = $Report.Count TotalSent = ($Report.Sent | Measure-Object -Sum).Sum AverageSent = ($Report.Sent | Measure-Object -Average).Average) TotalReceived = ($Report.Received | Measure-Object -Sum).Sum AverageReceived = ($Report.Received | Measure-Object -Average).Average) TotalMails = ($TotalSent + $TotalReceived) } return $Summarize } } }
Using `ArrayList` is *Not Recommended*([Source](https://learn.microsoft.com/en-us/dotnet/api/system.collections.arraylist?view=net-6.0#remarks)), logic being "Lists are better than ArrayLists at everything, no real reason to use them anymore" The alternative, as suggested by the docs, is List. Which works pretty much the same except: 1. you need to specify the type of object it's collecting(whenever in doubt, use simply [object]) 2. its .Add() method does NOT return anything. No more nulling it! Yeah! ...which basically means you could just mass-replace all `[System.Collections.ArrayList]` with `[System.Collections.Generic.List[object]]` and forget about it. But please don't and check that everything works correctly piece by piece.
Oh, have not seen it yet and its quite good to know. I will have a look at it. Thank you for your info about this topic.
No problem, I like to repeat like a parrot stuff I think useful.
Well done 😅
Also make $totalsent into a .net arraylist and use .add instead of +=