I am trying to get a monthly report of received e-mails from a list of e-mail-addresses. I want to export the results to a .csv and if possible send the csv automatically via e-mail to another address.
This is my approach in the exchange-management-shell looks like this but isn't creating any data in the .csv
$mail = @("mymail@mydomain.com","another@mydomain.com")
$(foreach ($name in $mail)
{
$startofmonth = Get-Date -format MM/dd/yyyy -day 1 -hour 0 -minute 0 -second 0
$endofmonth = ((Get-Date -day 1 -hour 0 -minute 0 -second 0).AddMonths(1).AddSeconds(-1))
$endofmonth = "{0:MM/dd/yy}" -f [datetime]$endofmonth
$results = (Get-MessageTrackingLog -ResultSize unlimited -recipients $name -Start $startofmonth -End $endofmonth -EventId RECEIVE).count
$results
}) | Export-CSV -path \\mymachine\c$\output.csv -NoTypeInformation
How do I get the data that I want like this in the .csv:
mailaddress | count(received mails) | timeframe
You can create a custom object for each result and return that to an array $output
. Then convert the array into a CSV
format and output it to a file.
$mail = @("mymail@mydomain.com","another@mydomain.com")
$startofmonth = Get-Date -format MM/dd/yyyy -day 1 -hour 0 -minute 0 -second 0
$endofmonth = ((Get-Date -day 1 -hour 0 -minute 0 -second 0).AddMonths(1).AddSeconds(-1))
$endofmonth = "{0:MM/dd/yy}" -f [datetime]$endofmonth
[Array]$output = foreach ($name in $mail) {
$results = (Get-MessageTrackingLog -ResultSize unlimited -recipients $name -Start $startofmonth -End $endofmonth -EventId RECEIVE).count
[pscustomobject]@{
Name=$name;
Received=$results;
StartTime=$startofmonth;
EndTime=$endofmonth;
}
}
$output | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath \\mymachine\c$\output.csv -Append
This will return a CSV
file that looks like this:
"Name","Received","StartTime","EndTime"
"mymail@mydomain.com","222","10/01/2016","10/31/16"
"another@mydomain.com","340","10/01/2016","10/31/16"