Counting Attachments form emailboxes

PowerShell Help
1

I have a PS script that counts inbox emails and outbox emails.

$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Inbound’]++
$row.Inbound = $MailTraffic[$RecipientName][$Date].Inbound

vv^^ this work fine vv^^

$MailTraffic[$Message.SenderAddress][$MessageDate][‘Outbound’]++

$row.Outbound = ($MailTraffic[$RecipientName][$Date].Outbound)

$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Attachments’]++
$row.InboundAttachments = $MailTraffic[$RecipientName][$Date].Attachments

^^This kind of works. This accurately count all the attachment but it counts screen prints, signature logos, facebook logos, Twitter logos and etc. I just want it to count just the .doc, .xls, .pdf, .txt. Is there a way to do that?

2

Just filter out the other stuff or filter for only what you want.

Of course in HTML based mail, any embedded picture, etc., as part of the signature of embedded body is an attachment.

Something like (or other filter approach) …

($MailTraffic[$RecipientName][$Date].Attachments) -Match '.doc|.xls|.pdf|.txt'
3
Hi, I liked that idea but it didn't work so great. Here's my code
$OutputFile = "folder name" $run_date = Get-Date -format g $StartDate = (Get-Date).AddDays(-1).ToString("MM/dd/yyy") $EndDate = (Get-Date).ToString("MM/dd/yyy")

$SecureOffice365AdminPassword = ConvertTo-SecureString -AsPlainText “password” -Force

$Office365Credentials = New-Object System.Management.Automation.PSCredential “email@email.com”, $SecureOffice365AdminPassword

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $Office365credentials -Authentication Basic –AllowRedirection
Import-PSSession $Session -AllowClobber | Out-Null

$Recipients = Get-Recipient -ResultSize Unlimited | select PrimarySMTPAddress

$MailTraffic = @{} foreach($Recipient in $Recipients) { $MailTraffic[$Recipient.PrimarySMTPAddress.ToLower()] = @{} } $Recipients = $null

$Messages = $null
$Page = 1
do
{
Write-Host “Collecting Message Tracking - Page $Page…”
$CurrMessages = Get-MessageTrace -PageSize 5000 -StartDate $StartDate -EndDate $EndDate -Page $Page | Select Received,SenderAddress,RecipientAddress,Size
$Page++
$Messages += $CurrMessages
}
until ($CurrMessages -eq $null)

Remove-PSSession $session

foreach($Message in $Messages)
{
if ($Message.SenderAddress -ne $null)
{
if ($MailTraffic.ContainsKey($Message.SenderAddress))
{
$MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

if ($MailTraffic[$Message.SenderAddress].ContainsKey($MessageDate))
{
$MailTraffic[$Message.SenderAddress][$MessageDate][‘Outbound’]++
$MailTraffic[$Message.SenderAddress][$MessageDate][‘OutboundSize’] += $Message.Size
$MailTraffic[$Message.SenderAddress][$MessageDate][‘Attachments’] ++
$MailTraffic[$Message.SenderAddress][$MessageDate][‘AttachmentsSize’] += $Message.Size ## CGW

} else { $MailTraffic[$Message.SenderAddress][$MessageDate] = @{} $MailTraffic[$Message.SenderAddress][$MessageDate]['Outbound'] = 1 $MailTraffic[$Message.SenderAddress][$MessageDate]['Inbound'] = 0 $MailTraffic[$Message.SenderAddress][$MessageDate]['InboundSize'] = 0 $MailTraffic[$Message.SenderAddress][$MessageDate]['OutboundSize'] += $Message.Size }

}
}

if ($Message.RecipientAddress -ne $null)
{
if ($MailTraffic.ContainsKey($Message.RecipientAddress))
{
$MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

if ($MailTraffic[$Message.RecipientAddress].ContainsKey($MessageDate))
{
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Inbound’]++
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘InboundSize’] += $Message.Size
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Attachments’]++
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘AttachmentsSize’] += $Message.Size
}
else
{
$MailTraffic[$Message.RecipientAddress][$MessageDate] = @{}
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Inbound’] = 1
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Outbound’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Attachment’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘OutboundSize’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘InboundSize’] += $Message.Size
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘AttachmentsSize’] = 0

} } }

if ($Message.RecipientAddress -ne $null)
{
if ($MailTraffic.ContainsKey($Message.RecipientAddress))
{
$MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

if ($MailTraffic[$Message.RecipientAddress].ContainsKey($MessageDate))
{
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Attachments’]++
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘AttachmentsSize’] += $Message.Size
}
else
{
$MailTraffic[$Message.RecipientAddress][$MessageDate] = @{}
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Inbound’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Outbound’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘Attachments’] = 1
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘OutboundSize’] = 0
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘InboundSize’] += $Message.Size
$MailTraffic[$Message.RecipientAddress][$MessageDate][‘AttachmentsSize’] = 0

} } } }

$table = New-Object system.Data.DataTable “MailboxStats”
$col1 = New-Object system.Data.DataColumn Date,([datetime])
$table.columns.add($col1)
$col2 = New-Object system.Data.DataColumn Recipient,([string])
$table.columns.add($col2)
$col3 = New-Object system.Data.DataColumn Inbound,([int])
$table.columns.add($col3)
$col4 = New-Object system.Data.DataColumn Outbound,([int])
$table.columns.add($col4)
$col5 = New-Object system.Data.DataColumn InboundAttachments,([int])
$table.columns.add($col5)

ForEach ($Recipient in $MailTraffic.keys)
{
if (($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’))
{
$RecipientName = $Recipient

foreach($Date in $MailTraffic[$RecipientName].keys)
{
$row = $table.NewRow()
$row.Date = $Date
$row.Recipient = $RecipientName
$row.Inbound = $MailTraffic[$RecipientName][$Date].Inbound
$row.InboundAttachments = $MailTraffic[$RecipientName][$Date].Attachments -Match ‘.doc|.xls|.pdf|.txt’ <----didn’t work :frowning:

if (($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’) -Or ($Recipient -eq ‘email@email.com’)) {
if ($MailTraffic[$RecipientName][$Date].Outbound - $MailTraffic[$RecipientName][$Date].Inbound -lt 0) {
$row.Outbound = (($MailTraffic[$RecipientName][$Date].Outbound) - ($MailTraffic[$RecipientName][$Date].Inbound)) * -1
}
else {
$row.Outbound = ($MailTraffic[$RecipientName][$Date].Outbound) - ($MailTraffic[$RecipientName][$Date].Inbound)
}
} else {
$row.Outbound = ($MailTraffic[$RecipientName][$Date].Outbound)
}

$table.Rows.Add($row)
}
}
}

4

Hi @Fenderjaz20 , Do you think anyone will put effort to read the code posted above unless you format it properly ?. I request you to format the above code. Please let us know if you find any difficulty in formatting the code.

See below on how to format the code

5

Or put your code in Visual Studio Code and then select all and right click to format the selection, then copy and paste back here wrapped in the tags as kvprasoon is showing.

...

# one should never put plaintext passwords in a script, always prompt for it 
# or select it from a password store, like Windows Credential Manager, or 
# secure file.
# $SecureOffice365AdminPassword = ConvertTo-SecureString -AsPlainText "password" -Force
$SecureOffice365AdminPassword = (Get-Credential -Credential 'admin@yourdomainname.onmicrosoft.com').GetNetworkCredential().password

...
    # eliminate all the extraneous 'Or', to the method below
    if ($Recipient -eq 'email001@email.com|email002@email.com') ... 
...
            # not this way
            # $row.InboundAttachments = $MailTraffic[$RecipientName][$Date].Attachments -Match '.doc|.xls|.pdf|.txt' 
            # try this way... you have to get a collection to match from a collection.
            $row.InboundAttachments = ($MailTraffic[$RecipientName][$Date].Attachments) -Match '.doc|.xls|.pdf|.txt'   
...
            if ($Recipient -eq 'email001@email.com|email002@email.com') ...
}
6 
$OutputFile = "c:\scripts\MailboxStats2.csv"
$run_date = Get-Date -format g
$StartDate = (Get-Date).AddDays(-1).ToString("MM/dd/yyy")
$EndDate = (Get-Date).ToString("MM/dd/yyy")

$SecureOffice365AdminPassword = ConvertTo-SecureString -AsPlainText "password" -Force

$Office365Credentials = New-Object System.Management.Automation.PSCredential "email@email.com", $SecureOffice365AdminPassword

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https//ps.outlook.com/powershell -Credential $Office365credentials -Authentication Basic –AllowRedirection

Import-PSSession $Session -AllowClobber | Out-Null

$Recipients = Get-Recipient -ResultSize Unlimited | select PrimarySMTPAddress

$MailTraffic = @{}
foreach($Recipient in $Recipients)
{
     $MailTraffic[$Recipient.PrimarySMTPAddress.ToLower()] = @{}
}
$Recipients = $null

#Collect Message Tracking Logs (These are broken into "pages" in Office 365 so we need to collect them all with a loop)
$Messages = $null
$Page = 1
Do
{
     Write-Host "Collecting Message Tracking - Page $Page..."
     $CurrMessages = Get-MessageTrace -PageSize 5000 -StartDate $StartDate -EndDate $EndDate -Page $Page | Select Received,SenderAddress,RecipientAddress,Size
     $Page++
     $Messages += $CurrMessages
}
until ($CurrMessages -eq $null)

Remove-PSSession $session

