Powershell for Exchange 2003

rkeith2412
rkeith2412 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

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

Author

Commented:
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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

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:
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

"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Sorry for that, didn't pay attention to your complete formatting hash table. You will need to remove the width setting for each property, as that does not make sense in a select-object:
#HTML Email Styles
$style  = @"
<style type='text/css'>
  BODY{background-color:#FFFFFF;font-family:Verdana;}
  TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}
  TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#D3D3D3;text-align:center }
  TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#FFFFFF;text-align:center }
</style>
"@

$file = "D:\users_over_750MB.html"

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

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

Open in new window

I have done some other slight changes just for fun.

Author

Commented:
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

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

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

Author

Commented:
Perfect. Thanks.

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