AD Attribute changes from CSV file

PowerShell Help
1

I know this about powershell and CSV files.

When you import a CSV file you use Import-csv c:\scripts\file.csv
When you need to work with the columns you use Select-Object to work with the columns headlines.
Select-Object Headline1,Headline2.

And the syntax would be like: Import-csv c:\scripts\file.csv | Select-Object Name,Company
When you create user some attributes are empty, and I need to fill those out automatically since it is being done manually for the time being.

In my scenario I have a csv file containing 4 rows.
The first being “Department” This is my unique identifier, that is selected from a dropdownlist, during usercreation.
This is fine.
But the other 3 rows needs to be updatet depending on whats in Column1.

If department is = to HR in column A row 9, then it must look update departmentnumber which is in column B and so on I also have an extra attribute that needs to be updated in Column C, called ExtraAttribute2 in AD.

2

well you have parts of this correct. there is no reason to use the select-object once you’ve imported the csv.
here is a REALLY simple example, assuming you have a column in your csv titled “networkid” which contains the samaccountname of the user to update, and a column titled “company” which has the value you want to set in the company attribute of AD.

$csv = import-csv c:\scripts\file.csv
foreach ($user in $csv)
{
    try
    {
        set-aduser $user.networkid -company $user.company    
    }
    catch
    {
        write-error "ERROR SETTING $($user.networkid)"    
    }
    
}

we import the csv, then loop through each entry using set-aduser to apply the value.

3

Hi David
Thanks for helping.

The main issue is, that the Username is not present in my csv file.
Company attribute is already set on the user, but it depends on what value the Department attribute has, in order to update from Column B,C and D.

I properly need help along the way to make it perfect.
But lets assume that I have a Username SamSmith - not in the CSV file.
Then I need to update Departmentnumber based on Department which is pre-filled in AD.
Department is also Column A in my CSV file.

Would it then look like I would like to test it on 1 user at a time just so I don’t make too many erros :wink:

set-aduser -Identity username $User.Company -Companynumber $user.companynumber

4

if you don’t have the network id in your file, this is going to be very difficult, you would need to try to search active directory with filters.

you have to provide one of the allowed items for identity. as well, companynumber is not a valid ad attribute.

so you should break it down into multiple parts, find an ad filter that works to return the appropriate user account, you can then put logic in for your use cases to update companyname.

until you can identify a single specific user to associate with each entry in your file you won’t be able to proceed.

5

I have thought of the filter process and how to do it.

My thought is this:
Find AD user created today:

$date = (Get-Date ).AddDays(-1)
Get-ADUser -Filter {whencreated -gt $date} -Properties * | Select-Object Name,WhenCreated

Yes you you told already I dont’t need select-object. But I like to see the result on-screen before proceeding. So please bare with me. :slight_smile:

  1. You take the users created today and put them in a variable.
  2. Import-csv file.
  3. Then you somehow go through the users created today and compare column A TO Department attribute in AD for the users created today
  4. Based on the information in Column A, add departmentnumber from Column B and Extra-attribute from Column C

I hope you can see where I am going with this?

6

it is going to be step 3 that will be painful for you.
unless you have a specific attribute that is 100% unique to each user, you will not really be able to accomplish your task.

7

Hi

If you do have something in your CSV file that could identify the correct user then you could use Get-ADUser -Filter {} and find the user and then set the values, but without it I believe it’s no can do task.

Regards

Jake

8

So unless I have a username in a column INSIDE the csv file my task can not be accomplished or at least very difficult?

Even if I as in above example get users created today, and from there get the username?

Because then I do have the unique id.
And powershell does treat the content of a csv file as objects? Or did I misunderstand?

9

Hi

No you don’t need to have username on the csv file, but you’ll need something that you can use to find the user. Powershell is using the info/rows from csv as an object.

Regards

Jake

10

So basically I need a unique identifier as in 100% unique IN the csv file in order to continue?

11

in general yes, you really do need a 100% unique identifier.

you could write some queries to combine first/last names and other things, but thats not the best idea as names tend to not be unique.

even something as simple as an email address. but without knowing your user configs or whats actually present in your csv file, its impossible to provide more information

12

A little update:
I have just found out that the user will be created before this script is running
So I do know the username
For now I am testing with a user called TestUser1.
The csv file contains the following data:
Department,Company,Manager
IT,Blue-42,Awesome
HR,RED-48,Phantom

and based on username which I have
I would like to update company and Manager depending on which department that has already been filled out.
So AD knows by now, username and Department, but not Company and manager.

Could you use import-path -ashashtable ?

And then do an IF Department -eq IT, Company should be Blue-42 and Manager should be Awesome.
I hope there is an easier method.

13

You will want to organize your csv file like this,

username, Department,Company,Manager
testuser1,it, blue-42, awesome
testuser2, HR,RED-48,Phantom

then

import-csv C:\user.csv | % {set-aduser -identity $_.username -department $_.department -company $_.company -manager $_.manager}
14

Am I back to step 1, if I say that the username will not be a part of the imported file.
But resides outside the imported file?
Meaning I won’t have have a column called Username.

15

You don’t want it outside the imported file. Why can’t you put it in the file?

16

If the department only has one manager and the department is unique and everyone in that department reports to this manager you could do something like this. The last bit just outputs to the console what you have just changed so you can sanity check it. You could always set the country as well.

Get-Aduser -filter “Department -eq ‘My Department’” | Set-ADuser -Manager DaBoss -passthru | Get-Aduser -Properties Title,Manager | Select Name,Title,Manager

17

@Jon
I cant because the csv file is predefined. Where Department is the only Unique parametre to look up.

And my script must be based on Department somehow to fill out the rest.

@Simon B
I like the way you think I will need to check the Manager attribute.
I always lije to sanity check my changes.
But I still need the file to go from.
Could I go the Long way around and type in all departments and there managers?
In some hash table?

18

Does this get you any closer ??

$Departments = import-csv C:\HR\HR.csv
foreach ($Department in $Departments){
Get-Aduser -filter “Department -eq $($Department.Department)” | Set-ADuser -Manager $($Department.Manager) -passthru | Get-Aduser -Properties Title,Manager | Select Name,Title,Manager
}

19

I will test it later this evening and update you on the progress.

20

@ Simon B
When runnning you suggested command I got the following error message:

Get-Aduser : Error parsing query: 'Department -eq ’ Error Message: ‘syntax error’ at position: ‘12’.
At line:3 char:1

  • Get-Aduser -filter “Department -eq $($Department.Department)” | Set-A …
  •   + CategoryInfo          : ParserError: (:) [Get-ADUser], ADFilterParsingException
  + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

Get-Aduser : Error parsing query: 'Department -eq ’ Error Message: ‘syntax error’ at position: ‘12’.
At line:3 char:1

  • Get-Aduser -filter “Department -eq $($Department.Department)” | Set-A …
  •   + CategoryInfo          : ParserError: (:) [Get-ADUser], ADFilterParsingException
  + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser