Venturing into the world of PowerShell & DataTables

‘Sup PSHomies,

Been rethinking my Active Directory snapshot approach…

There are many ways to Rome, so which one to you choose? I’ve always been a fan of CSV and the Export-Csv cmdlet. Then I found out that Export-CliXML makes for a better experience when it comes to saving PS Objects (just don’t try figuring the tags out). Use the Import-CliXML cmdlet and you’ve got your PS Objects with Types back and in tact! Kevin Marguette has a great blog about this. While you’re at it, Jeff Hicks also has a great series on this topic, sharing is caring… 🙂

Gathering all you Active Directory data in one PS Object might not be the best approach when it comes to performance on a sizable AD. In my test lab, that wasn’t an issue. When I ran my script against a sizable AD, let’s just say I ran into some memory issues… 😉

My objective is to:

• Gather AD data for offline reporting  purposes.
• Keep data fragmentation to a minimum.

Let’s gather ADUser data for this exercise…

I want to collect the following data on AD users:

• Disabled
• Expired
• NoExperationDate
• Inactive
• MustChangePassword
• CannotChangepassword
• All User & Properties

Approach #1

Pretty straightforward. Use the pipeline to keep resource usage to a minimum. When processing large collections you should always think pipeline!

Get-ADUser -Filter * -Properties * |
Export-Clixml .\export\dsa\dsADUsers-CliXML.xml -Encoding UTF8

Exporting directly using Export-CliXML will get the job done. I could do this for each AD User query:

• Search-ADAccount -AccountDisabled
• Search-ADAccount -AccountExpired
• Get-ADUser -LDAPFilter ‘(|(accountExpires=0)(accountExpires=9223372036854775807))’
• Search-ADAccount -AccountInactive
• Get-ADUser -Filter {pwdLastSet -eq 0}
• Get-ADUser -Filter * -Properties CannotChangePassword |Where-Object {$_.CannotChangePassword}
• Get-ADUser -Filter * -Properties *

Nothing wrong with that, other than having a bunch of CliXML files. Collecting everything first isn’t an option performance wise.

Approach #2

Use a data Set/Table.

Full disclosure: This is my first attempt using data set/tables. I’m pretty sure that there’s a better approach and I’d love to here all about it!!!

I (re)found Chrissy LeMaire blog on basic .NET Datasets. Now why doesn’t that surprise me? 😛 Hehe…

	$dsADUsers = New-Object System.Data.DataSet
	$dtADUsers = New-Object System.Data.Datatable 'ADUsers'
	$columnsADUsers = Get-ADUser -Identity guest -Properties * |
	Get-Member -MemberType Property |
	Select-Object -ExpandProperty Name
	$columnsADUsers |
	Foreach-Object{
	[void]$dtADUsers.Columns.Add("$($_)")
	}
	Get-ADUser -Filter * -Properties * |
	Foreach-Object{
	$Row = $dtADUsers.NewRow()
	foreach ($column in $columnsADUsers){
	$Row["$($column)"] = if($_.$column -ne $null){($_ | Select-Object -ExpandProperty $column) -join "`n"}else{$null}
	}
	$dtADUsers.Rows.Add($Row)
	}
	$dsADUsers.Tables.Add($dtADUsers)
	$dtADUsers.WriteXml('c:\scripts\export\dsa\dtADUsers.xml')

view raw Get-dtADUsers.ps1 hosted with ❤ by GitHub

 

Quick breakdown: First I created a Dataset (prefix ds just in case you were wondering…) and the data table. I wanted all available properties. I realize this might be more than you need so feel free to change that to your needs. To get the necessary column Names I’m using the Get-Member cmdlet with parameter MemberType Property. A quick select and expand and I have column names, beats hard coding column names…

Ok here’s where it gets a bit tricky, So I’m processing each object as it goes through the pipeline, but I do have a datatable object… Like I said first attempt…

Once the data table is ready just add it to the dataset! A dataset can hold multiple datatables which helps in solving my data gathering fragmentation.

Fun fact

Did you know that you can write/export your datatable to XML? Unlike CliXML output this one is legible…

To write the dataTable to XML:

$dtADUsers.WriteXml('c:\scripts\export\dsa\dtADUsers.xml')

Same command applies for the dataSet 🙂 Added bonus is the file size, much smaller than CliXML, but then again not as rich…

Take away

I love the fact that there’s more than one way to work with data. I guess it comes down to preference. I was pleasantly surprised by the datatable XML formatting, nice and clean! If  export file size is an issue, then datasets can help, the trade off being you’ll loose rich data of the PS objects. The *CliXML cmdlets are sufficient for my needs, if I’m honest with myself, still glad I looked into data sets/tables…

Now If you have a full fledged SQL environment you can take advantage of, then, Go for it!!! Just ask Rob Sewell aka sqldbawithbeard  or Chrissy LeMaire, our PowerShell SQL experts to point you in the right direction!

The more you know! 😉

Hope it’s worth something to you…

Ttyl,

Urv

Advertisement