This blog has moved to http://ThePowerShellGuy.com
Greetings /\/\o\/\/
Ok finaly in this 5th part of the series about ActiveDirectory we leave the commandline and are going to make a start to put the bits learned in former post together to make a script to create users from a CSV file.
But first a last bit about listing and exporting the properties of an DirectoryEntry Object in this post as we are going to do an export of the userproperties of a completely configured user to CSV using export-csv,to create a template for our import function to be.
But because the properties are wrapped, as we have seen in former posts, this is not as easy as exporting most other objects in PowerShell, as I will show you in the following example.
For this example I did create and configure a new user (ken Meyer) in the Users & computers MMC and will use that user in the example to create a template for the CSV file to use for creating Users.
# get the testuser
PoSH>$user = New-Object DirectoryServices.DirectoryEntry('LDAP://mowdc001/cn=ken Myer,ou=MowOu,dc=mow,dc=local')
PoSH>$user
distinguishedName
-----------------
{CN=Test,OU=MowOu,DC=mow,DC=local}
# list all properties
PoSH>$user | fl *
# Select the properties we want to export
PoSH>$user | select cn,displayname,description,physicalDeliveryOfficeName,telephoneNumber,givenName,initials,sn,company,
homeDrive,homeDirectory,profilePath,scriptPath,sAMAccountName,mail
cn : {Ken Myer}
displayName : {Ken Myer}
description : {Template User}
physicalDeliveryOfficeName : {room 01-05}
telephoneNumber : {12345}
givenName : {Ken}
initials : {}
sn : {Myer}
company : {Mow}
homeDrive : {H:}
homeDirectory : {\\mowdc001\profile$\KMeyer}
profilePath : {\\mowdc001\profile$\KMeyer}
scriptPath : {Logon.cmd}
sAMAccountName : {KMeyer}
mail : {Kmeyer@mowMail.com}
# export this to a CSV file :
PoSH>$user | select cn,displayname,description,physicalDeliveryOfficeName,telephoneNumber,givenName,initials,sn,company,
homeDrive,homeDirectory,profilePath,scriptPath,sAMAccountName,mail | Export-Csv -NoTypeInformation c:\powershell\users.c
sv
# Import it again :
PoSH>import-csv C:\PowerShell\users.csv
cn : System.DirectoryServices.PropertyValueCollection
displayName : System.DirectoryServices.PropertyValueCollection
description : System.DirectoryServices.PropertyValueCollection
physicalDeliveryOfficeName : System.DirectoryServices.PropertyValueCollection
telephoneNumber : System.DirectoryServices.PropertyValueCollection
givenName : System.DirectoryServices.PropertyValueCollection
initials : System.DirectoryServices.PropertyValueCollection
sn : System.DirectoryServices.PropertyValueCollection
company : System.DirectoryServices.PropertyValueCollection
homeDrive : System.DirectoryServices.PropertyValueCollection
homeDirectory : System.DirectoryServices.PropertyValueCollection
profilePath : System.DirectoryServices.PropertyValueCollection
scriptPath : System.DirectoryServices.PropertyValueCollection
sAMAccountName : System.DirectoryServices.PropertyValueCollection
mail : System.DirectoryServices.PropertyValueCollection
As you can see this is not wat we expected / wanted, a csv file with the values,
the properties are returned as PropertyValueCollection's, as we have also seen in former posts (a lot of the objects a wrapped the formatters will hide this by enumerating them, but when we export it we run into this as the object will be passed on the pipeline), for this example making a template CSV this would not matter, but as we will run into this (getting back collections) more, while working with objects from the DirectoryServices namespace, as we will run into this also also when we start searching the active directory later in this series I will get into this and how to solve it a bit more in this post.
A way to workaround this is put the properties in scriptblocks (a very powerfull feature of Select-Object), then they get parsed first.
this looks like this :
(I added linebreaks after each property in the select statement to make this more clear but you can past this on the PowerShell console as one line )# Export it again using scriptblocks in the select statement
PoSH>$user | select {$_.cn},
>> {$_.description},
>> {$_.physicalDeliveryOfficeName},
>> {$_.telephoneNumber},
>> {$_.givenName},
>> {$_.initials},
>> {$_.sn},
>> {$_.company},
>> {$_.homeDirectory},
>> {$_.homeDrive},
>> {$_.scriptPath},
>> {$_.sAMAccountName},
>> {$_.mail} | Export-Csv -NoTypeInformation c:\powershell\users.csv
>>
# and import
PoSH>import-csv C:\PowerShell\users.csv
$_.cn : Ken Myer
$_.description : Template User
$_.physicalDeliveryOfficeName : room 01-05
$_.telephoneNumber : 12345
$_.givenName : Ken
$_.initials :
$_.sn : Myer
$_.company : Mow
$_.homeDirectory : \\mowdc001\profile$\KMeyer
$_.homeDrive : H:
$_.scriptPath : Logon.cmd
$_.sAMAccountName : KMeyer
$_.mail : Kmeyer@mowMail.com
you can see that this solves the PropertyValueCollection problem but is more work and also the columnnames contain the expression in the scriptblock also not very friendly, there is good and bad news, the bad news is that it's more work and typing and the syntax looks strange, the a the good news is we can rename the columns, its very flexible and I hope to show that there is more logic to the sysntax as seems at first.
it works like this, in addition to using a scriptblock for a property in select-object you can also use a hashTable that contains a name key with the wanted name as value and an expression key with the scriptblock to execute as a property.
You also can find more info blog and PowerShell Userguide (selecting properties)
but as the syntax used might still be a bit abstract I will show another example that might make more clear what happens here, as you can see from the next example on the commandline this is not select-object syntax, we are creating a new hashtable and we pass that in :
# commandline example of the HashTable to pass to the select-object Cmdlet
PoSH>@{ name='Description'; Expression={$_.description} }
Name Value
---- -----
name Description
Expression $_.description
PoSH>@{ name='Description'; Expression={$_.description} }
Name Value
---- -----
name Description
Expression $_.description
PoSH>@{ name='Description'; Expression={$_.description} } | gm
TypeName: System.Collections.Hashtable
Name MemberType Definition
---- ---------- ----------
Add Method System.Void Add(Object key, Object value)
Clear Method System.Void Clear()
Clone Method System.Object Clone()
Contains Method System.Boolean Contains(Object key)
ContainsKey Method System.Boolean ContainsKey(Object key)
ContainsValue Method System.Boolean ContainsValue(Object value)
......
So what happens is that we are just creating a hashtable on the fly for each property, to pass that to select-object and select-object just looks for a name and expression key for information to build the property.
I hope that showing the HashTable on the commandline, made the syntax a bit more logical by seperating it from the select-object statement, and does make the next command more clear, and the syntax eisier to remember, also as some propertynames in Active directory are not realy descriptive (SN) and different from the names in the MMC, I translated them on the way making use of the situation and on top of that,I will show that by making it a filter we can reuse it and use for more purposes.
I made an export like this :
# Using select with hastables to rename the properties
$user | select @{name='Name';Expression={$_.cn} },
@{ name='Description'; Expression={$_.description} },
@{ name='Room'; Expression={$_.physicalDeliveryOfficeName} },
@{ name='Telephone'; Expression={$_.telephoneNumber} },
@{ name='FirstName'; Expression={$_.givenName} },
@{ name='Initials'; Expression={$_.initials} },
@{ name='LastName'; Expression={$_.sn} },
@{ name='Company'; Expression={$_.company} },
@{ name='HomeDir'; Expression={$_.homeDirectory} },
@{ name='HomeDrive'; Expression={$_.homeDrive} },
@{ name='LogonScript'; Expression={$_.scriptPath} },
@{ name='Accountname'; Expression={$_.sAMAccountName} },
@{ name='Mail'; Expression={$_.mail} } | Export-Csv -NoTypeInformation c:\powershell\users.csv
# And import again
PoSH>import-csv C:\PowerShell\users.csv
Name : Ken Myer
Description : Template User
Room : room 01-05
Telephone : 12345
FirstName : Ken
Initials :
LastName : Myer
Company : Mow
HomeDir : \\mowdc001\profile$\KMeyer
HomeDrive : H:
LogonScript : Logon.cmd
Accountname : KMeyer
Mail : Kmeyer@mowMail.com
You can see that as most of the code is boilerplate that it's not as bad as it looks after we get that funky syntax of select-object property-hashtables, and that it is actualy handy as the AD properties are not the same as we see in the MMC interface.
and as you can see below we can make this a filter to make it reusable
# You can make a filter like this :
filter Format-ADUser {
$_ | select @{name='Name';Expression={$_.cn} },
@{ name='displayName'; Expression={$_.displayName} },
@{ name='Description'; Expression={$_.description} },
@{ name='Room'; Expression={$_.physicalDeliveryOfficeName} },
@{ name='Telephone'; Expression={$_.telephoneNumber} },
@{ name='FirstName'; Expression={$_.givenName} },
@{ name='Initials'; Expression={$_.initials} },
@{ name='LastName'; Expression={$_.sn} },
@{ name='Department'; Expression={$_.Department} },
@{ name='Company'; Expression={$_.company} },
@{ name='HomeDir'; Expression={$_.homeDirectory} },
@{ name='HomeDrive'; Expression={$_.homeDrive} },
@{ name='LogonScript'; Expression={$_.scriptPath} },
@{ name='Accountname'; Expression={$_.sAMAccountName} },
@{ name='Mail'; Expression={$_.mail} }
}
# And after that reuse it :
PoSH>$user | Format-ADUser | Export-Csv -not C:\PowerShell\users.csv
PoSH>import-csv C:\PowerShell\users.csv
Name : Ken Myer
displayName : Ken Myer
Description : Template User
Room : room 01-05
Telephone : 12345
FirstName : Ken
Initials :
LastName : Myer
Department : Dep001
Company : Mow
HomeDir : \\mowdc001\profile$\KMeyer
HomeDrive : H:
LogonScript : Logon.cmd
Accountname : KMeyer
Mail : Kmeyer@mowMail.com
Now we can also start to play with the data this in available and change it from powershell to make it ready to import again :
some examples :
# select some existing users
PoSH>($ouUsers |? {$_.Department -match 'Engineering'}).count
6
PoSH>($ouUsers |? {$_.Department -match 'Engineering'}) | ft name,displayname,department
Name displayName Department
---- ----------- ----------
AWu0003 Roberto Tamburello Engineering
AWu0009 Gail A Erickson Engineering
AWu0011 Jossef H Goldberg Engineering
AWu0012 Terri Lee Duffy Engineering
AWu0267 Michael I Sullivan Engineering
AWu0270 Sharon B Salavaria Engineering
# format them using the created filter :
PoSH>($ou.get_Children() |? {$_.Department -eq 'Engineering'}) | format-adUser | ft
Name displayName Description Room Telephone FirstName Initials LastName Department Company
---- ----------- ----------- ---- --------- --------- -------- -------- ---------- -------
AWu0003 Roberto ... {} {} {} Roberto {} Tamburello Engineering {}
AWu0009 Gail A E... {} {} {} Gail {} Erickson Engineering {}
AWu0011 Jossef H... {} {} {} Jossef {} Goldberg Engineering {}
AWu0012 Terri Le... {} {} {} Terri {} Duffy Engineering {}
AWu0267 Michael ... {} {} {} Michael {} Sullivan Engineering {}
AWu0270 Sharon B... {} {} {} Sharon {} Salavaria Engineering {}
# export them to CSV file :
PoSH>($ou.get_Children() |? {$_.Department -eq 'Engineering'}) | format-adUser | export-csv -not C:\PowerShell\NewUsers.csv
# the CSV file in text format looks like this :
PoSH>gc C:\PowerShell\NewUsers.csv
Name,displayName,Description,Room,Telephone,FirstName,Initials,LastName,Department,Company,HomeDir,HomeDrive,LogonScrip
t,Accountname,Mail
AWu0003,"Roberto Tamburello",,,,Roberto,,Tamburello,Engineering,,,,,$222000-BOCQ2JHU74K1,
AWu0009,"Gail A Erickson",,,,Gail,,Erickson,Engineering,,,,,$822000-KK9G22LQPGJV,
AWu0011,"Jossef H Goldberg",,,,Jossef,,Goldberg,Engineering,,,,,$A22000-RFN1CV2D2U5N,
AWu0012,"Terri Lee Duffy",,,,Terri,,Duffy,Engineering,,,,,$B22000-L10CLJU1NCSO,
AWu0267,"Michael I Sullivan",,,,Michael,,Sullivan,Engineering,,,,,$AA2000-R7OSI41L9LHR,
AWu0270,"Sharon B Salavaria",,,,Sharon,,Salavaria,Engineering,,,,,$DA2000-7UPIT07U0FPV,
# And now we can use it as an object
PoSH>$newUsers = import-csv C:\PowerShell\NewUsers.csv
PoSH>$newusers[0]
Name : AWu0003
displayName : Roberto Tamburello
Description :
Room :
Telephone :
FirstName : Roberto
Initials :
LastName : Tamburello
Department : Engineering
Company :
HomeDir :
HomeDrive :
LogonScript :
Accountname : $222000-BOCQ2JHU74K1
Mail :
# Also now we can do formatting on the job
# Change the description to "[dep] - [displayname]"
PoSH>$newusers |% {$_.description = "{0} - {1}" -f $_.Department,$_.displayname}
PoSH>$newusers | ft name,description
Name Description
---- -----------
AWu0003 Engineering - Roberto Tamburello
AWu0009 Engineering - Gail A Erickson
AWu0011 Engineering - Jossef H Goldberg
AWu0012 Engineering - Terri Lee Duffy
AWu0267 Engineering - Michael I Sullivan
AWu0270 Engineering - Sharon B Salavaria
# note that FT standard does not show the empty properties
PoSH>$newusers | ft
Name displayName Description Room Telephone FirstName Initials LastName Department Company
---- ----------- ----------- ---- --------- --------- -------- -------- ---------- -------
AWu0003 Roberto ... Engineer... Roberto Tamburello Engineering
AWu0009 Gail A E... Engineer... Gail Erickson Engineering
AWu0011 Jossef H... Engineer... Jossef Goldberg Engineering
AWu0012 Terri Le... Engineer... Terri Duffy Engineering
AWu0267 Michael ... Engineer... Michael Sullivan Engineering
AWu0270 Sharon B... Engineer... Sharon Salavaria Engineering
# show all properties :
PoSH>$newusers | ft *
Name display Descrip Room Telepho FirstNa Initial LastNam Departm Company HomeDir HomeDri LogonSc Account Mail
Name tion ne me s e ent ve ript name
---- ------- ------- ---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ----
AWu0003 Robe... Engi... Roberto Tamb... Engi... $222...
AWu0009 Gail... Engi... Gail Eric... Engi... $822...
AWu0011 Joss... Engi... Jossef Gold... Engi... $A22...
AWu0012 Terr... Engi... Terri Duffy Engi... $B22...
AWu0267 Mich... Engi... Michael Sull... Engi... $AA2...
AWu0270 Shar... Engi... Sharon Sala... Engi... $DA2...
# change the name value
PoSH>$newusers |% {$_.name = $_.name.replace('AWu','NewUser')}
PoSH>$newusers | ft name,description
Name Description
---- -----------
NewUser0003 Engineering - Roberto Tamburello
NewUser0009 Engineering - Gail A Erickson
NewUser0011 Engineering - Jossef H Goldberg
NewUser0012 Engineering - Terri Lee Duffy
NewUser0267 Engineering - Michael I Sullivan
NewUser0270 Engineering - Sharon B Salavaria
As you can see I did change the Description and the Name of the users,
if we now do a new export-csv of the data we are able to make bulkchanges to the records to import and we can do the rest of the editing in excel.
you can see that working with CSV files like this is very powerfull in POwerShell, as you also could see in my CSV series before,
in the next part we will use the CSV file generated and edited to finaly start importing the Users from the file into active directory.
Enjoy,
Greetings /\/\o\/\/
Tags : Monad msh PowerShell