Author Posts

September 21, 2016 at 9:18 pm

I'd like to change this exchange script to export-csv with column headings, etc. Everything I try just creates a blank file.

Ideas?

# Initialize some variables used for counting and for output
$From = Get-Date "20/11/2011"
$To = $From.AddDays(1)

[Int64] $intSent = $intRec = 0
[Int64] $intSentSize = $intRecSize = 0
[String] $strEmails = $null

Write-Host "DayOfWeek,Date,Sent,Sent Size,Received,Received Size" -ForegroundColor Yellow

Do
{
# Start building the variable that will hold the information for the day
$strEmails = "$($From.DayOfWeek),$($From.ToShortDateString()),"

$intSent = $intRec = 0
(Get-TransportServer) | Get-MessageTrackingLog -ResultSize Unlimited -Start $From -End $To | ForEach {
# Sent E-mails
If ($_.EventId -eq "RECEIVE" -and $_.Source -eq "STOREDRIVER")
{
$intSent++
$intSentSize += $_.TotalBytes
}

# Received E-mails
If ($_.EventId -eq "DELIVER")
{
$intRec++
$intRecSize += $_.TotalBytes
}
}

$intSentSize = [Math]::Round($intSentSize/1MB, 0)
$intRecSize = [Math]::Round($intRecSize/1MB, 0)

# Add the numbers to the $strEmails variable and print the result for the day
$strEmails += "$intSent,$intSentSize,$intRec,$intRecSize"
$strEmails

# Increment the From and To by one day
$From = $From.AddDays(1)
$To = $From.AddDays(1)
}
While ($To -lt (Get-Date))
#While ($To -lt (Get-Date "01/12/2011"))

September 21, 2016 at 9:23 pm

Oh, goodness. Yes. Hang on a sec. Be right back.

September 21, 2016 at 9:27 pm

So, here's what you're doing wrong: You're not letting PowerShell do it for you. You're also using Write-Host. Stop that. You can't redirect Write-Host, ever.

Suppose I have my data in a bunch of variables – $one, $two, $three.

function Get-MyData {
  $one = "Something"
  $two = "Something Else"
  $three = "Something third"
  $columns = @{ 'ColumnA' = $one
                'ColumnB' = $two
                'Gollum' = $three }
  New-Object -Type PSObject -Prop $columns
}

Get-MyData | Export-CSV filename.csv

I'd strongly suggest going through "Learn Windows PowerShell in a Month of Lunches" and "Learn PowerShell Toolmaking in a Month of Lunches;" you're missing out on some fantastic functionality to make PowerShell do the heavy work for you. In this very brief example, I've made a function which outputs an object. It could output more than one, too! All of that output goes to Export-Csv, and I will get a 3-column CSV file as a result.

What you're doing – manually constructing and outputting text – is a very VBScript-ish approach, and it means a lot more work for you. Those books will also help you understand why Write-Host isn't doing what you think it is – you're just not up to speed on some of PowerShell's under-the-hood truths, and once you get those, a lot of stuff will suddenly become a lot clearer.

Good luck!