Home > Accounts, PowerShell > PowerShell Script to export data from Active Directory

PowerShell Script to export data from Active Directory

I was asked to assist a vendor who was working with our Unix/Oracle team. He was trying to import data from Active Directory into an Oracle database.

 

I’m no expert on Oracle, but it appears that the LDAP connector simply returned blobs of data, this then had to be parsed, but there seems to be no order to the data returned. The was before we starting looking at converting the objectGUID or seeing if we’d hit the 1000 record AD limit (which SQL Server suffers from).

 

I’d recently been on a PowerShell course delivered by Microsoft, so I thought I’d write a quick script to export the data. In this case, we output the result to a CSV file, but it is possible to update the database directly. Anyway, here’s the script we used:

 

# LDAP path to start search from
$RootOU = "LDAP://OU=Regional Accounts,OU=User Accounts,DC=domain,DC=net"

# CSV path
$CSVpath = "C:\TEMP\ADusers.csv"

$search = New-Object DirectoryServices.DirectorySearcher([ADSI]$RootOU)
$Search.PageSize = 1000
$search.filter = “(&(objectCategory=person)(objectClass=user))"
$results = $search.FindAll()

$myData = @()
foreach($result in $results){
   $User = $result.GetDirectoryEntry()
   $myData += ( $User | Select-Object -Property @{Name="objectGUID";Expression={($_.objectGUID | foreach { $ofs="" } { "{0:X2}" -f $_})}},
   @{Name="sAMAccountName";Expression={$_.sAMAccountName}},
   @{Name="homeDirectory";Expression={$_.homeDirectory}},
   @{Name="mail";Expression={$_.mail}},
   @{Name="sn";Expression={$_.sn}},
   @{Name="givenName";Expression={$_.givenName}},
   @{Name="mailNickName";Expression={$_.mailNickName}},
   @{Name="title";Expression={$_.title}},
   @{Name="postOfficeBox";Expression={$_.postOfficeBox}},
   @{Name="accountexpires";Expression={[datetime]::fromfiletime($_.ConvertLargeIntegerToInt64($_.accountexpires[0])).ToString("s")}},
   @{Name="employeeNumber";Expression={$_.employeeNumber}}
)
}
$myData | Export-csv -path $CSVpath -notype

# Or, you can output to a grid
#$myData | Out-GridView
Advertisements
Categories: Accounts, PowerShell
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: