Need help modifying PowerShell script - to identify columns of SIP & SMTP etc...

Need help modifying PowerShell script - to identify columns of SIP & SMTP etc...
Also, if possible, would like x400 & x500 to be part of the output:

Thank you for your time in advance!
K.B.

Long version:
Get-Mailbox -OrganizationalUnit "ou=blahblah,ou=blah,ou=xyz,dc=abc,dc=com" -resultsize unlimited |Select-Object DisplayName, UserPrincipalName, ForwardingAddress, Alias, LegacyExchangeDN, RecipientType, RecipientTypeDetails, PrimarySmtpAddress,@{Name="FirstName";Expression={(Get-User $_).FirstName}}, @{Name="LastName";Expression={(Get-User $_).LastName}}, @{Name="StreetAddress";Expression={(Get-User $_).StreetAddress}}, @{Name="City";Expression={(Get-User $_).City}}, @{Name="StateOrProvince";Expression={(Get-User $_).StateOrProvince}}, @{Name="PostalCode";Expression={(Get-User $_).PostalCode}}, @{Name="CountryOrRegion";Expression={(Get-User $_).CountryOrRegion}}, @{Name="Phone";Expression={(Get-User $_).Phone}}, @{Name="HomePhone";Expression={(Get-User $_).HomePhone}}, @{Name="MobilePhone";Expression={(Get-User $_).MobilePhone}}, @{Name="Fax";Expression={(Get-User $_).Fax}}, @{Name="Pager";Expression={(Get-User $_).Pager}}, @{Name="Department";Expression={(Get-User $_).Department}}, @{Name="Office";Expression={(Get-User $_).Office}}, @{Name="Title";Expression={(Get-User $_).Title}}, @{Name="WebPage";Expression={(Get-User $_).WebPage}}, @{Name="Notes";Expression={(Get-User $_).Notes}}, @{Name="EmailAddresses";Expression={($_.EmailAddresses | Where-Object {$_.PrefixString -ceq "smtp" -or "sip"} | ForEach-Object {$_.SmtpAddress}) -join ","}} | ConvertTo-CSV -notype | ForEach-Object { $_ -replace '"',""} | Out-File C:\scripts\SMTP_SIP.csv -encoding ascii

Open in new window



Short Version for easier reading:
Get-Mailbox -OrganizationalUnit "ou=blahblah,ou=blah,ou=xyz,dc=abc,dc=com" -resultsize unlimited |Select-Object DisplayName, UserPrincipalName, ForwardingAddress, Alias, LegacyExchangeDN, RecipientType, RecipientTypeDetails, PrimarySmtpAddress,@{Name="FirstName";Expression={(Get-User $_).FirstName}}, @{Name="Notes";Expression={(Get-User $_).Notes}}, @{Name="EmailAddresses";Expression={($_.EmailAddresses | Where-Object {$_.PrefixString -ceq "smtp" -or "sip"} | ForEach-Object {$_.SmtpAddress}) -join ","}} | ConvertTo-CSV -notype | ForEach-Object { $_ -replace '"',""} | Out-File C:\scripts\SMTP_SIP.csv -encoding ascii

Open in new window

LVL 9
K BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
First of all: Please use syntax formatting:
Get-Mailbox -OrganizationalUnit "ou=blahblah,ou=blah,ou=xyz,dc=abc,dc=com" -resultsize unlimited |
  Select-Object DisplayName, UserPrincipalName, ForwardingAddress, Alias, LegacyExchangeDN, RecipientType,RecipientTypeDetails,PrimarySmtpAddress,
                @{Name="FirstName"      ;Expression={(Get-User $_).FirstName}},
                @{Name="LastName"       ;Expression={(Get-User $_).LastName }},
                @{Name="StreetAddress"  ;Expression={(Get-User $_).StreetAddress}},
                @{Name="City"           ;Expression={(Get-User $_).City}},
                @{Name="StateOrProvince";Expression={(Get-User $_).StateOrProvince}},
                @{Name="PostalCode"     ;Expression={(Get-User $_).PostalCode}},
                @{Name="CountryOrRegion";Expression={(Get-User $_).CountryOrRegion}},
                @{Name="Phone"          ;Expression={(Get-User $_).Phone}},
                @{Name="HomePhone"      ;Expression={(Get-User $_).HomePhone}},
                @{Name="MobilePhone"    ;Expression={(Get-User $_).MobilePhone}},
                @{Name="Fax"            ;Expression={(Get-User $_).Fax}},
                @{Name="Pager"          ;Expression={(Get-User $_).Pager}},
                @{Name="Department"     ;Expression={(Get-User $_).Department}},
                @{Name="Office"         ;Expression={(Get-User $_).Office}},
                @{Name="Title"          ;Expression={(Get-User $_).Title}},
                @{Name="WebPage"        ;Expression={(Get-User $_).WebPage}},
                @{Name="Notes"          ;Expression={(Get-User $_).Notes}},
                @{Name="EmailAddresses" ;Expression={($_.EmailAddresses | Where-Object {$_.PrefixString -ceq "smtp" -or "sip"} | ForEach-Object {$_.SmtpAddress}) -join ","}} |
  ConvertTo-CSV -notype |
  ForEach-Object { $_ -replace '"'} |
  Out-File C:\scripts\SMTP_SIP.csv -encoding ascii

Open in new window

0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Would you mind showing us an example output - now and as desired?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Why did you accept http:#a40698477 ? We were just starting, the script above is just different formatting of the code, and does not change anything. Next improvement is the elimination of the repetive get-user call:
Get-Mailbox -OrganizationalUnit "ou=blahblah,ou=blah,ou=xyz,dc=abc,dc=com" -resultsize unlimited |
  % {
    $usr = Get-User $_
    $_ | Select-Object DisplayName, UserPrincipalName, ForwardingAddress, Alias, LegacyExchangeDN, RecipientType,RecipientTypeDetails,PrimarySmtpAddress,
                @{Name="FirstName"      ;Expression={$usr.FirstName}},
                @{Name="LastName"       ;Expression={$usr.LastName }},
                @{Name="StreetAddress"  ;Expression={$usr.StreetAddress}},
                @{Name="City"           ;Expression={$usr.City}},
                @{Name="StateOrProvince";Expression={$usr.StateOrProvince}},
                @{Name="PostalCode"     ;Expression={$usr.PostalCode}},
                @{Name="CountryOrRegion";Expression={$usr.CountryOrRegion}},
                @{Name="Phone"          ;Expression={$usr.Phone}},
                @{Name="HomePhone"      ;Expression={$usr.HomePhone}},
                @{Name="MobilePhone"    ;Expression={$usr.MobilePhone}},
                @{Name="Fax"            ;Expression={$usr.Fax}},
                @{Name="Pager"          ;Expression={$usr.Pager}},
                @{Name="Department"     ;Expression={$usr.Department}},
                @{Name="Office"         ;Expression={$usr.Office}},
                @{Name="Title"          ;Expression={$usr.Title}},
                @{Name="WebPage"        ;Expression={$usr.WebPage}},
                @{Name="Notes"          ;Expression={$usr.Notes}},
                @{Name="EmailAddresses" ;Expression={($_.EmailAddresses | ? {$_.PrefixString -ceq "smtp" -or "sip"} | % {$_.SmtpAddress}) -join ","}} |
  ConvertTo-CSV -notype |
  % { $_ -replace '"'} |
  Out-File C:\scripts\SMTP_SIP.csv -encoding ascii

Open in new window

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

K BAuthor Commented:
Okay sounds good .. bare with me while I work through a separate Microsoft case and I will revisit and provide example output.
0
K BAuthor Commented:
Thank for your patience!

I had a chance to test this.  One thing that happened is that I needed to add the closing bracket } but I could not exactly figure out where to place it.
2015-07-10-1831.pngHowever...
No matter where I added the closing bracket } I received this error for each user/mbx:
2015-07-10-1833.png
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The missing closing bracket belongs into line 22, in front of the pipe symbol (there should be 3 consecutive cloing brackets).

You are executing the script remotely, as it seems. That applies some restrictions, and we need to be more explicit if reusing Exchange/AD objects. This should work (adapted to your test code):
Get-Mailbox -Resultsize 3 | 
  % {
    $usr = Get-User $_.SamAccountName
    $_ | Select-Object DisplayName, UserPrincipalName, ForwardingAddress, Alias, LegacyExchangeDN, RecipientType,RecipientTypeDetails,PrimarySmtpAddress,
                @{Name="FirstName"      ;Expression={$usr.FirstName}},
                @{Name="LastName"       ;Expression={$usr.LastName }},
                @{Name="StreetAddress"  ;Expression={$usr.StreetAddress}},
                @{Name="City"           ;Expression={$usr.City}},
                @{Name="StateOrProvince";Expression={$usr.StateOrProvince}},
                @{Name="PostalCode"     ;Expression={$usr.PostalCode}},
                @{Name="CountryOrRegion";Expression={$usr.CountryOrRegion}},
                @{Name="Phone"          ;Expression={$usr.Phone}},
                @{Name="HomePhone"      ;Expression={$usr.HomePhone}},
                @{Name="MobilePhone"    ;Expression={$usr.MobilePhone}},
                @{Name="Fax"            ;Expression={$usr.Fax}},
                @{Name="Pager"          ;Expression={$usr.Pager}},
                @{Name="Department"     ;Expression={$usr.Department}},
                @{Name="Office"         ;Expression={$usr.Office}},
                @{Name="Title"          ;Expression={$usr.Title}},
                @{Name="WebPage"        ;Expression={$usr.WebPage}},
                @{Name="Notes"          ;Expression={$usr.Notes}},
                @{Name="EmailAddresses" ;Expression={($_.EmailAddresses | ? {$_.PrefixString -ceq "smtp" -or "sip"} | % {$_.SmtpAddress}) -join ","}}
  } |
  ConvertTo-CSV -notype |
  % { $_ -replace '"'} |
  Out-File C:\scripts\SMTP_SIP.csv -encoding ascii

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K BAuthor Commented:
Thank you Qlemo for your help!  

This did not seem to output any email addresses for "EmailAddresses" field
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Works for me (MSX 2013). What is the result of
Get-Mailbox -ResultSize 3 | Select EMailAddresses

Open in new window

Does it show arrays of addresses containing SIP and SMTP as prefix (like SIP:qlemo@e-e.com)?

The only issue I can see is that the EMailAddresses column consists of comma-separated values, which would conflict with CSV if you read the file elsewhere. If that is an issue, we can either use a different separator for the addresses or for the fields.
0
K BAuthor Commented:
Thank you again...
Yes, Get-Mailbox -ResultSize 3 | Select EMailAddresses gives data/is correct..
However, in the full script, the raw text file (CSV) shows data in all fields except EmailAddresses.

Yes, as you say, once populated, it would be awesome if the results in the EmailAddresses field have a unique character so I can use excel to break them up into different cells.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Change the character at the very end of line 22 to e.g. a semi-colon, so the addresses are not considered to be individual columns by default CSV imports.

If the test line provides correct info (i.e. an array of values prefixed with SMTP or SIP), then my code works. You can try to remove the condition in line 22, or store the raw data in EMailAddresses, but that should not change much. As said, it works for me as-is, and I cannot see any potential flaw.
0
K BAuthor Commented:
The original question was asked about on-premise exchange.. I tested it on-premise (Exchange 2013) and it works perfectly! I will open a new question with the same code asking for tweaks to make it work for Office 365 if that sounds good.

Thank you so much for your persistence and patience!
K.B.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That's a good move, as I don't have experience with O365 and the requirement to use Remoting coming with it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.