Powershell - modify powershell script getting data from CSV and writing other attributes


could anyone assist to modify this powershell script

1. getting data from CSV " ; " first column userID
This script should getting user.id from CSV " ; "  like

2. output should be only the primarysmtp and owamailboxpolicy = webmail to 2nd column

Get-CASMailbox -id userid | fl Owamai*

3. Output should have 3 Columns Userid;primarysmtp;owamailmailboxpolicy
If SMTP and OWApolicy does not exists only insert the UserID with blank behind
as follows

UserID      primarysmtp                    OWA...
E12345     mike.jones@test.com      Webmail
E34567     mike.test@test.com
E12345     mike.jones2@test.com     Webmail
E23459                                                Webmail

$AllRecipients = Get-Recipient -ResultSize unlimited #csv instead
#Initializing variables
$Threshold = 0
$AttributeList = @()
$UsersAndSMTPProxies = @()
$UpperLimit = 0
#Examine each recipient
$AllRecipients | % {
    #Get a list of SMTP and smtp proxy addresses for each User
    $SmtpProxyAddresses = $_.emailaddresses | ? {$_.prefixstring -like 'smtp'} | sort IsPrimaryAddress -Descending
    #Create a new placeholder object with only their name
    $UserAndSmtpObject = New-Object PSObject -Property @{
        Name = $_.name
    #Initialize the proxy counter
    $counter = 0
    #Begin breaking out proxy addresses
    $SmtpProxyAddresses | % {
        $SMTPaddress = $_ -replace “smtp:”
        $counter += 1
        if ($counter -eq 1) {$UserAndSmtpObject | Add-Member -MemberType NoteProperty -Name PrimarySmtpAddress -Value $SMTPaddress}
        else {$UserAndSmtpObject | Add-Member -MemberType NoteProperty -Name (“SmtpAddress” + $Counter) -Value $SMTPaddress}
        #Keep track of the highest proxy count
        if ($Counter -gt $UpperLimit) {$UpperLimit = ($UpperLimit + 1)}

Open in new window

Who is Participating?
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.

Just to be sure (as I think we can rewrite the script for simplicity)

1. You want to import users from a csv file
2. You want to querythe AD for Primarysmtp and OWA values
3. You want to populate a new csv with these values
4. If a user has a missing value either Owa or smtp you want to leave that specific column blank in the final csv.

Let me know if the above assumptions are correct and I can give you a solution.
Mandy_Author Commented:
These are the 4 points of reality. Your help would be much appreciated. Mandy
I think this should work for what you need ( I am assuming there is an input csv with userid)

import-csv file.csv | % {
$user = $_.userid                                   
Get-Mailbox -identity $user | 
$psmtp = $_.PrimarySmtpAddress
$owaval = (Get-CASMailbox -id $user | select -ExpandProperty Owamai*)
if ($psmtp -eq $null -or $psmtp -eq "") {$psmtp = ""}
if ($owaval -eq $null -or $owaval -eq "") {$owaval = ""}
$item = New-Object PSObject
$item | Add-Member -type NoteProperty -Name 'USERID' -Value $user
$item | Add-Member -type NoteProperty -Name 'PRIMARYSMTP' -Value $psmtp
$item | Add-Member -type NoteProperty -Name 'OWAPOLICY' -Value $owaval
$csvarray += $item
$csvarray | export-csv emlreport.csv -nti

Open in new window

There is some useless code in there but I think this should do what you need, if it does not let me know and I will test it.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Mandy_Author Commented:
First many thanks. I tested the script extensively. There are still bugs.

1. Alway only one user exported although three valid users are present in the CSV (CSV is ok)
 Is it possible that UserIds contained in the CSV and not be found in the ad are also listed in the outputCSV?

i modified the script to getting the correct output of owapolicy  pls see

import-csv c:\export.csv -Delimiter ";" | % { 

$user = $_.userid 
Get-Mailbox -id $user | 
$psmtp = $_.PrimarySmtpAddress
$owaval = (Get-CASMailbox $user | select-object -ExpandProperty owamailboxpolicy).tostring()
if ($psmtp -eq $null -or $psmtp -eq "") {$psmtp = ""}
if ($owaval -eq $null) {$owaval = ""}

# if user does not exist in AD should be also listed in output csv if ($user -eq $null ) {$user = ""}

$item = New-Object PSObject;
$item | Add-Member -type NoteProperty -Name 'USERID' -Value $user;
$item | Add-Member -type NoteProperty -Name 'PRIMARYSMTP' -Value $psmtp;
$item | Add-Member -type NoteProperty -Name 'OWAPOLICY' -Value $owaval;
$csvarray = $item
$csvarray | export-csv c:\emlreport.csv -nti

Open in new window

Ok so at this point the final edit you need it to leave the user as blank if the get-mailbox for that user returns nothing ?

If so, I can Just dump the full mailbox DB and owapolicy for all users, then do a compare against the csv contents then call for the owapolicy which is cheaper in terms of overhead.

Let me know if what I outlined above is correct and I will go ahead and put it together but I don't have access to my servers this weekend so I won't be able to test.
Mandy_Author Commented:
I try to explain
1. my CSV has semicolon separated values. The first column called UserID and has up to 300 values.
These are samAccountNames  e.g.  X12345, X23456, X34567
The CSV looks like this:
X12345;Rotten, Johny;ASU;NYC;o12345;j.rotten@asu.com;11.09.14;;
X12347;Vicious, Sid;ASU;NYC;o12345;j.rotten@asu.com;11.09.14;;

Still a problem is:
My test-csv contains 5 user. Three of them matches the criteria but only one of them always filtered
to the output-csv, regardless of where they are placed in the CSV.

2.The script should scan all UserIDs if the user has already a mailbox and maybe not a full mbx only OWA (owapolicy)
and also if an ad-account exists or not (This info you can't get with get-mailbox)

3.The output should add to an excel worksheet which has already the data coming from the same CSV (up to 300 values in UserID in Excel column B2-B300).
If i insert the output into the following columns of the excel sheet (primarySmtp and owa...)
i'm able to compare how these orders has to process in the following steps, you know?

If the output has only the users which have values and all other not included , it would be difficult to sort
the CSV into the worksheet. I need to find out and this would be difficult with a adquery from excel,
which Class of user it is.

A) user has no AD-Account - Before i can attach the mbx the ad-account must be created
B) User has AD-Account and already a Mailbox - no further doing necessary
c) User has  OWA only - upgrade to full mailbox
d) user has full mbx - downgrade to OWA only

If i run a script for up to 200-300 users, the powerhell codes created in excel line for line and if the output
information not given, i'm getting a lot of errors making a lot of extra work. (paste into EMS)

If i include this info into a cell i can change the powershell code depending of the value of the cell.

I've found these "get-aduser code" below. Maybe it could be integrate for the example if AD-account exist or not
and if mail exist or not.

The best course would be to write the result directly to the column of the worksheet without the detour via the csv ( C2-C300 and D2-D300 worksheet "import"

$getuser = Get-ADUser -filter "Name -eq  '$($_.name)'" -Properties mail | Select Name, SAMAccountName, mail
if ($getuser) {$getuser}
else {[PSCustomObject]@{Name=$_;SAMAccountName='Not found';mail=$null}}

Unfortunately complex powershell coding is missing in my skill  :-(
I hope i'm not confusing to much.
Best Regards
Ok so I think something like this is what you are after:

1. I import the AD module to test for AD users
2. Dump all AD users into an array
3. Dump all mailboxes into an array
4. Dump all OWA policies into an array

5. We loop through the csv and set the user value
6. we set the value of the different variables to an empty value
7. we run an if condition on the output from the arrays created above and if they samaccount name matches we populate the value (you can use any other value you want for this)
8. We write the objects back into an array
9. We export the output to csv.

Import-module ac* 
$Adusers = (Get-Aduser -filter * -ResultSetSize unlimited )
$mailboxes = Get-Mailbox
$owapol = (Get-CasMailbox)
import-csv c:\export.csv -Delimiter ";" | % 
$sam = $_.userid
$Adval = ""
$mbxval = ""
$owaval = ""
$Adusers | % {if ($_.Samaccountname -eq $sam) {$Adval = "True"}}
$mailboxes | % {if ($_.Samaccountname -eq $sam) {$mbxval = $_.PrimarySmtpAddress}}
$owapol | % {if ($_.Samaccountname -eq $sam) {$Adval = (($_.owamailboxpolicy).tostring())}}
$item = New-Object PSObject;
$item | Add-Member -type NoteProperty -Name 'USERINAD' -Value $adval;
$item | Add-Member -type NoteProperty -Name 'USERID' -Value $sam;
$item | Add-Member -type NoteProperty -Name 'PRIMARYSMTP' -Value $mbxval;
$item | Add-Member -type NoteProperty -Name 'OWAPOLICY' -Value $owaval;
$csvarray = $item
$csvarray | export-csv output.csv -nti

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
How about this much shorter code?
import-csv c:\export_neu.csv -Delimiter ";" -header UserID| % {
  $_ | Add-Member NoteProperty PrimarySMTP (get-mailbox $_.UserID -ea SilentlyContinue | Select -Expand PrimarySMTPAddress) -PassThru |
       Add-Member NoteProperty OWAPolicy ("WebMail" * (get-CASMailbox $_.UserID -ea SilentlyContinue | select -Expand OwaEnabled)) -passThru
} | export-csv c:\emlreport.csv -nti

Open in new window

It keeps all UserIDs, and just appends columns.

Edit: This was prior to the last comments - that makes it a bit more complex ...

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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Script also checking AD:
import-csv c:\export_neu.csv -Delimiter ";" -header UserID| % {
  $_ | Add-Member NoteProperty PrimarySMTP (get-mailbox $_.UserID -ea SilentlyContinue | Select -Expand PrimarySMTPAddress) -PassThru |
       Add-Member NoteProperty OWAPolicy ("WebMail" * (get-CASMailbox $_.UserID -ea SilentlyContinue | select -Expand OwaEnabled)) -PassThru |
       Add-Member NoteProperty ADAccount (Get-ADUser -Identity $_.UserID -ea SilentlyContinue | Select Name) -PassThru
} | export-csv c:\emlreport.csv -nti

Open in new window

I don't know what to code for Excel, but I've written a generic object-to-Excel function for direct import of data. It should work if we use a template containing the required formulas, but IMHO we should do all stuff in PowerShell instead, or at least only import the final result into Excel (including the generated PS code lines).

Honestly, I'm still not clear why you have PS code generated in Excel, instead of using the Excel sheet as a PS script source doing the necessary processing on demand.
Mandy_Author Commented:
Dear qlemo,

your contribution is awesome as usual. The first code serves my needs. One thing is only that the header also keeps
in the output. The 2nd code only keeping the id's which has values. If not found not keeping but i can live with the
1st code. Thank you so much. The suggestion to handle the whole thing with PowerShell I've already tried.
The thing is very complex.

BTW: to import directly into excel columns  you could check my other question
Write to excel worksheet

Have a nice sunday
Mandy_Author Commented:
Many thanks to all involved
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

From novice to tech pro — start learning today.