foreach($Message in $Messages)
{
     If ($Message.SenderAddress -ne $null)
     {
          If ($MailTraffic.ContainsKey($Message.SenderAddress))
          {
               $MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

               If ($MailTraffic[$Message.SenderAddress].ContainsKey($MessageDate))
               {
                     $MailTraffic[$Message.SenderAddress][$MessageDate]['Outbound']++
                     $MailTraffic[$Message.SenderAddress][$MessageDate]['OutboundSize'] += $Message.Size
                     $MailTraffic[$Message.SenderAddress][$MessageDate]['Attachments'] ++
                     $MailTraffic[$Message.SenderAddress][$MessageDate]['AttachmentsSize'] += $Message.Size

               }
               Else
               {
                    $MailTraffic[$Message.SenderAddress][$MessageDate] = @{}
                    $MailTraffic[$Message.SenderAddress][$MessageDate]['Outbound'] = 1
                    $MailTraffic[$Message.SenderAddress][$MessageDate]['Inbound'] = 0
                    $MailTraffic[$Message.SenderAddress][$MessageDate]['InboundSize'] = 0
                    $MailTraffic[$Message.SenderAddress][$MessageDate]['OutboundSize'] += $Message.Size
                }

           }
     }

     If ($Message.RecipientAddress -ne $null)
     {
          If ($MailTraffic.ContainsKey($Message.RecipientAddress))
          {
               $MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

                    If ($MailTraffic[$Message.RecipientAddress].ContainsKey($MessageDate))
                         {
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['Inbound']++
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['InboundSize'] += $Message.Size
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['Attachments']++
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['AttachmentsSize'] += $Message.Size
                         }
                         Else
                         {
                              $MailTraffic[$Message.RecipientAddress][$MessageDate] = @{}
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['Inbound'] = 1
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['Outbound'] = 0
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['Attachment'] = 0
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['OutboundSize'] = 0
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['InboundSize'] += $Message.Size
                              $MailTraffic[$Message.RecipientAddress][$MessageDate]['AttachmentsSize'] = 0

                         }
                    }
               }

               If ($Message.RecipientAddress -ne $null)
               {
                    If ($MailTraffic.ContainsKey($Message.RecipientAddress))
                    {
                         $MessageDate = Get-Date -Date $Message.Received -Format yyyy-MM-dd

                              If ($MailTraffic[$Message.RecipientAddress].ContainsKey($MessageDate))
                              {
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['Attachments']++
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['AttachmentsSize'] += $Message.Size
                              }
                              Else
                              {
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate] = @{}
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['Inbound'] = 0
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['Outbound'] = 0
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['Attachments'] = 1
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['OutboundSize'] = 0
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['InboundSize'] += $Message.Size
                                   $MailTraffic[$Message.RecipientAddress][$MessageDate]['AttachmentsSize'] = 0

                               }
                          }
                     }
                }

$table = New-Object system.Data.DataTable "MailboxStats"
$col1 = New-Object system.Data.DataColumn Date,([datetime])
$table.columns.add($col1)
$col2 = New-Object system.Data.DataColumn Recipient,([string])
$table.columns.add($col2)
$col3 = New-Object system.Data.DataColumn Inbound,([int])
$table.columns.add($col3)
$col4 = New-Object system.Data.DataColumn Outbound,([int])
$table.columns.add($col4)
$col5 = New-Object system.Data.DataColumn InboundAttachments,([int])
$table.columns.add($col5)

#Transpose hashtable to datatable
ForEach ($Recipient in $MailTraffic.keys)
{
     if (($Recipient -eq 'email@email.com') -Or ($Recipient -eq 'email@email.com') )
     {
          $RecipientName = $Recipient

          foreach($Date in $MailTraffic[$RecipientName].keys)
          {
               $row = $table.NewRow()
               $row.Date = $Date
               $row.Recipient = $RecipientName
               $row.Inbound = $MailTraffic[$RecipientName][$Date].Inbound
               $row.InboundAttachments = $MailTraffic[$RecipientName][$Date].Attachments

               if (($Recipient -eq 'email@email.com') -Or ($Recipient -eq 'email@email.com')) {
                    if ($MailTraffic[$RecipientName][$Date].Outbound - $MailTraffic[$RecipientName][$Date].Inbound -lt 0) {
                       $row.Outbound = (($MailTraffic[$RecipientName][$Date].Outbound) - ($MailTraffic[$RecipientName][$Date].Inbound)) * -1
                    }
                    else {
                         $row.Outbound = ($MailTraffic[$RecipientName][$Date].Outbound) - ($MailTraffic[$RecipientName][$Date].Inbound)
                    }
                    } else {
                         $row.Outbound = ($MailTraffic[$RecipientName][$Date].Outbound)
                    }

                    $table.Rows.Add($row)
               }
          }
     }
$table | sort Recipient,Date,Inbound,Outbound | export-csv $OutputFile