/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$PreviousItems = (" PowerShell and Active Directory Part 4 (TypeData) "," PowerShell and MOM2005 part 2 : Updating Settings "," PowerShell Boolean FileMode "," Working with Fixed Length delimited Text files in ... "," PowerShell and Active Directory Part 3 (UserProper... "," PowerShell and MOM 2005 "," PowerShell and Active Directory Part 2 "," PowerShel and Active Directory Part 1 "," TechEd RoundUp "," A big hurray for the Scripting Guy ! "," ")

Wednesday, August 02, 2006


PowerShell and Active Directory Part 5

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')


# 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,

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

# 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

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

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

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.


Greetings /\/\o\/\/
Tags :

Comments: Post a Comment

Links to this post:

Create a Link

<< Home


October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006  

$Links = ("PowerShell RC1 Docs"," PowerShell RC1 X86"," PowerShell RC1 X64"," Monad GettingStarted guide"," Monad Progamming Guide"," Monad SDK"," Monad videos on Channel 9"," MSH Community Workspace"," scripts.readify.net "," MonadSource"," www.reskit.net"," PowerShell Blog"," Under The Stairs"," computerperformance powershell Home"," proudlyserving"," MSH on wikipedia"," MSHWiki Channel 9"," Keith Hill's Blog"," Precision Computing"," PowerShell for fun"," MSH Memo (Japanese)"," monadblog")

find-blog -about "PowerShell","Monad" | out-Technorati.
find-blog -contains "","" | out-Technorati.
Web mow001.blogspot.com

This page is powered by Blogger. Isn't yours?