Obtaining a random result set in PowerShell

Given a set of data how does one return an arbitrary subset?  Well in T-SQL we’d do this:

SELECT TOP 5 displayName
FROM person
ORDER BY NEWID() ASC

So how can we do this in PowerShell?  Well in pretty much the same way actually.  Pipe to Sort-Object and then to Select-Object.  Here’s a couple of examples:

@("a", "b", "c", "d", "e", "f") | Sort-Object {
	[Guid]::NewGuid() } | Select-Object -First 3

The above randomly sorts the String array and returns the first three results after sorting.

Get-ADUser `
    -SearchBase 'OU=People,DC=corp,DC=contoso,DC=com' `
    -Filter * | Sort-Object { [Guid]::NewGuid() } |
        Select-Object -First 10 sAMAccountName;

The above pulls any object underneath the PEOPLE OU, pipes into Sort-Object which orders by GUID and then pipes to Select-Object to only return the TOP 10 results.

Another option is to use the Get-Random cmdlet.  I only discovered this while writing this post but it seems a better PowerShell way of doing things.  Here’s a random example.

@(1, 2, 3, 4, 5, 6, 7, 8, 9) | Get-Random -Count 3

And here’s another.

Get-Service | ? { $_.Status -ne 'Running' } | Get-Random -Count 3

The problem with this approach is that the T-SQL example is somewhat optimised to not pull back the entire result set first whereas the above PS examples do indeed pull all objects first.

However it’s still handy.

And now I can find it easily next time I forget.  Smile

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in Off-topic, Scripting and tagged , , , . Bookmark the permalink.

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