Report formating problems

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Matt Bloomfield Matt Bloomfield 1 month, 1 week ago.

  • Author
    Posts
  • #55895

    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
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    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
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    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

    '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

You must be logged in to reply to this topic.