Mass request for Sybase servers via PowerShell

Good afternoon, colleagues.

Today I want to show a simple way how to use powershell execute the same query immediately to all servers that are running Sybase. To do this, we need a parameter $host_name  enter a comma, as in the example server names or ip address, which is set Sybase. Then, in the course of execution, you need to enter a query directly as it is entered in isql. After that, the data will be requested from the user(the default sapsa) and password for each system. And in the end we will get the output result of the query. Conclusion is not very convenient, sometimes it is more convenient as a table, but alas, some queries return a lot of data(example sp_configure), and then the table is not necessary. If you have questions and suggestions gladly answer. For example, is the result of the query “select @@sbssav”.In addition, it is unnecessary to ask you to name the server (such as TST), the script itself determine what your server name.

Here content of script(Requirements:installed Sybase ASE ODBC driver, credentials for access servers service):

#==================================================Variables

$host_name = “host1″,”host2″,”host3″,”host4o”,”host5″,”host6″

#===================================================Function

#Function create objets with needs parameters for generating connectionstring

Function SapInstance{

param($host_name,$Server_name)

  $SapInst = “” | Select `

@{n = “Host_name”;e={$host_name}},`

@{n = “Server_name”;e={$Server_name}},`

@{n = “Query_result”;e={$Query_result}}

return $SapInst

}

#Function do query to Sybase and return result

Function Sql-query{

param($query,$ConnectionString)

$conn=New-Object System.Data.Odbc.OdbcConnection

$conn.ConnectionString= $ConnectionString

$conn.open()

$cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)

$cmd.CommandTimeout=30

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.odbc.odbcDataAdapter($cmd)

$da.fill($ds)

$conn.close()

Return $ds

}

#===================================================Body

$arr = @()

$host_name | foreach {

    $Server_name = (Get-Service -ComputerName $_ -Name SAP*_00).name.substring(3,3)

    $arr += SapInstance $_ $Server_name

    Remove-Variable Server_name

}

$query = Read-Host -Prompt “Enter SQL query”

#Выбор необходимых задач за текущую дату

$sql = @()

$arr | foreach {

    #Generating connection string

    $credential = $host.ui.PromptForCredential(“Need credentials”, “User name and password for Sybase ” + $_.Server_name + ” server name.”, “sapsa”, “”)

    $ConnectionString = “driver={Adaptive Server Enterprise}; dsn=” + $_.Server_name + “;db=master;na=”`

     + $_.host_name + “,4901;uid=” +`$credential.username + “;pwd=”

     #Execute query

     $_.Query_result = (Sql-query $query ($ConnectionString + $credential.getNetworkCredential().password + “;”))

     Remove-Variable credential,ConnectionString

}

#Output querys results

$arr | foreach {

    Write-Host “Host:”$_.host_name -ForegroundColor DarkYellow

    write-host “Server name:”$_.server_name -ForegroundColor Cyan

    $_.query_result[1].tables | ft -Wrap

    Write-Host (“=”*70)

}