Hello everyone,
I have a script (actually find on Poshcode) I want define if and else conditions and write output. I am having issue figure out manipulate returning data. Here is the script;

function Get-OLEDBData {
param (
[string]$server = "Server1",
[string]$instance ="PPP"
 # [string]$port ="1527",
 # [int]$Threshold ="70" )
 $sql = "select * from dba_tablespace_usage_metrics"
 $connectstring = "password=mypass;UserID=myuser;DataSource=$instance;Provider=OraOLEDB.Oracle"
 $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring) \$OLEDBConn.open()
 $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn) $readcmd.CommandTimeout = '30'
$da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
$dt = New-Object system.Data.datatable
 [void]$da.fill($dt)
 $OLEDBConn.close()
 return $dt }

this returns;

TABLESPACE_NAME  USED_SPACE   TABLESPACE_SIZE USED_PERCENT
---------------  ---------    --------------- ------------
ASSRM            52543200     57647104        91.14629591800483160437686514
PSTRMC    	 2268816      3200000         70.9005

Now I want to manipulate this data and define if and else condition such as = if (  tablespace_name -eq etc. ) or elseif ( used_percent -gt "30") and etc. However I am running into problem.

 if($_.used_percent -gt "30") {
Write-host Table Space Name $Tablespacename is crossed the threshold current value is $Used_Percent and $Used_Space for the database $instance and on the server $Server
} 
the output i want to see for each table space for example;

Table Space name ASSRM is crossed the threshold current value is 91.14 and used Space 52543200 for the database PPP and the server Server1

I coud not figure to create variable for $tablespacename  $used_percent and $used_space then use for the output.
Any help greatly appreciated.
 Thank you.

Try this:

$server = "Server1"
$instance = "PPP"

$dataTable = Get-OLEDBData -Server $server -Instance $instance

$dataTable.Rows | ForEach-Object {
    if ($_["USED_PERCENT"] -gt 30)
    {
        Write-host
        ("Table Space Name {0} is crossed the threshold current value is {1} and {2} for the database {3} and on the server {4}" `
        -f $_["TABLESPACE_NAME"], $_["USED_PERCENT"], $_["USED_SPACE"], $instance, $server)
    }
}

1. I updated the script so that it accepts a path to a CSV file. The script assumes that the CSV file is in the format:

Server, Instance
someServer, someInstance

2. I'm not exactly sure what you mean when you say "same output". If you mean that you don't want "11" being outputted, pipe $adapter.Fill() to Out-Null. I updated, the script with this assumption.

3. I don't know what the effect is when you put a comma after the return keyword. All I know is that when you precede a variable with a comma, it wraps the variable into an array.

param (
    [String]$Path
)

function Get-OraTableSpace
{
    param (
        [String]$Server,
        [String]$Instance,
        [String]$Port = "1527"
    )
    
    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
   
    $connection = New-Object System.Data.OracleClient.OracleConnection( `
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$server)(PORT=$port)) `
        (CONNECT_DATA=(SERVICE_NAME=$instance)));User Id=myuser;Password=mypass;");
    
    $set = New-Object System.Data.DataSet   
    $query = "select TABLESPACE_NAME , round ( USED_SPACE / (1024 * 1024),2) as USED_SPACE_MB , TABLESPACE_SIZE/1024 TABLESPACE_SIZE_MB , round(USED_PERCENT,2) USED_SPACE_PERCENTAGE from  dba_tablespace_usage_metrics"
    
    $adapter = New-Object System.Data.OracleClient.OracleDataAdapter ($query, $connection)
    $adapter.Fill($set) | Out-Null
    
    return $set.Tables[0]
}

function Main
{
    Import-CSV -Path $Path | ForEach-Object {
        $server = $_.Server
        $instance = $_.Instance
   
        (Get-OraTableSpace -Server $server -Instance $instance).Rows | ForEach-Object {
            if ($_["USED_SPACE_PERCENTAGE"] -gt 30.00)
            {
                Write-host
                ("Table Space Name {0} is crossed the threshold current value is {1} and {2} for the database {3} and on the server {4}" -f $_["TABLESPACE_NAME"], $_["USED_SPACE_PERCENTAGE"], $_["USED_SPACE_MB"], $instance, $server)
            }
        }
    }
}

Main

New to powershell

What would the command look like when passing in comma delimitted server list?