Link to home
Start Free TrialLog in
Avatar of rkeith2412
rkeith2412Flag for United States of America

asked on

Powershell for Exchange 2003

I have the follwoing script that works great until I try to convert it to HTML and save it to a file so I can email it.

#HTML Email Styles
$style = "<style type='text/css'>"
$style = $style + "BODY{background-color:#FFFFFF;font-family:Verdana;}"
$style = $style + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}"
$style = $style + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#D3D3D3;text-align:center }"
$style = $style + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#FFFFFF;text-align:center }"
$style = $style + "</style>"

$file = "D:\users_over_750MB.html"

$table = @{Expression={$_.MailboxDisplayName};Label="Name";width=50}, `
@{Expression={[math]::truncate($_.Size / 1KB)};Label="Size";width=25}, `
@{Expression={$_.TotalItems};Label="Total Items";width=25}

$mailboxdata = (Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer concourse -Filter "size>=768000" | sort-object -desc Size | format-table $table)
$mailboxdata | ConvertTo-HTML -head $style | Out-File "$file"

Open in new window


This script used to use Select-Object instead of Format-Table and the HTML conversion worked just fine.

#HTML Email Styles
$style = "<style type='text/css'>"
$style = $style + "BODY{background-color:#FFFFFF;font-family:Verdana;}"
$style = $style + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}"
$style = $style + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#D3D3D3;text-align:center }"
$style = $style + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#FFFFFF;text-align:center }"
$style = $style + "</style>"



$file = "D:\users_over_750MB.html"
$mailboxdata = (Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer concourse -Filter "size>=1048576" | sort-object -desc Size | select-object MailboxDisplayName,Size,TotalItems)
$mailboxdata | ConvertTo-HTML -head $style | Out-File "$file"

Open in new window


I really want this in an easy to read HTML table instead of the long string of text I get without it.
users-over-750MB.html
Avatar of Qlemo
Qlemo
Flag of Germany image

Is the attached result from format-table or select-object? And why would you prefer one over the other?
Avatar of rkeith2412

ASKER

from the format table, the output from the select object has a couple of issues.

1. It doens't do the conversion to MB (maybe I don't know how)
2. the text is in one really long string when it gets attached to the email, it doens't have the same formatting as it does in powershell.
I think I got what you are after. Took me some time ...

Your issue is that
you get proper formatting with format-table, but the HTML table has only one very wide column
the select-object has multiple columns, but doesn't show values as expected.

The "secret" is to apply the same formatting as with format-table to select-object (only showing line 15 of your first code snippet):
$mailboxdata = (Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer concourse -Filter "size>=1048576" | sort-object -desc Size | select-object $table)

Open in new window

That should give you something you can stuff into convertto-html.
Here is the error i get when I try that.

Select-Object : Illegal key width
At D:\size.ps1:15 char:174
+ $mailboxdata = (Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer concourse -Filter "size>=768000" | sort-object -desc Size | select-object <<<<  $table)
    + CategoryInfo          : InvalidArgument: (:) [Select-Object], NotSupportedException
    + FullyQualifiedErrorId : DictionaryKeyIllegal,Microsoft.PowerShell.Commands.SelectObjectCommand

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Still giving the same issue, here is the current code.

#HTML Email Styles
$style = "<style type='text/css'>"
$style = $style + "BODY{background-color:#FFFFFF;font-family:Verdana;}"
$style = $style + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}"
$style = $style + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#D3D3D3;text-align:center }"
$style = $style + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#FFFFFF;text-align:center }"
$style = $style + "</style>"

$file = "D:\users_over_750MB.html"

$a = @{Expression={$_.MailboxDisplayName};Label="Name"}, `
@{Expression={[math]::truncate($_.Size / 1KB)};Label="Size (MB)"}, `
@{Expression={$_.TotalItems};Label="Total Items"}

Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer concourse -Filter "size>=768000" | sort-object -desc Size | format-table $a | ConvertTo-HTML -head $style

Open in new window

That doesn't look like my snippet (http:#a38014404) at all. Just use that one, no changes to apply!
Perfect. Thanks.