Solved

Hello, i have an issue with a script to import and update a list of current user details in AD

Posted on 2014-09-03
10
394 Views
Last Modified: 2014-09-05
Hello,
I have a csv file that has been exported from our AD, I have amended and added details and can re-import a sample few back into a test environment, however I have come across an issue where any blank cells in the csv file cause the script to fall over.

is there a way I can get my script to skip or ignore any blank cells it comes across, change the value to empty if needed and continue?

here is what I have so far which works fine if all csv cells have information in them.



# Import AD Module            
Import-Module ActiveDirectory            
           
# Import CSV into variable $userscsv            
         
$users = Import-Csv -Path c:\users\aflook\desktop\NewUsers1.csv            
# Loop through CSV and update users if the exist in CVS file            
           
foreach ($user in $users)
{            
#Search in specified OU and Update existing attributes            
 
 Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase “ou=Adam Test,DC=therange,DC=local" |            
  Set-ADUser -Description $($user.Description) -Office $($user.Office) -GivenName $($user.GivenName) -SurName $($user.SurName) -DisplayName $($user.DisplayName) -PostalCode $($user.postalCode) -mobile $($user.mobile) -Company $($user.company) -StreetAddress $($user.StreetAddress) -City $($user.City) -OfficePhone $($user.OfficePhone) -Title $($user.Title) -Department $($user.Department) -Fax $($user.Fax) -State $($user.State) -HomePage $($user.HomePage) }
0
Comment
Question by:BYRONJACKSON
  • 5
  • 4
10 Comments
 
LVL 28

Expert Comment

by:becraig
ID: 40302419
Hi I think we can look at the recently answered question:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_28505620.html

Simply make the change to do the following:

It is cumbersome but does what you need.

import-csv file.csv | % {
if ($_.value1 -eq $null -or $_value1 -eq "") {$_.value1 = "EMPTY"}
if ($_.value2 -eq $null -or $_value1 -eq "") {$_.value2 = "EMPTY"}
if ($_.value3 -eq $null -or $_value1 -eq "") {$_.value3 = "EMPTY"}
}

Open in new window

0
 
LVL 83

Expert Comment

by:oBdA
ID: 40302812
You can do that dynamically by querying the properties of the imported csv files, generating a hashtable from it, and then use splatting (see http://technet.microsoft.com/en-us/library/jj672955.aspx):
# Import AD Module
Import-Module ActiveDirectory

# Import CSV into variable $userscsv

$users = Import-Csv -Path "C:\users\aflook\desktop\NewUsers1.csv"
# Loop through CSV and update users if the exist in CVS file

foreach ($user in $users)
{
	#Search in specified OU and Update existing attributes            
	$Arguments = @{}
	$user | Get-Member -MemberType NoteProperty | % {if ($user.($_.Name)) {$Arguments[$_.Name] = $user.($_.Name)}}
	Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "ou=Adam Test,DC=therange,DC=local" | Set-ADUser @Arguments
}

Open in new window

0
 

Author Comment

by:BYRONJACKSON
ID: 40302879
Hi thanks for the replies i have tested the splatting method and this is the error i now receive.

any help would be greatly appreciated.

Set-ADUser : Cannot convert 'System.String' to the type 'System.Management.Automation.SwitchParameter' required by parameter 'Confirm'.
At line:14 char:143
+ ... " | Set-ADUser @Arguments
+                    ~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Set-ADUser], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.ActiveDirectory.Management.Commands.SetADUser
0
 
LVL 83

Expert Comment

by:oBdA
ID: 40302887
Does the csv file have more fields than the ones you've used in the Set-ADUser? If so, please post a sanitized version of the csv file (one content line should be enough).
0
 

Author Comment

by:BYRONJACKSON
ID: 40302889
I have also looked at this one,
import-csv file.csv | % {
if ($_.value1 -eq $null -or $_value1 -eq "") {$_.value1 = "EMPTY"}
if ($_.value2 -eq $null -or $_value1 -eq "") {$_.value2 = "EMPTY"}
if ($_.value3 -eq $null -or $_value1 -eq "") {$_.value3 = "EMPTY"}
}

do i need to pre define the values or can i simply input them where it states "value1" etc, and will this on its own carry out the task or do i need to an in other sections from my current code?

many thanks
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:BYRONJACKSON
ID: 40302894
NewUsers1.csvNewUsers1.csvNewUsers1.csvAttached is my test user i have been playing with, the main csv has about 500+ entries hope this helps
0
 
LVL 83

Expert Comment

by:oBdA
ID: 40302935
The issue is the "co" column. Since there is no AD property "co", Set-ADUser tries to match that to a known argument and finds "-Confirm".
If you want to set the "Country" property, simply rename the "co" column header to "Country".
If not, this could be filtered out as well; would you then rather use a blacklist (to exclude a given list columns) or a whitelist (to include only a given list of columns)?
Note that in its current form, the script will fail or create incorrect results if any property in the file contains a comma (like "Main Street 123, Apartment 456"). The fields should be enclosed in double quotes to prevent that.
0
 

Author Comment

by:BYRONJACKSON
ID: 40302956
Ah ok brilliant, so for the Blank fields where i need data to be either removed from AD if its not present or left blank do i need to put "" into the csv's blank cell?
i would rather use a white list to include the given columns, will this cause an issue with multiple entries though as not all columns are completely empty. basically it needs to check for an empty cell in the column and either ignore or correct the entry in AD and then carry on if that makes sense?
0
 
LVL 83

Accepted Solution

by:
oBdA earned 500 total points
ID: 40303152
Currently, it simply ignores properties that don't have a value.
This one can exclude columns, and it will clear the properties that don't have a value:
# Import AD Module
Import-Module ActiveDirectory

# Import CSV into variable $userscsv

$users = Import-Csv -Path "C:\users\aflook\desktop\NewUsers1.csv"
# Loop through CSV and update users if the exist in CSV file
$ExcludeProperties = @(
	"co"
)

foreach ($user in $users)
{
	$Arguments = @{}
	$user | Get-Member -MemberType NoteProperty | ? {$ExcludeProperties -NotContains $_.Name} | % {if ($user.($_.Name)) {$Arguments[$_.Name] = $user.($_.Name)} Else {$Arguments[$_.Name] = $Null}}
	#Search in specified OU and Update existing attributes            
	Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "ou=Adam Test,DC=therange,DC=local" | Set-ADUser @Arguments
}

Open in new window

And even without quotes, blank cells are not an issue in the csv; only fields that contain a comma in their text will be.
0
 

Author Closing Comment

by:BYRONJACKSON
ID: 40305494
Brilliant all working!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

757 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

19 Experts available now in Live!

Get 1:1 Help Now