Need some help in formatting the Column export result to remove the SMTP: & smtp: ?

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

Need some help in formatting the Column export result to remove the SMTP: & smtp: ?

Post by ITEngineer » Sun Sep 02, 2018 9:15 pm

Hi People,

I need some help with the Replace string operation to make sure the result does not contain the SMTP: and smtp: string?

The below script export the list of the Disabled AD account with their corresponding Exchange mailbox details:

Code: Select all

$filter = '(Enabled -eq $false) -and (homeMDB -ne "$null")'
$properties = @('homeMDB', 'mailNickName', 'mail', 'DisplayName', 'SamAccountName', 'ProxyAddresses')

$Allusers = (Get-ADUser -Filter $filter -Properties $properties  |
             ForEach-Object {

                $MBSize = (Get-MailboxStatistics $_.SamAccountName).TotalItemSize.Value.ToMB()
                $MBLastLogonTime = (Get-MailboxStatistics $_.SamAccountName).LastLogonTime
                $MBLastLoggedOnUserAccount = ((Get-MailboxStatistics $_.SamAccountName).LastLoggedOnUserAccount)
                $MBDisconnectDate = ((Get-MailboxStatistics $_.SamAccountName).DisconnectDate)
                $MBSMTPAddresses = ($_.ProxyAddresses | Where-Object {$_ -like "*smtp:*" }).replace("smtp:","") -join ';'

                New-Object -TypeName PSObject -Property @{
                    DisplayName    = $_.DisplayName
                    mailNickName   = $_.mailNickName
                    SamAccountName = $_.SamAccountName
                    mail           = $_.mail
                    ProxyAddresses = $MBSMTPAddresses
                    homeMDB        = (($_.homeMDB).split(',')[0]).split('=')[1]
                    MBytes         = $MBSize
                    LastLogonTime  = $MBLastLogonTime
                    LastLoggedOnUserAccount = $MBLastLoggedOnUserAccount
                    DisconnectDate = $MBDisconnectDate
                }
            }) | Sort-Object MBytes -Descending | Export-Csv "C:\Result.csv" -NoTypeInformation
However, the problem with the Export.CSV ProxyAddresses column output is the content of the column is like:
SMTP:First.Last@domain.com;First.Last@domain2.com;Alias1@domain.com

So how to get rid of that SMTP: string?

Thank you in advance.
/* IT Engineer */

User avatar
jvierra
Posts: 13725
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Need some help in formatting the Column export result to remove the SMTP: & smtp: ?

Post by jvierra » Sun Sep 02, 2018 9:42 pm

It seems I have shown you how to do this about a half dozen times. You need to take time to analyze your code to understand what it is actually doing.

Code: Select all

$filter = '-not Enabled -and homeMDB'
$properties = @('homeMDB', 'mailNickName', 'mail', 'DisplayName', 'SamAccountName', 'ProxyAddresses')

Get-ADUser -Filter $filter -Properties $properties  |
    ForEach-Object {
        $stat = Get-MailboxStatistics $_.SamAccountName
        $smtpAddresses = $_.ProxyAddresses | 
            Where-Object {$_ -match '^smtp:' }
            ForEach-Object{$_ -replace 'smtp:'}

        New-Object -TypeName PSObject -Property @{
            DisplayName    = $_.DisplayName
            mailNickName   = $_.mailNickName
            SamAccountName = $_.SamAccountName
            mail           = $_.mail
            ProxyAddresses = $smtpAddresses -join ';'
            homeMDB        = (($_.homeMDB).split(',')[0]).split('=')[1]
            MBytes         = $stat.TotalItemSize.Value.ToMB()
            LastLogonTime  = $stat.LastLogonTime
            LastLoggedOnUserAccount = $stat.SamAccountName
            DisconnectDate = $stat.DisconnectDate
        }
    } | 
    Sort-Object MBytes -Descending | 
    Export-Csv C:\Result.csv -NoTypeInformation

User avatar
ITEngineer
Posts: 145
Joined: Wed Oct 12, 2011 10:52 am

Re: Need some help in formatting the Column export result to remove the SMTP: & smtp: ?

Post by ITEngineer » Sun Sep 02, 2018 11:19 pm

jvierra wrote:
Sun Sep 02, 2018 9:42 pm
It seems I have shown you how to do this about a half dozen times. You need to take time to analyze your code to understand what it is actually doing.

Code: Select all

$filter = '-not Enabled -and homeMDB'
$properties = @('homeMDB', 'mailNickName', 'mail', 'DisplayName', 'SamAccountName', 'ProxyAddresses')

Get-ADUser -Filter $filter -Properties $properties  |
    ForEach-Object {
        $stat = Get-MailboxStatistics $_.SamAccountName
        $smtpAddresses = $_.ProxyAddresses | 
            Where-Object {$_ -match '^smtp:' }
            ForEach-Object{$_ -replace 'smtp:'}

        New-Object -TypeName PSObject -Property @{
            DisplayName    = $_.DisplayName
            mailNickName   = $_.mailNickName
            SamAccountName = $_.SamAccountName
            mail           = $_.mail
            ProxyAddresses = $smtpAddresses -join ';'
            homeMDB        = (($_.homeMDB).split(',')[0]).split('=')[1]
            MBytes         = $stat.TotalItemSize.Value.ToMB()
            LastLogonTime  = $stat.LastLogonTime
            LastLoggedOnUserAccount = $stat.SamAccountName
            DisconnectDate = $stat.DisconnectDate
        }
    } | 
    Sort-Object MBytes -Descending | 
    Export-Csv C:\Result.csv -NoTypeInformation
Mr. Vierra.

Many thanks for the great assistance in this matter.

My skill is quite limited in terms of string parsing, hence I posted the script here.

it is working flawlessly. :)
/* IT Engineer */

User avatar
jvierra
Posts: 13725
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Need some help in formatting the Column export result to remove the SMTP: & smtp: ?

Post by jvierra » Sun Sep 02, 2018 11:31 pm

??? There is no siring parsing. It is just a filter and a replacement. You could call it partsing but it really isn't.

Look at you original code and try to seem what it was that you did wrong. It was a very obvious mistake, You mixed foreach and where. The rest I did to remind you that making the same call a half dozen times is unnecessary.

Locked