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

Ask your 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.
This topic is 5 years and 6 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

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

Post 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.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post 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
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

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

Post 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. :)
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post 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.
This topic is 5 years and 6 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked