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 8
K BAsked:
Who is Participating?
 
QlemoBatchelor, 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
 
QlemoBatchelor, 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Would you mind showing us an example output - now and as desired?
0
How do you know if your security is working?

Protecting your business doesn’t have to mean sifting through endless alerts and notifications. With WatchGuard Total Security Suite, you can feel confident that your business is secure, meaning you can get back to the things that have been sitting on your to-do list.

 
QlemoBatchelor, 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
 
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
 
K BAuthor Commented:
Thank you Qlemo for your help!  

This did not seem to output any email addresses for "EmailAddresses" field
0
 
QlemoBatchelor, 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
 
QlemoBatchelor, 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
 
QlemoBatchelor, 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.