Dynamic Filtered Where-Object in Powershell

Hey all,

I had a new challenge to dynamically filter out an object, where the column that is filtered is passed in through a parameter.  Here is what I came up with.  This also shows how we’ve been executing queries lately.  We ditched Invoke-SqlCmd, and rely on SMO.

FUNCTION GetInventoryServers ([string]$FilterColumn, [string]$FilterValue)

#Load SMO Objects
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ConnectionInfo”) | Out-Null;

$ManagementNodeServer = “ManagementServer”
$ManagementDatabaseName = “MgmtDB”

$MgmtServerSMO = New-Object (‘Microsoft.SqlServer.Management.SMO.Server’) $ManagementNodeServer
$MgmtDatabaseSMO = $MgmtServerSMO.Databases[$ManagementDatabaseName]
$GetActiveServersQuery = “SELECT Server,IPAddress,Client FROM Inventory”

$AllInventoryServers = ($MgmtDatabaseSMO.ExecuteWithResults($GetActiveServersQuery)).Tables[0]

if($FilterColumn -ne “” -and $FilterValue -ne “”)
$conditions = “`$_.” + $FilterColumn + ” -like ‘*” + $FilterValue + “*'”
$filter = [ScriptBlock]::Create($conditions)
$FilteredServers = $AllInventoryServers | WHERE-OBJECT $filter
return $FilteredServers
return $AllInventoryServers

This entry was posted in Uncategorized. 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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s