Report formating problems

PowerShell Help
1

Hello Everyone, I’m really new to powershell, but I’m trying really hard.

I’m trying to get a list of all my users, there Excahnge DB, and all there emails (including aliasses)
I’ve manage to get most of what I want with

get-aduser -filter * -properties * | 
select-object CN, Displayname, CanonicalName, SamAccountName, homeMDB, mail, enabled, @{"name"="proxyaddresses":"expression"={$_.proxyaddresses}} | 
Export-Csv -Encoding Unicode -NoTypeInformation -Delimiter ";" -Path c:\temp\test.csv

Here what is not displaying how I want.

  • homeMDB: I would like the cut the text after the first “,”

  • proxyaddresses: They are all displaying on the same line and they are all prefixed with smtp:
    I would like to have them in the same cell in excel, but on different line (like alt+enter when you do it manually) and remove the SMTP: prefix

  • I would like also to querry get-mailboxstatistic,to have some thie like

Get-Mailbox -ResultSize Unlimited |

  Get-MailboxStatistics |

  Select DisplayName, `

  @{name=”TotalItemSize (MB)”; expression={[math]::Round( `

  ($_.TotalItemSize.ToString().Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1MB),2)}}, `

  ItemCount, LastLogoffTime,LastLogonTime |

  Sort “TotalItemSize (MB)” -Descending |

 Export-CSV “C:\temp\All Mailboxes $(get-date -f yyyy-MM-dd).csv” -NoTypeInformation -encoding Unicode -Delimiter ";"

but I really don’t know of to add it in the same report.

Thanks for your kind help and support.

Jean-François Gauthier

2

For the first part - you can change your Select-Object to this: 

 | select-object CN, Displayname, CanonicalName, SamAccountName, @{Name=‘homeMDB’;Expression={($.homeMDB -split ‘,’)[0]}}, mail, enabled, @{Name=‘proxyaddresses’;Expression={(($.Proxyaddresses) | % {($_ -split ‘:’)[1]}) -join ‘,’}} |

But in csv you cannot do things like in Excel. You don’t have cells. The maximum you can do is join the elements you need/want by a character of your choice. In the xample I did with the ‘,’.

For the second part I have think more … :wink:

3

As Olaf says, you can’t have line breaks in a CSV file. However, you can in an Excel file.

If this is a requirement, have a look at the Excel module: Introducing the PowerShell Excel Module and consider exporting your data straight to an XLSX document.

You can turn your output into an Excel formula with a bit of joining and replacing. In my example

'smtp:john@contoso.com, smtp:john.smith@contoso.com, smtp:j.smith@contoso.com'

becomes

="john@contoso.com"&CHAR(10)&"john.smith@contoso.com"&CHAR(10)&"j.smith@contoso.com"

If you turn on ‘Wrap text’ for the cell, Excel will display the addresses on separate lines in the same cell.

My example code also shows you how to split the string for the HomeMDB attribute and this is exported as well. By using a custom object, you can build up the data you want to output from multiple sources. You can use this method to add your mailbox statistics data.

$homeMDB = 'CN=Mailbox Store (CONTOSO-MSG-01),CN=First Storage Group,CN=InformationStore,CN=CONTOSO-MSG-01,CN=Servers,CN=First Administrative Group,CN=Administrative Groups,CN=First Organization,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=contoso,DC=com'

$homeMDB = ($homeMDB -split ',')[0]

$proxyAddress = 'smtp:john@contoso.com, smtp:john.smith@contoso.com, smtp:j.smith@contoso.com'

$proxyAddress = -join ("=",($proxyAddress -replace 'smtp:','"' -replace ', ','"&CHAR(10)&'),"`"")

$obj = [PSCustomObject] @{

    homeMDB = $homeMDB
    addresses = $proxyAddress

}

$obj | Export-Excel E:\__Temp\output.xlsx