Shiju Samuel Shiju Samuel - 3 months ago 29
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"


or


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 @"
select
b.Name as ApplicationName,
a.Name as ServerName,
a.FQDN,
c.Name ServerRole,
e.Name as Enviornment,
d.Name Domain,
f.Name ServerRegion
from
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 = d.id
left join Enviornment e on a.EnviornmentId = e.Id
left join ServerRegion f on a.ServerRegionID = f.Id
WHERE 1=1
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(
[Parameter(Mandatory=$true)][string]$Query,
[Parameter(Mandatory=$true)][string]$connectionstring
)

$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;
return;
}
#$connectionstring = "server=$servername;database=$DatabaseName;trusted_connection=true;pooling=false"
$connection.ConnectionString = $connectionstring;
$Connection.Open()
$Command.Connection = $Connection
$Command.CommandText = $Query
$Adapter.SelectCommand = $Command
$Adapter.Fill($DataSet) | out-NULL
$Connection.Close()
$Connection.Dispose()
return $DataSet.Tables[0]

Answer

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
{
     param
     (
         [string]$AppName,
         [string]$ServerName
     )

     $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
1=1

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.)

Comments