locked
Using Powershell to parse Transport log to calculate the number of Email from internal and external domain ? RRS feed

  • Question

  • Hi People,

    I need your help in modifying the below script to calculate the total email sent to a distribution group of what has originated outside of the organization as opposed to inside the organisation.

    Get-TransportServer | Get-MessageTrackingLog -resultsize unlimited -start "2011-07-25 00:01" -end "2011-08-8 23:59" -EventId Expand | Group-Object RelatedRecipientAddress | Select-Object Name, Count, @{n="totalMBs"; e={[int](($_.group | Measure-Object -Property totalbytes -Sum).sum/1mb)}} | Sort Name | Export-Csv C:\EmailStat.csv
    

    so the result would be something like this pretty much:

    Name     : group1@domain.com

    Count    : 12
    totalMBs : 23
    From Internal @domain.com : 10  
    From External : 2

    Name     : group2@domain.com
    Count    : 56
    totalMBs : 235
    From Internal @domain.com : 48
    From External : 8

     

    Any help would be greatly appreciated.

    Thanks.

     


    /* Server Support Specialist */
    Wednesday, August 10, 2011 5:52 AM

Answers

  • Hi Albert,

     

    I tested in my lab, you can use these commands to calculate the number of email from internal and external domain:

     

    $DG=get-distributiongroup

    $incount=0

    $excount=0

    foreach ($item in $DG)

    {

    $msg=get-messagetrackinglog -start "2011-07-25 00:01" -end "2011-08-8 23:59"-eventid receive -recipients $item.primarySMTPaddress.tostring()

     

    $Domainname=get-AcceptedDomain |% {$_.domainname.domain}

     

     $Domain="*@"+$Domainname

     

     foreach ($m in $msg){

     

     if ($m.sender -inotlike "$Domain"){

     $excount+=1

     }

     if ($m.sender -ilike "$Domain"){

     $incount+=1

     }

    }

     

    "Distribution Group: "+$item.name

    "Internal: " +$incount

    "External: " +$excount

    $incount=0

    $excount=0

    }

     

    Thanks,

     

    Evan Liu

     

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com  

    Monday, August 15, 2011 11:02 AM
  • Hi Albert,

     

    Thanks for your reply.

     

    I use RECEIVE for this reason:

     

    $msg=get-messagetrackinglog -start "2011-07-25 00:01" -end "2011-08-8 23:59"-eventid receive -recipients $item.primarySMTPaddress.tostring()

     

    Use Expand, the Recipients will be expanded, that all the members’ email address will be included. This is not easy for me to get the $msg by recipients.

     

    When use RECEIVE, the Recipients will not expanded, it only the group email address.

     

    Per my knowledge, use RECEIVE or EXPAND will not affect calculate the number of Email from internal and external domain.

     

    Thanks,

     

    Evan Liu

     

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com  

    Tuesday, August 16, 2011 1:59 AM

All replies

  • Hi Albert,

     

    For this issue, please have a read for this document, this is a very good script:

     

    Exchange 2007/2010 Email stats

    http://gallery.technet.microsoft.com/scriptcenter/bb94b422-eb9e-4c53-a454-f7da6ddfb5d6?SRC=Home

     

    I will try to modify your script in my lab, and update the post when I get the result.

     

    Thanks,

     

    Evan Liu

     

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com  

    Thursday, August 11, 2011 7:13 AM
  • Yes, I have tried that too but it is too general, what I'd like to do is to get targetted result for distribution group only and separated by domain.com (internal) while the rest can be categorized as external.
    /* Server Support Specialist */
    Thursday, August 11, 2011 7:15 AM
  • Hi Albert,

     

    I tested in my lab, you can use these commands to calculate the number of email from internal and external domain:

     

    $DG=get-distributiongroup

    $incount=0

    $excount=0

    foreach ($item in $DG)

    {

    $msg=get-messagetrackinglog -start "2011-07-25 00:01" -end "2011-08-8 23:59"-eventid receive -recipients $item.primarySMTPaddress.tostring()

     

    $Domainname=get-AcceptedDomain |% {$_.domainname.domain}

     

     $Domain="*@"+$Domainname

     

     foreach ($m in $msg){

     

     if ($m.sender -inotlike "$Domain"){

     $excount+=1

     }

     if ($m.sender -ilike "$Domain"){

     $incount+=1

     }

    }

     

    "Distribution Group: "+$item.name

    "Internal: " +$incount

    "External: " +$excount

    $incount=0

    $excount=0

    }

     

    Thanks,

     

    Evan Liu

     

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com  

    Monday, August 15, 2011 11:02 AM
  • Many thanks for the reply man, however does the EventID matters ?

    I'm confused in using EXPAND or RECEIVE to get the count for the distribution group.


    /* Server Support Specialist */
    Tuesday, August 16, 2011 12:53 AM
  • Hi Albert,

     

    Thanks for your reply.

     

    I use RECEIVE for this reason:

     

    $msg=get-messagetrackinglog -start "2011-07-25 00:01" -end "2011-08-8 23:59"-eventid receive -recipients $item.primarySMTPaddress.tostring()

     

    Use Expand, the Recipients will be expanded, that all the members’ email address will be included. This is not easy for me to get the $msg by recipients.

     

    When use RECEIVE, the Recipients will not expanded, it only the group email address.

     

    Per my knowledge, use RECEIVE or EXPAND will not affect calculate the number of Email from internal and external domain.

     

    Thanks,

     

    Evan Liu

     

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com  

    Tuesday, August 16, 2011 1:59 AM
  • ah, OK now I see the difference.

    Many thanks once again for the explanation man.


    /* Server Support Specialist */
    Tuesday, August 16, 2011 2:04 AM
  • Hi Evan,

    I came across your code and it's exactly what I'm looking for.  Although in my case, it doesn't seem to be enumerating the domain properly.  All counts are 0 for internal, although I see a number for external.  When I hard code a domain for $DomainName, it seems to work, but I am also getting incorrect counts.

    When I run get-AcceptedDomain |% {$_.domainname.domain}, it displays our 5 domains.

    As far as the incorrect count.  Groups that haven't received anything still show as 1 external message.  Are you available to assist?

    Thanks!

    Thursday, September 13, 2012 8:49 PM