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

Posted on 2014-09-26
Medium Priority
Last Modified: 2014-09-28

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

Question by:Mandy_
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
LVL 29

Expert Comment

ID: 40346839
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.

Author Comment

ID: 40347191
These are the 4 points of reality. Your help would be much appreciated. Mandy
LVL 29

Expert Comment

ID: 40347210
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 40347304
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

LVL 29

Expert Comment

ID: 40347665
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.

Author Comment

ID: 40347726
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
LVL 29

Assisted Solution

becraig earned 600 total points
ID: 40347844
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

LVL 70

Accepted Solution

Qlemo earned 1400 total points
ID: 40348022
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 ...
LVL 70

Assisted Solution

Qlemo earned 1400 total points
ID: 40348031
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.

Author Comment

ID: 40348296
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

Author Closing Comment

ID: 40348298
Many thanks to all involved

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question