Report formating problems

Welcome Forums General PowerShell Q&A Report formating problems

This topic contains 2 replies, has 3 voices, and was last updated by

2 years, 10 months ago.

  • Author
  • #55895

    Topics: 1
    Replies: 0
    Points: 0
    Rank: Member

    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

  • #55903

    Topics: 1
    Replies: 1526
    Points: 2,571
    Helping Hand
    Rank: Community Hero

    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 ... 😉

  • #55907

    Topics: 1
    Replies: 302
    Points: 144
    Helping Hand
    Rank: Participant

    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




    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 = ',,'
    $proxyAddress = -join ("=",($proxyAddress -replace 'smtp:','"' -replace ', ','"&CHAR(10)&'),"`"")
    $obj = [PSCustomObject] @{
        homeMDB = $homeMDB
        addresses = $proxyAddress
    $obj | Export-Excel E:\__Temp\output.xlsx

The topic ‘Report formating problems’ is closed to new replies.