Author Posts

February 20, 2018 at 9:04 am

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.

February 20, 2018 at 1:42 pm

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.

February 20, 2018 at 2:25 pm

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 😉

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

February 20, 2018 at 2:34 pm

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.

February 20, 2018 at 3:37 pm

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. 🙂

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?

February 20, 2018 at 4:13 pm

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.

February 20, 2018 at 5:01 pm

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

February 20, 2018 at 5:28 pm

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?

February 20, 2018 at 6:02 pm

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

February 20, 2018 at 6:44 pm

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

February 20, 2018 at 7:23 pm

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

February 21, 2018 at 12:46 pm

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.

February 21, 2018 at 1:07 pm

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}

February 21, 2018 at 2:18 pm

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.

February 21, 2018 at 2:25 pm

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

February 21, 2018 at 2:43 pm

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

February 21, 2018 at 3:33 pm

@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?

February 21, 2018 at 3:52 pm

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
}

February 21, 2018 at 4:50 pm

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

March 3, 2018 at 9:19 pm

@ 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