Page 1 of 1

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

Posted: Sun Sep 02, 2018 9:15 pm
by ITEngineer
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.

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

Posted: Sun Sep 02, 2018 9:42 pm
by jvierra
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

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

Posted: Sun Sep 02, 2018 11:19 pm
by ITEngineer
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. :)

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

Posted: Sun Sep 02, 2018 11:31 pm
by jvierra
??? 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.