Counting Attachments form emailboxes

Welcome Forums General PowerShell Q&A Counting Attachments form emailboxes

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
1 month, 1 week ago.

  • Author
    Posts
  • #118909

    Participant
    Points: 19
    Rank: Member

    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?

  • #118981

    Participant
    Points: 316
    Helping Hand
    Rank: Contributor

    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'
    
  • #119214

    Participant
    Points: 19
    Rank: Member
    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    🙁 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) } } }

  • #119215

    Participant
    Points: 832
    Helping Hand
    Rank: Major Contributor

    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

  • #119266

    Participant
    Points: 316
    Helping Hand
    Rank: Contributor

    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') ...
    }
    
  • #119668

    Participant
    Points: 19
    Rank: Member
    $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

You must be logged in to reply to this topic.