I need help to modify PowerShell script to take multiple input from .CSV to get email statistics and export as Excel with Multiple Worksheet ?

Senior IT System Engineer
Senior IT System Engineer used Ask the Experts™
on
People,

I need some assistance in combining or modifying the below PowerShell script to:
Display Start and End time frame at the beginning of the display result in DD/MM/YYYY HH:MM
Show the result like in the Desired Output section.

The script below is to get all external email address that was delivered to my mailbox in the past 1 days:

$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)

Write-host "Time window between $start until $end :"

foreach ($email in (Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv")) {
    $trackparams = @{
	    start = $start
	    end = $end
	    resultsize = 'Unlimited'
        EventId = 'DELIVER'
	    Recipients = $email.email
    }

	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams

	ForEach ($Domain in (Get-AcceptedDomain).Name){
		$Messages = $Messages | where {$_.Sender -notlike "*$Domain"}
	}

    Write-host "Email address: $($email.email) :"

	$Messages | 
	Group-Object -Property Sender | 
	Select-Object Name, Count | 
	Sort-Object Count -Descending
}

Open in new window


Existing Output on the console only:
Name                                 Count
----                                 -----
alerts-noreply@mail.windowsazure.com  7364
no-reply@email.microsoftazure.com     5256
alerts@email.office365.com            2368
....

Open in new window


Desired Output in Excel spreadsheet if possible:
Timeframe between: 13-03-2018 00:01 until 13-04-2018 13:00

Worksheet name: DistributionGroup1@myDomain.com
Name                                 Count
----                                 -----
alerts-noreply@mail.windowsazure.com  7364
no-reply@email.microsoftazure.com     5256
alerts@email.office365.com            2368
....

Worksheet name: DistributionGroup2@myDomain.com
Name                                 Count
----                                 -----
alerts-noreply@mail.windowsazure.com  2345
no-reply@email.microsoftazure.com     2121
alerts@email.office365.com            1134
....

Worksheet name: PersonX@Domain.com
Name                                 Count
----                                 -----
alerts-noreply@mail.azure.com         9832
no-reply@email.microsoft.com          7465
alerts@office365.com                  4324
....

Open in new window


Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)

Write-host "Time window between $start until $end :"

foreach ($email in (Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv")) {
    $trackparams = @{
	    start = $start.ToString("dd/MM/yyyy HH:MM")
	    end = $end.ToString("dd/MM/yyyy HH:MM")
	    resultsize = 'Unlimited'
        EventId = 'DELIVER'
	    Recipients = $email.email
    }

	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams

	ForEach ($Domain in (Get-AcceptedDomain).Name){
		$Messages = $Messages | where {$_.Sender -notlike "*$Domain"}
	}

    Write-host "Email address: $($email.email) :"

	$Messages | 
	Group-Object -Property Sender | 
	Select-Object Name, Count | 
	Sort-Object Count -Descending
}

Open in new window

Author

Commented:
Jose,

Thanks for the reply, but somehow it failed to run since the date parameter was converted from the American MM/DD/YYY into the display format DD/MM/YYYY ?

Cannot process argument transformation on parameter 'End'. Cannot convert value "13/04/2018 17:04" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    + CategoryInfo          : InvalidData: (:) [Get-MessageTrackingLog], ParameterBindin...mationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-MessageTrackingLog

I need the conversion to happens at the beginning of the display result not at the Message Tracking log filtering parameters.
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018
Commented:
I think I get it, but the point is that you can't just change the date at the beginning, you can change it at the moment it reports to CSV or to another point, but if we change it into the tracking log, since it failed to get the "US" culture date the cmdlet will fail (as we saw).
in the beginning, you change it but nothing would work because you change it from the US.

Probably to show it in the screen you can have something like this:

$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)

Write-host "Time window between $($start.ToString("dd/MM/yyyy HH:MM")) until $($end.ToString("dd/MM/yyyy HH:MM")):"

