Solved

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

Posted on 2014-09-26
11
330 Views
Last Modified: 2014-09-28
hi

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
UserID
e12345
e12346
e23456
e34565


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
E23456      
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

0
Comment
Question by:Mandy_
  • 5
  • 4
  • 2
11 Comments
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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.
0
 
LVL 2

Author Comment

by:Mandy_
Comment Utility
These are the 4 points of reality. Your help would be much appreciated. Mandy
0
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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.
0
 
LVL 2

Author Comment

by:Mandy_
Comment Utility
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

0
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:Mandy_
Comment Utility
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:
UserID;Diplayname;Comp;Loc;oID;smtp;date;name
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
Mandy
0
 
LVL 28

Assisted Solution

by:becraig
becraig earned 150 total points
Comment Utility
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

0
 
LVL 68

Accepted Solution

by:
Qlemo earned 350 total points
Comment Utility
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 ...
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 350 total points
Comment Utility
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.
0
 
LVL 2

Author Comment

by:Mandy_
Comment Utility
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
0
 
LVL 2

Author Closing Comment

by:Mandy_
Comment Utility
Many thanks to all involved
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Hi all.   The other day I had to change the passwords for a bunch of users on the fly. Because they were so many, I decided to do it in an automated way and I would like to share it with you all.   If you are not doing it directly in a Domain Co…
Set OWA language and time zone in Exchange for individuals, all users or per database.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now