Use this lovely utility for bulk importing your company's joiners into Active
Directory. List your users' properties in
Excel, then export to a .csv file; from there use SolarWinds free 'Import
user' tool to create the accounts in Windows Active Directory.
Preparation is the key to a successful import. In particular,
plan the attributes that you wish to import and list them in the top row
of your spreadsheet / CSV file. Bulk importing users into ad with
this free product is much easier than using the command-line CSVDE import.
Don't worry if your list of fields is not a perfect match for
the LDAP properties in active directory; one of the cleverest features
of SolarWinds' user import tool is the ability to map your idea of user fields, to
those of Active Directory. See the 'First Row Fields' mapping
below.
Once you have completed the mapping, just a few more clicks
in the GUI, a
quick check, and you the new users will be born into your Windows Active
Directory domain.
Create Mailbox-enabled Users If you have Exchange Server, then place the radio button next to
'Create AD account and Exchange mailbox. Once again,
SolarWinds' free bulk import tool is much
easier than grappling with CSVDE in a DOS box.
SolarWinds provide the CSV import utility as part of a 3-in-one free download. The other two tools
remove inactive accounts, one for users the other for computers.
Just select the date, and the tool display all the old accounts.
The key is to check, and even export a list of these victims, that
way
at least you can review who was deleted.
In truth, I don't have much use for these tools as I prefer to
disable inactive accounts, or move them to a special OU. However,
I am beginning to think I am a hoarder, and it could be time for a spring
clean of very old accounts, not to mention those zillions of test
accounts that I won't ever need again.
I found the hardest part of the install was remembering where I
downloaded the zip file! Another point, as its name suggests
install this CSV Import User utility on a domain controller.
The rest was easy, I extracted the two .msi files called
ServiceInstaller and UserImportTool, and then clicked on the
licence agreement, and let the install wizard perform the setup.
After the install completes the importer launches you are ready to
begin a csv import into active directory. Should you need find the program later look on the
start menu, or if all else fails navigate to:
C:\Program Files (x86)\SolarWinds\UserImportTool
Should you need to uninstall, then go to the Control Panel, Programs,
scroll down to 'U' for User Import Tool. It's a sign of a good
CSV
freeware program that it uninstalls easily and completely.
Recommendation: Click on the 'Get Geeky' tab and
visit the
Thwack forum.
LDAP Fields for Active Directory
Here are the commonest LDAP (Lightweight Directory Access Protocol) attributes which are handy when creating new
users from CSVDE or this freeware tool:
samAccountName - Must be unique
CN - Common Name
DN (Distinguished Name) The most important LDAP attribute. CN=Guy
Thomas OU= Wales,DC=cp,DC=com
It's worth spending the time to check how the LDAP attributes map to the Active Directory boxes.
One of my favourite research techniques is to add a value in the Active
Directory box, then export using CSVDE, finally open the file in Excel and search for the value.
How to Build-up a DN (Distinguished
Name) Here is how to import AD users with a csv. My example is of a
distinguished name is: CN=Guy Thomas,OU=Wales,dc=cp,dc=com.
Not only is DN important as is a mandatory field, but also DN has to be unique. However, if you take it step-by-step it's not as
daunting as it first appears. Extend the sAMAccountName method but include not one, but three elements. Split the DN into CN (Common Name), OU (Organizational Unit) and dc (domain context).
1st Element CN= Conceptually we need to script: Guy Thomas (or CN=Guy Thomas,)
Excel formula: ="CN="& c2 & " " & d2 &","
You could build the first element by joining all of the givenName with all of the SN. For example, =c2 & " " & d2 result =Guy Thomas.
To be precise, you need to add cn= and a comma. For example, CN=Guy Thomas,. So the Excel formula would be ="cn=" & c2 & " " & d2 & ",". Often it's the punctuation that catches you out, note how I included a
space with a null string " ", also remember the comma at the end ",".
2nd Element
OU= If you remember, the second element is the OU. The simplest solution is to 'hard code' with, & "OU=Wales,". However if you have a production file where accounts will be in different
OUs you could store the OU name in little used LDAP field such as extensionAttribute12. In which case the formula becomes & "OU=" & x2 & ",". Where x2 is the cell holding the OU name. The
advantage is where you copy one row, the following rows will have the correct OUs. If you use the hard code method, all users will have the same OU.
3rd Element dc= The final element
has one or more dc=, elements. People often take their eye of the ball when the copy dc=, this partly because amateurs think dc= means domain controller, professionals know it means domain context.
Therefore, do pay attention to the detail of your domain name. The key question does your domain have a .com or .net extension? Mistaking YourDom for YourDom.com would cause the import to
fail.
As a matter of scripting technique, I store this value in one cell, say z2. then I script the absolute reference by adding dollar
signs thus, & $x$2. In that cell z2 would be "dc=cp,dc=com". If you wished to keep is simple you could just add: & "dc=cp,dc=com".
You have now reached a stage where you can either use CSVDE -import,
or else use SolarWinds CSV importer to bulk import from csv into AD.
Example Using PowerShell
Import-CSV
PowerShell has several built-in cmdlets which cater for CSV files.
This free program helps to map CSV fields.
# PowerShell Import-CSV data from a spreadhseet Clear-Host $Spread =
"D:\PShell\Serv4s.csv" Import-CSV $Spread
Summary: Evaluation of CSVDE User Import Tool
SolarWinds provide a great tool to bulk import users into Active
Directory. All you need for this CSV freeware is a spreadsheet with the fields in the
first row. This tools saves time by using CSVDE to create computer
or users accounts in AD.
As a bonus, in your download are two more free utilities, these will
help delete old accounts or test users.
Additional Free and Trial SolarWinds Network Software
These are programs which I have enjoyed evaluating on my
network. Some are completely free, while other downloads are trial
versions of the full product. I think SolarWinds have a great
strategy, namely, supplying a free gadget, which may be all a small
company need, yet providing a big-brother suite of programs for
larger organizations.
Guy Recommends:
SolarWinds' NPM - Network Performance Monitor
SolarWinds' performance monitor is designed for detecting network outages,
making it easy to see what's working, and what needs your attention.
This utility guides you through creating network maps; it also helps
identifying whether the
root cause is faulty equipment, or resource overload. Give NPM a try.