foreach ($email in (Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv")) {
    $trackparams = @{
	    start = $start
	    end = $end
	    resultsize = 'Unlimited'
        EventId = 'DELIVER'
	    Recipients = $email.email
    }

	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams

	ForEach ($Domain in (Get-AcceptedDomain).Name){
		$Messages = $Messages | where {$_.Sender -notlike "*$Domain"}
	}

    Write-host "Email address: $($email.email) :"

	$Messages | 
	Group-Object -Property Sender | 
	Select-Object Name, Count | 
	Sort-Object Count -Descending
}

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Jose,

yes, it works for the date display format.

I wonder if it is possible to export the result into one single Excel Spreadsheet and each line of Email address statistics is written to each own Worksheet ?
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
It's possible but working with excel on PowerShell is really really annoying and I'll avoid doing that for free

Author

Commented:
what about using the below snippets ?

#=============================================================================
# Convert powershell Object to Array for Excel
#=============================================================================
function ConvertTo-MultiArray {
 <#
    .Notes
        NAME: ConvertTo-MultiArray
        AUTHOR: Tome Tanasovski
        Website: http://powertoe.wordpress.com
        Twitter: http://twitter.com/toenuff
        Version: 1.2
    .Synopsis
        Converts a collection of PowerShell objects into a multi-dimensional array

    .Description
        Converts a collection of PowerShell objects into a multi-dimensional array.  The first row of the array contains the property names.  Each additional row contains the values for each object.

        This cmdlet was created to act as an intermediary to importing PowerShell objects into a range of cells in Exchange.  By using a multi-dimensional array you can greatly speed up the process of adding data to Excel through the Excel COM objects.

    .Parameter InputObject
        Specifies the objects to export into the multi dimensional array.  Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to ConvertTo-MultiArray.

    .Inputs
        System.Management.Automation.PSObject
        You can pipe any .NET Framework object to ConvertTo-MultiArray

    .Outputs
        [ref]
        The cmdlet will return a reference to the multi-dimensional array.  To access the array itself you will need to use the Value property of the reference

    .Example
        $arrayref = get-process |Convertto-MultiArray

    .Example
        $dir = Get-ChildItem c:\
        $arrayref = Convertto-MultiArray -InputObject $dir

    .Example
        $range.value2 = (ConvertTo-MultiArray (get-process)).value

    .LINK
        http://powertoe.wordpress.com

#>
    param(
        [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
        [PSObject[]]$InputObject
    )
    BEGIN {
        $objects = @()
        [ref]$array = [ref]$null
    }
    Process {
        $objects += $InputObject
    }
    END {
        $properties = $objects[0].psobject.properties |%{$_.name}
        $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
        # i = row and j = column
        $j = 0
        $properties |%{
            $array.Value[0,$j] = $_.tostring()
            $j++
        }
        $i = 1
        $objects |% {
            $item = $_
            $j = 0
            $properties | % {
                if ($item.($_) -eq $null) {
                    $array.value[$i,$j] = ""
                }
                else {
                    $array.value[$i,$j] = $item.($_).tostring()
                }
                $j++
            }
            $i++
        }
        $array
    }
}
    #=============================================================================
    # Export pipe in Excel file
    #=============================================================================
    function Export-Excel {
        [cmdletBinding()]
        Param(
            [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
            [PSObject[]]$InputObject
        )
        begin{
            $header=$null
            $row=1
            $xl=New-Object -ComObject Excel.Application
            $wb=$xl.WorkBooks.add(1)
            $ws=$wb.WorkSheets.item(1)
            $xl.Visible=$false
            $xl.DisplayAlerts = $false
            $xl.ScreenUpdating = $False
            $objects = @()

            }
        process{
            $objects += $InputObject

        }
        end{
            $array4XL = ($objects | ConvertTo-MultiArray).value

            $starta = [int][char]'a' - 1
            if ($array4XL.GetLength(1) -gt 26) {
                $col = [char]([int][math]::Floor($array4XL.GetLength(1)/26) + $starta) + [char](($array4XL.GetLength(1)%26) + $Starta)
            } else {
                $col = [char]($array4XL.GetLength(1) + $starta)
            }
            $ws.Range("a1","$col$($array4XL.GetLength(0))").value2=$array4XL

            $wb.SaveAs("$([Environment]::GetFolderPath('desktop'))\Export-Excel ($(Get-Date -Format u)).xlsx")
            $xl.Quit()
            Remove-Variable xl
        }
    }

Open in new window

Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
As said, I'm not working with Excel COM Objects for free.

What I can Offer is a solution to get the CSV Information and that info can be imported into Excel using the Data/TextFile Option.

$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)


Write-host "Time window between $($start.ToString("dd/MM/yyyy HH:MM")) until $($end.ToString("dd/MM/yyyy HH:MM")):"

$AllMessages=@()

foreach ($email in (Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv")) {
    $trackparams = @{
	    start = $start
	    end = $end
	    resultsize = 'Unlimited'
        EventId = 'DELIVER'
	    Recipients = $email.email
    }

	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams

	ForEach ($Domain in (Get-AcceptedDomain).Name){
		$Messages = $Messages | where {$_.Sender -notlike "*$Domain"}
	}

    Write-host "Email address: $($email.email) :"

	$allMessages+=$Messages | Group-Object -Property Sender | Select-Object Name, Count | Sort-Object Count -Descending
}


$AllMessages | ConvertTo-Csv -NoTypeInformation | Out-File Output.csv

Open in new window

Author

Commented:
Jose,

thanks for the reply, however, it gives me the below error:

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
   + CategoryInfo          : InvalidArgument: (Exc2010-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
Does "$env:USERPROFILE\Desktop\trackingemail.csv" exists?

this means that the current profile, on the desktop must have a trackingemail.csv file.

Author

Commented:
Yes, the file is there and contains some email address.
Have you tried on your Exchange 2013 Environment ?
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
The script works if you have your input CSV file created correctly (you need to have a header line with the column email), and only one email address stored there. If there are more, results are cummulated without keeping the email reference.


To allow Excel export, I use my own function similar to what has been posted above - let's call the file Obj2XLS Pipe.ps1:
# Export object from pipe to Excel sheet

<# Example use:
  (new-object PSObject -Property @{a = "A" ; b = 1.5 }),
  (new-object PSObject -Property @{a = "NA"; b = 2   }) |
    export-xls "C:\temp\ee\tst.xlsx"
#>

<# To Do:
  optional header format, like Interior.ColorIndex, Font.ColorIndex, ...
  optional table formatting (script block)
#>
<# Note: If $SheetName is provided, the workbook will be saved but not closed
   to speed up processing.
   Also make sure to use PowerShell 4 or later for better Automation performance
#>
function export-xls ([String] $xlsFile, [String] $sheetName)
{
begin {
  # Excel initialization stuff
  # use global Excel instance object, if already set
  if (!$global:excel)
  {
    $global:excel = New-Object -ComObject excel.application
  }
  $excel.visible=$true

  # Open existing workbook or create new
  if (Test-Path $xlsFile)
  {
    $wb = $excel.Workbooks.Open($xlsFile)
  } else {
  	$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

    #   Delete all worksheets but one
    $excel.DisplayAlerts = $false
    for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
    $excel.DisplayAlerts = $true
    $ws = $wb.Worksheets.Item(1)
    if ($sheetName) { $ws.Name = $sheetName }
    $wb.SaveAs($xlsFile)
  }
  # if sheet should be named in an existing workbook, search its name
  if ($sheetName -and !$ws) { $ws = $wb.Sheets | ? { $_.Name -eq $sheetName } }
  # append a new sheet if not found (and workbook is not new)
  if (!$ws)
  {
    $lastWS = $wb.WorkSheets.Item($wb.WorkSheets.Count)
    $ws = $wb.WorkSheets.Add($lastWS)
    $lastWS.Move($ws)
  }

  # name it if a name has been provided
  if ($sheetName) { $ws.Name = $sheetName }
	$props = $null
	$row = 1
}
process {
	if (!$props) {
		$col = 1
		$props = $_.PsObject.Properties
		$props | % {
			$ws.Cells.Item($row, $col).Value = $_.Name
			$ws.Cells.Item($row, $col++).Font.Bold = $true
		}
		$row++
	}
	$col = 1
	foreach ($prop in $props) {
		$ws.Cells.Item($row, $col++).Value = $_.($prop.Name).toString()
	}
	$row++
}
end {
	[void] $ws.usedRange.EntireColumn.AutoFit()
	$wb.Save()
	if (!$sheetName) { $excel.Quit() }
}
}

Open in new window

And with that, the code is
$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)

Write-host "Time window between $($start.ToString("dd/MM/yyyy HH:MM")) until $($end.ToString("dd/MM/yyyy HH:MM")):"

. 'C:\temp\ee\Obj2Excel Pipe.ps1'

$rxDomains = '@' + ((Get-AcceptedDomain).Name -join '|@')

$trackparams = @{
    start      = $start
    end        = $end
    resultsize = 'Unlimited'
    EventId    = 'DELIVER'
}

foreach ($email in (Import-Csv "C:\Temp\EE\trackingemail.csv")) {

    Write-host "Email address: $($email.email)"

    Get-TransportService |
        Get-Messagetrackinglog @trackparams -Recipients $email.email |
        ? { $_.Sender -notmatch $rxDomains } |
        Group-Object -NoElement Sender |
        Sort-Object Count -Descending |
        Select Name, Count |        
        Export-XLS C:\temp\ee\Results.xlsx -Sheet $email.email
}
$excel.Quit()

Open in new window

I've changed the file  (and scipt) locations to C:\Temp\EE.

Author

Commented:
QLemo,

Thanks for joining in this thread, yes, you are almost correct, I have modified the script below to run against multiple Exchange 2013 Servers:

Initialization script where I run firstly in my PowerGUI console:
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://PRDEXC01-VM/PowerShell/ -Authentication Kerberos
Import-PSSession $Session -AllowClobber

Open in new window


$resultFile = "C:\Temp\EE\Results.xlsx"
$inputFile = 'C:\TEMP\EE\trackingemail.csv'
$trackparams = @{
    Start      = (Get-Date).AddDays(-1)
    end        = Get-Date
    ResultSize = 'Unlimited'
    EventId    = 'DELIVER'
}

$rxDomains = '@' + ((Get-AcceptedDomain).Name -join '|@')

Write-host "`n Time window between $($trackparams.start.ToString('dd/MM/yyyy HH:MM')) until $($trackparams.end.ToString('dd/MM/yyyy HH:MM'))" -ForegroundColor Blue
Import-Csv $inputFile |
    ForEach-Object{
        $email = $_.email
        Write-host "Email address: $email" -Fore Green	
	    Get-TransportService |
            ForEach-Object {Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email} |
                Where-Object { $_.Sender -notmatch $rxDomains } |
                Group-Object -NoElement Sender |
                Sort-Object Count -Descending |
                Select Name, Count |
                Export-XLS $resultFile -Sheet $email.email
    }
$excel.Quit()
#######################################################################################
## Export object from pipe to Excel sheet

<# Example use:
  (new-object PSObject -Property @{a = "A" ; b = 1.5 }),
  (new-object PSObject -Property @{a = "NA"; b = 2   }) |
    export-xls "C:\temp\ee\tst.xlsx"
#>

<# To Do:
  optional header format, like Interior.ColorIndex, Font.ColorIndex, ...
  optional table formatting (script block)
#>
<# Note: If $SheetName is provided, the workbook will be saved but not closed
   to speed up processing.
   Also make sure to use PowerShell 4 or later for better Automation performance
#>
function export-xls ([String] $xlsFile, [String] $sheetName) {
	begin {
	  # Excel initialization stuff
	  # use global Excel instance object, if already set
	  if (!$global:excel) {
	    $global:excel = New-Object -ComObject excel.application
	  }
	  $excel.visible=$true

	  # Open existing workbook or create new
	  if (Test-Path $xlsFile) {
	    $wb = $excel.Workbooks.Open($xlsFile)
	  } else {
	  	$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

	    #   Delete all worksheets but one
	    $excel.DisplayAlerts = $false
	    for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
	    $excel.DisplayAlerts = $true
	    $ws = $wb.Worksheets.Item(1)
	    if ($sheetName) { $ws.Name = $sheetName }
	    $wb.SaveAs($xlsFile)
	  }
	  # if sheet should be named in an existing workbook, search its name
	  if ($sheetName -and !$ws) { $ws = $wb.Sheets | ? { $_.Name -eq $sheetName } }
	  # append a new sheet if not found (and workbook is not new)
	  if (!$ws) {
	    $lastWS = $wb.WorkSheets.Item($wb.WorkSheets.Count)
	    $ws = $wb.WorkSheets.Add($lastWS)
	    $lastWS.Move($ws)
	  }

	  # name it if a name has been provided
	  if ($sheetName) { $ws.Name = $sheetName }
		$props = $null
		$row = 1
	}
	process {
		if (!$props) {
			$col = 1
			$props = $_.PsObject.Properties
			$props | % {
				$ws.Cells.Item($row, $col).Value = $_.Name
				$ws.Cells.Item($row, $col++).Font.Bold = $true
			}
			$row++
		}
		$col = 1
		foreach ($prop in $props) {
			$ws.Cells.Item($row, $col++).Value = $_.($prop.Name).toString()
		}
		$row++
	}
	end {
		[void] $ws.usedRange.EntireColumn.AutoFit()
		$wb.Save()
		if (!$sheetName) { $excel.Quit() }
	}
}

Open in new window


Hopefully, that's not the case where the result is behaving strangely:

Content of C:\Temp\EE\trackingemail.csv
Email
MyEmailAddress@domain.com
MyManager@domain.com

Open in new window


The result of the XLSX file:
Sheet 1 (empty)
Sheet 2 (empty)
Sheet 3- Shows the statistics of my external email that I have received correctly. Although the name of the sheets is not MyEmailAddress@domain.com
Sheet 4 (empty)
Sheet 5 (empty)
MyManager@domain.com (empty)

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
That is strange, as the script should not work at all with an imported Exchange session. I've tested it with the Exchange Management Shell, and it works fine. In an imported session you get issues because of nested Exchange cmdlets in a command pipeline.

In any case, the sheets are named correctly for me.

Are you getting any error messages?
Does it work if you remove line 17 above (Get-TransportService |, e.g. by commenting it out?

Author

Commented:
Qlemo,

Does it work if you remove line 17 above (Get-TransportService |, e.g. by commenting it out?
Yes, but the result is empty Result.XLSX file with 5 worksheets.

When I executed my code above, It is working somehow. Only the Worksheet needs to be named properly.

Content of C:\TEMP\EE\trackingemail.csv is 5 lines excluding the header:

Email
MyEmailAddress@domain.com
Manager1@domain.com
Manager2@domain.com
EmailDistroList1@domain.com
EmailDistroList2@domain.com

Open in new window


The distro list cannot be queried using the "Deliver" event log, hence no result.

The result is that C:\Temp\EE\Results.xlsx now populated properly, except the name of the worksheet is not renamed as per email address.

Content:
Sheet1 - This contains the email statistics for MyEmailAddress@domain.com
Sheet2 - This contains the email statistics for Manager1@domain.com
Sheet3 - This contains the email statistics for Manager2@domain.com
Sheet4 - Empty this is due to the Get-Recipient cmdlets returns MailUniversalDistributionGroup not User Mailbox
Sheet5 - Empty this is due to the Get-Recipient cmdlets returns MailUniversalDistributionGroup not User Mailbox
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I see now, you made some changes to the code I did not spot earlier.
To get the sheet names right, you need to provide $email instead of $email.email to export-xls.
For getting results for DGs I would have to do some research how to handle best.

Author

Commented:
QLEMO,

Thanks for the suggestion, yes, it is almost correct.

The first email address on the line does not labelled properly on the Excel it's become Sheet1 while the rest are correct.

EventId    = 'EXPAND' is the event for Distribution group.
as per: https://technet.microsoft.com/en-us/library/bb124375%28v=exchg.150%29.aspx

Author

Commented:
If the Get-Recipient returns UserMailbox   Then it is EventId = 'DELIVER'

If the Get-Recipient returns MailUniversalDistributionGroup   Then it is EventId = 'EXPAND'
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
In that case I would just change
            ForEach-Object {Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email} |

Open in new window

to
            ForEach-Object {
                  Get-Messagetrackinglog -Server $_.Name @trackparams -EventID Deliver -Recipients $email
                  Get-Messagetrackinglog -Server $_.Name @trackparams -EventID Expand -Recipients $email
            } |

Open in new window

and remove the eventid from trackparams.

Author

Commented:
Yes, that does make sense.
However, the first email address in the CSV somehow doesn’t gets labelled properly in the Sheet1 ?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Hmm, the first sheet is always named correctly for me, even if using your script (without the export-xls definition) ... Delete the Excel workbook, then try again; might be a remnant of earlier tries.

Also, in my Exchange I have much more success finding mails if I just run Get-MessageTrackinglog -Server $_.Name -recipient SomeEMailGroup@domain.com -EventID Receive. That works for groups and users.

Author

Commented:
Thanks guys.
QLemo solutions work very well.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial