PowerShell Ezine, Logon Scripts

Guy's Scripting Ezine 44 - Excel and CSVDE

I would like to start with a list of troubleshooting resources

Contents for Guy's Scripting Ezine 44 - Excel and CSVDE

This week’s secret

Every time that I revisit the combination of Excel and CSVDE, I have to brush up my syntax.  Do not get me wrong, Excel is my favourite vehicle for manipulating data and CSVDE is easy to understand.  However, my point is that between spreadsheet data and Active Directory accounts, there are an army of gremlins waiting to ambush my best intentions.

If Excel is the vehicle holding all the account information, then CSVDE is our road to Active Directory, else we could use VBScript for when we need to modify existing users.  Incidentally, I am pleasantly surprised how those spreadsheet constructions that I learnt all those years ago, can be applied to scripting and even databases.  For example, If, or, Not. 

ˆ

This week's mission - to import users from Excel by using CSVDE

The main purpose of this ezine is to revisit Excel techniques in general and concatenation (&) in particular.  My goal is to populate the rows in your spreadsheet with minimal effort.  All you need to supply is the firstname, lastname, also modify my domain name to your domain name.  Excel calculates the rest.  O.K, so you also need to sort out the OU name, my example creates an OU called Droitwich.  Over to you to amend the spreadsheet, else create an OU called Droitwich in YOUR Active Directory.

Introduction.

CSVDE is a built-in command found in Windows 2000 and Server 2003.  Be aware of the two limitations of CSVDE, firstly, you cannot modify existing objects (delete test objects, then try again).  Secondly CSVDE cannot set passwords, however, I do have a VBScript for that job.

One of my personality traits is, that when faced with a new challenge, I focus on getting a basic model working.  Only when the job is up and running, do I look at the extra bells and whistles.  So, let us begin by building a skeleton user with just the essential attributes.  I am sure that once you master the technique, then you can add further LDAP fields.  What I want to avoid is trying to create a user with the full range of attributes, including a mailbox, only to find that your domain has not been prepared for Exchange.  The consequence of trying to run before we can walk, would be failure accompanied by incomprehensible error messages.

Back to basics.  By the way, I am assuming that you are familiar with terms such as, Cell (B2) Column B and Row 2.  Our Excel spreadsheet holds all the data in a grid, each cell holds the property for one attribute.  The master list of LDAP attributes runs across Row one, while each user object occupies a different row starting at Row 2.  Crucially, the values for a particular attribute always lie under their LDAP heading in Row 1.

Just to be crystal clear, when you prepare the Active Directory data to import, the first row in the spreadsheet is crucial.  That first row always holds the LDAP field names, for Example DN, sAMAccountName and ObjectClass. 

Get to know LDAP attributes.

What we need is a reminder of how to construct the vital LDAP attributes which will combine to create our user object.

ObjectClass.  You must tell Active Directory whether you want a user, computer, group or contact.

sAMAccountName.  This is a unique name of up to 11 characters.  Here is the name that users type in the logon box.   My advice is to create the sAMAccountName by joining Firstname (givenName) and Lastname (SN).

DN.  Such a short name, but so difficult to construct.  Best to break DN down into 3, bite-sized, chunks.

1) CN=Guy Thomas,   Here is the first element which gives my Common name (CN).

2) OU=Droitwich,   This element supplies the OU where I will be created.  Incidentally, people are often confused because CSVDE has no switch to determine the OU where the objects are created.  Now you know why, the answer is the OU information is incorporated within the DN construction.

A trap, the Active Directory container called USERS is not, repeat not, an OU.  This is a container object so referred to as CN=Users, not repeat not, OU=Users.  Take the time to absorb this fact because it causes endless misunderstandings and faulty scripts.

3) DC=CP,DC=com  The key here is to remember that dc stands for domain context.  Beginners think dc refers to the name of their domain controllers - wrong.  Adjust this component to suit your domain, for example, school.edu.uk would require 3 sub-components dc=school,dc=edu,dc=uk.  On the other hand BigOneDomain would simply dc=BigOneDomain.

There are zillions of other LDAP attributes but fortunately for our purposes, all the other attributes are optional.

ˇ

Building elements with Concatenation

Probably the most important Excel principle to master, is joining elements.  In maths we would call this addition, but with text this process is called, concatenation. What we use is an operator called ampersand (&), not the usual maths operator called plus (+).  For example, to build the LDAP field CN, this is how we do it, 
CN=givenName & " " & sn.  In the context of your spreadsheet it would be, CN =C2 & " " & D2.  This is assuming that, givenName is in column C and SN is in column D.  Note the use of " " for a space.

Derive names using the Left() function.

Another useful technique is to derive the logon name, sAMAccountName from the leftmost 4 letters of the givenName joined to the leftmost 3 letters of the SN (Last Name). =Left(givenName,4) & Left(sn,3).  Again this would translate to =Left(c2,4) & left(d2,3).  This looks easyish when I write it down, but it is full of potential errors, for example =Left(4),c3 is incorrect.  Remember the syntax is: =left(text, number of characters). or (LEFT(CELL,Number)

Once the spreadsheet has been created, then Save As, .CSV (Comma delimited) file, for example GuyUsers.csv.  If I were you, then I would create a special folder say C:\ MyImport.  Next navigate to C:\ MyImport and type csvde -i -f guyusers.csv -k.  Best would be to create the spreadsheet on an actual domain controller.  Alternatively, remote desktop to connect to your domain controller.  My point is that you may get permission errors and connection errors if you run such CSVDE commands from an XP machine.  Especially in the beginning, you want the best chance of confidence building success.

Traps: Blank Columns, or columns with no LDAP field will throw CSVDE into confusion.

Summary

Computing requires many and different talents.  Take the time to master the skill combination of Spreadsheet, CSVDE and LDAP fields.  Pay careful attention to syntax, and become familiar with operators like & (ampersand) and functions such as Left().

I have created an example spreadsheet, here is the URL. Spreadsheet

Computer Training Software - Recommended Training VideosGuy Thomas recommends Computer Training Software. 

Their topics and material are ideal for getting you started with VBScript.  The videos are easy to follow and you can control the pace.  Try their free demo material and then see if you want to buy the full package. See more about VB Script Training CD.