Shiju Samuel Shiju Samuel - 1 year ago 186
PowerShell Question

Powershell Function Parameters for filtering

I am trying to write a function which pulls all my servers from sql database. It works correctly. I need to implement filtering for each columns so I can call the function with one or more filtering ..

for example if I need to pull all UAT server I should be able to write something like this and it should pull all UAT -

Get-SHservers -Enviornment "uat"


Get-Shservers -Enviornment "uat" -Application "App1"

or any combination of that

function Get-SHServers
$connectionstring = (Get-SHJson "D:\config\configdb.json").value
$server = Get-SHSQLData $connectionstring -Query @"
b.Name as ApplicationName,
a.Name as ServerName,
c.Name ServerRole,
e.Name as Enviornment,
d.Name Domain,
f.Name ServerRegion
server a inner join Application b on a.ApplicationID = b.ID
left join ServerRole c on a.ServerRoleId = c.Id
left join Domain d on a.DomainID =
left join Enviornment e on a.EnviornmentId = e.Id
left join ServerRegion f on a.ServerRegionID = f.Id
order by 1
foreach($item in $server) {
$output = [ordered] @{
ServerName = $item.ServerName
FQDN = $item.FQDN
ApplicationName = $item.ApplicationName
ServerRole = $item.ServerRole
Domain = $item.Domain
Enviornment = $item.Enviornment
ServerRegion = $item.ServerRegion
$obj = new-object -TypeName PSObject -property $output
$obj.psobject.TypeNames.Insert(0, "sh.config.server")
write-output $obj

I prefer to infuse the filter in SQL for performance reason any help to implement this behavior? * Should I be passing 7 different parameters to the function and validate in an [if else] if one or more parameter is passed and construct SQL or there is an easier way in Powershell to do this *

Also, here is the implementation of Get-SHSQLData

[CmdletBinding()] param(

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Command = New-Object System.Data.SQLClient.SQLCommand
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet

if ($Query.Trim() -eq '' -or $connectionstring.Trim() -eq ''){
write-host 'Query and ConnectionString are Mandatory Parameter' -ForegroundColor Red;
#$connectionstring = "server=$servername;database=$DatabaseName;trusted_connection=true;pooling=false"
$connection.ConnectionString = $connectionstring;
$Command.Connection = $Connection
$Command.CommandText = $Query
$Adapter.SelectCommand = $Command
$Adapter.Fill($DataSet) | out-NULL
return $DataSet.Tables[0]

Answer Source

Yes, you should be passing 7 different parameters to the function but, no, there is not really an easier way to do it because it is so straightforward already. Here is a sample with your first two parameters:

function Get-Stuff

     $whereClause = '1=1'
     if ($AppName) { $whereClause += " AND ApplicationName = '$AppName'"}
     if ($ServerName) { $whereClause += " AND ServerName = '$ServerName'"}
     Write-Host $whereClause    # this line is debug output only

And here are a few examples demonstrating its use:

PS> Get-Stuff

PS> Get-Stuff -AppName "my app"
1=1 AND ApplicationName = 'my app'

PS> Get-Stuff -Server 'localhost' -AppName 'big app'
1=1 AND ApplicationName = 'big app' AND ServerName = 'localhost'

(Footnote: Take a look at Invoke-SqlCmd, which should allow you to significantly reduce the size of your code.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download