Hoping you guys can help. I have been given a list of over 2000 users as a CSV file with 2 columns, "employeeNumber" and the corresponding "homePhone". Both only have numerical values. The management want me to update the users homePhone attribute within AD from the list provided. The catch is not to update the field if it already has a value. So, in essence I only want to update the AD accounts that have a blank attribute. I am hoping to use the import function within Powershell. Thank you.
Hello, yes this is possible. What you want to do is use an LDAP query to look for a user account matching the employeenumber that has an empty homephone attribute. This can be done using the following LDAP query:
(&(employeenumber=$_.employeenumber)(!(homephone=*))
Then if a match is found you can update the homephone property based on the csv file. The full code looks like this:
Do note that there will be no prompts and no confirmations, so be careful before running this.
Hi Jaap
Thank you for the quick reply.
I have entered the query in powershell as it is except for the path to my csv file. I get the following error:
Exception calling "FindOne" with "0" argument(s): "The(&([email protected]{employeeNumber=12345678;homePhone=38888}.employeenumber)(!(homephone=*)) search filter is invalid."At line:2 char:6+ if (($User =[adsi]([adsisearcher]"(&(employeenumber=$_.employeenumber)(!(homeph ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException+ FullyQualifiedErrorId : ArgumentException
I am not sure what I am doing wrong. I am using powershell version 3, if that makes a difference.
Right, I forgot to wrap that into a $() block. Here is the updated code:
Thank you for the reply.
I am now getting a slightly different error. Please see below:
Exception calling "FindOne" with "0" argument(s): "The (&(employeenumber=23897689)(!(homephone=*)) search filter is invalid."At line:2 char:60+ if (($User = [adsi]([adsisearcher]"(&(employeenumber=$($_.employeenumber))(! ...+ ~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ArgumentException
Yes, I think we can officially rename today to Typo Friday . I also forgot to put in one of the brackets in the code I posted. So here is the correct search filter:
This is great. I have tested it and it works perfect. You are a star.
One thing i forgot to ask was can I add some code to that script so that it outputs a new CSV file which would have a list of all the users who have had their homePhone number added. what would that code look like. I ask this because I can see my manager requesting this.
Depends what kind of attributes you want to output. But if you want the distinguishedname of each user, which can be used to retrieve all other attributes you could add this line:
$user.properties.distinguishedname
Then you have the following code which you can redirect to a text file:
I have used the following 3 lines after $User.setinfo():
$User.properties.sn $User.properties.givenName $User.properties.employeeNumber $User.properties.homePhone
This information should be enough, but I had to place this after $User.setinfo() because I was not getting the homePhone which I am now.
However I want this information in the form of a CSV file to be saved locally. Can this be done and if so what code would I need adding. Thank you.
Here you go, if you output it as a Custom object it can be stored as a csv. Have a look at this:
Sorry.. I am fairly new to Powershell.
When I enter the code above I still get the results displayed within the powershell console. I know I have to use the export-csv command to export the results in a csv file rather then being displayed within the console. But I am unsure I how go about doing this. Thanks
Hey, there are multiple points at which you could add Export-Csv, I decided to place it at the end of the Foreach-Object loop. I added the -NoTypeInformation parameter. Here is the code for that:
Everything seems to be working fine in terms of the attribute being updated if its blank and the code also generates a csv file which show the users who have been updated. The only problem I have is that I also get the following error in the console:
Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null. At line:12 char:5 + } | Export-Csv -Path C:\Users\akhtao1\Desktop\test101.csv -NoTypeInformation + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null. At line:12 char:5 + } | Export-Csv -Path C:\Users\akhtao1\Desktop\test101.csv -NoTypeInformation + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand I dont understand what this code is complaining about as I am getting the results that I want. Please help, thank you.
At line:12 char:5
+ } | Export-Csv -Path C:\Users\akhtao1\Desktop\test101.csv -NoTypeInformation
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand
Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
I dont understand what this code is complaining about as I am getting the results that I want. Please help, thank you.
Ah sorry, assuming you are using PowerShell version 3 you could do this:
You are the man!
It all works perfect - yes I am using powershell 3. I wont need to go anywhere now for any other powershell query
thanks for all your help.