Use PowerShell to Create Active Directory User Accounts from Data Contained in the Adventure Works 2012 Database

This past Saturday, I presented a session at PowerShell Saturday 003 in Atlanta. Towards the end of the presentation, I created 290 Active Directory user accounts by using the information for employees contained in the Adventure Works 2012 database. This is actually a PowerShell script that I whipped up Friday night at the hotel after the speaker dinner. I populated some demographic information by joining multiple tables together from that particular database. There is more demographic information to be had, but I got tired of joining tables and thought this was good enough for a proof of concept.

I'm using implicit remoting to keep from having to install the Active Directory tools or SQL tools on the Windows 8 computer that this script is being run from.

 1Import-PSSession -Session (New-PSSession -ComputerName dc01) -Module ActiveDirectory
 2Import-PSSession -Session (New-PSSession -ComputerName sql01) -Module SQLPS
 3
 4Invoke-Sqlcmd -ServerInstance sql01 -Database AdventureWorks2012 -Query `
 5"select Employee.LoginID, Person.FirstName as givenname, Person.LastName as surname,
 6Employee.JobTitle as title, Address.AddressLine1 as streetaddress, Address.City,
 7Address.PostalCode, PersonPhone.PhoneNumber as officephone
 8from HumanResources.Employee
 9join Person.Person
10on Employee.BusinessEntityID = Person.BusinessEntityID
11join Person.PersonPhone
12on Person.BusinessEntityID = PersonPhone.BusinessEntityID
13join Person.BusinessEntityAddress
14on PersonPhone.BusinessEntityID = BusinessEntityAddress.BusinessEntityID
15join Person.Address
16on BusinessEntityAddress.AddressID = Address.AddressID" |
17
18select @{l='Name';e={$_.givenname+" "+$_.surname}},
19@{l='SamAccountName';e={$_.loginid.tolower().substring(16)}},
20@{l='UserPrincipalName';e={$_.loginid.tolower().substring(16)+"@mikefrobbins.com"}},
21@{l='DisplayName';e={$_.givenname+" "+$_.surname}},
22title, givenname, surname, officephone, streetaddress, postalcode, city |
23
24New-ADUser -Path "OU=AdventureWorks Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com" -PassThru |
25select Name, SamAccountName, UserPrincipalName | Format-Table -AutoSize

create-aw-users1.png

The amazing thing is the entire process including creating the PSSessions only takes 23 seconds to create all 290 Active Directory user accounts:

create-aw-users2.png

If the PSSessions are created first, it only takes 5 seconds to create all 290 user accounts:

create-aw-users3.png

Except for creating the PSSessions, this is all a PowerShell one liner! The crazy insane thing is these VM's are running on an older single CPU system with an E6600 CPU and 8GB of RAM, although all of the servers are running server core with no GUI (low overhead :-)).

µ