1

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

Uke
  • 169
  • 3
  • 21

2 Answers2

2

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"
Richard
  • 6,812
  • 5
  • 45
  • 60
0

try it:

 $mails = ("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


 Get-MessageTrackingLog -ResultSize Unlimited -Start $startofmonth -End  $endofmonth -EventId RECEIVE | where{$_.Recipients -in   $mails} | select- object  Timestamp,SourceContext,Source,EventId,MessageSubject,Sender, {$_.Recipients} | 
group-object -property Timestamp |
export-csv C:\temp\MessageTrackingLogResults.csv
Esperento57
  • 16,521
  • 3
  • 39
  • 45
  • this would give me the messages itself - I'm only interested in the count of the mails per month. To track how much was sent to each specific account. – Uke Oct 06 '16 at 10:44
  • there is a space too much between select- and object. Also the result would be: #TYPE Microsoft.PowerShell.Commands.GroupInfo "Values","Count","Group","Name" "System.Collections.ArrayList","1","System.Collections.ObjectModel.Collection`1[System.Management.Automation.PSObject]","04.10.2016 13:20:34" – Uke Oct 06 '16 at 13:44