General

Using PowerShell to populate user’s properties using spreadsheets

I recently had to make a massive query to a large directory get some user attributes. The original set of usernames I had to query was on an ISO/IEC 26300:2006 (ODF) ODS spreadsheet. Both input and output needed to be open in Excel.

This piece of PowerShell connects to a domain (I haven’t found a way to do forests automatically), reads usernames from the first column ($i,1) of an ODS file, searches the object (using standard LDAP filters, and searching by the attribute samaccountname) then fetches an attribute (title) and outputs back to a CSV file.

PowerShell reminds me a lot of Perl, with some hints of Ruby. The use of the pipe, however, is really powerful as it passes along objects to other methods, not just streams of text. Thus, you can use functions like export-csv on a very simple way even when compared to Perl. PowerShell also has this powerful feature of opening COM objects and operating on them from PowerShell, truly opening the array of possibilities. Given that virtually all current parts of the Evil Stack are now operable thru PowerShell this really is a paradigm shift for sysadmins – but for now I shall continue operating on LDAP and data. It’s good to see pash around on GitHub, though.

$objDomain = New-Object System.DirectoryServices.DirectoryEntry("LDAP://dc=foo,dc=com")
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher

[array]$people = $null

$objSearcher.SearchRoot = $objDomain
$objSearcher.PropertiesToLoad.Add("title")

$xl = New-Object -ComObject "excel.application"
$xl.Workbooks.Open("input.ods")

$workbooks = $xl.Workbooks
$sheet = $workbooks.Item(1).worksheets.item(1)

$i = 1

do {
$alias = $sheet.cells.item($i,1).value2
$objSearcher.Filter = "(&(samaccountname=$alias))"
$contact = $objSearcher.FindOne()
$title = [string]$contact.properties.title
$person = New-Object PSObject
$person | Add-Member NoteProperty alias $alias
$person | Add-Member NoteProperty title $title
$people += $person
$i++
} while ($sheet.cells.item($i,1).value2)

$people | export-csv -path output.csv

Standard

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