Tag Archives: scripting

Poor Man’s vCPU & vRAM Right Size Recommendation Tool

VMware vCenter Operations Management Suite can be expensive. If you are like me and there is no budget for vCOPs, this script will give you a vCPU & vRAM recommendations based off of past virtual machine usage. The following script will connect to your vCenter, grab historical performance data and provide recommendations that were designed around two vKernel whitepapers. The following whitepapers are:

 

The script is simple to use only requiring the vCenter parameter to start with all defaults:

PoorMansRecommendations.ps1 -vCenter site1.local.domain

 

Specifies additional authentication information. Grabbing 60 days of past performance instead of the default 30 days:

PoorManRecommendations.ps1 -vCenter site1.local.domain -Username fred -Password root -PastDays 60

 

Specifies more samples for accuracy and using a larger ‘building block’ for memory recommendations:

PoorMansRecommendations.ps1 -vCenter site1.local.domain -PastDays 60 -MaxSamples 25000 -MemoryBuildingBlockMB 1024

 

When running the script interactively, a progress bar be displayed as it calculates recommendations per virtual machine:
Poor Man's Right Sizing

The results:

Poor Man's Recommendations Results

This should only be used as a guidance, point of reference, a conversation point or just a rough estimate. Each environment and workload characteristics are unique, please use your logic along with this data to come to a solution that is right for your environment.

Download the script: PoorMansRecommendations.ps1

Thanks for looking. Please leave any questions or comments below and have a great day!

Tagged , , , , , , ,

Oracle Database Queries in PowerShell, Script Examples

Below are some code examples to help get your data out of an Oracle database into a PowerShell object quickly! The logic to manipulate data is the same regardless of vendor: load any required libraries, define the connection string, setup the connection object, use that connection object for subsequent queries and finally close the connection.

Oracle Data Access Components (ODAC) allows you to run an Oracle complied binary within the .NET framework including PowerShell. When you are working with Oracle queries in PowerShell, it is required to download and install the Oracle Data Access Components prior to accessing a database. Download the components here, bit.ly/1t2W790. Select the appropriate architecture (x86/x86-64) and ensure the correct PowerShell program architecture is being executed with the corresponding Oracle component’s architecture. Failure to do so will lead to binary related errors while loading the assembly in PowerShell.

Load the System.Data.OracleClient Assembly:

### try to load assembly, fail otherwise ###
$Assembly = [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

if ( $Assembly ) {
    Write-Host "System.Data.OracleClient Loaded!"
}
else {
    Write-Host "System.Data.OracleClient could not be loaded! Exiting..."
    Exit 1
}

Setup the connection string and open a database connection:

### connection string ###
$OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracledb.domain.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=oracledb.domain.com)));uid=user;pwd=password;"

### open up oracle connection to database ###
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
$OracleConnection.Open()

Stored procedure example with parameters example:

try {

    ### create object ###
    $GetHostIDCommand = New-Object System.Data.OracleClient.OracleCommand;
    $GetHostIDCommand.Connection = $OracleConnection
    $GetHostIDCommand.CommandText = "GET_HOST_ID"
    $GetHostIDCommand.CommandType = [System.Data.CommandType]::StoredProcedure

    ### add storeprocedure paramaters ###
    $GetHostIDCommand.Parameters.Add("p_hostname", [System.Data.OracleClient.OracleType]::VarChar).Value = $Computer.ToUpper();
    $GetHostIDCommand.Parameters.Add("p_host_id", [System.Data.OracleClient.OracleType]::Number).Direction = [System.Data.ParameterDirection]::Output

    ### execute storedprocedure ###
    $GetHostIDCommand.ExecuteNonQuery() | Out-Null

    ### get the data from output defined by the storedprocedure ###
    $HostID = $GetHostIDCommand.Parameters["p_host_id"].Value

    ### dispose of object ###
    $GetHostIDCommand.Dispose()

}
catch { Write-Host "$Computer : Failed to GET_HOST_ID" }

SQL select query text example:

try {

    ### sql query command ###
    $OracleSQLQuery = "SELECT * FROM HOSTS"

    ### create object ###
    $SelectCommand = New-Object System.Data.OracleClient.OracleCommand;
    $SelectCommand.Connection = $OracleConnection
    $SelectCommand.CommandText = $OracleSQLQuery
    $SelectCommand.CommandType = [System.Data.CommandType]::Text

    ### create datatable and load results into datatable ###
    $SelectDataTable = New-Object System.Data.DataTable
    $SelectDataTable.Load($SelectCommand.ExecuteReader())

}
catch {

    Write-Host "Error while retrieving data!"

}

SQL update command text example:
(same would be for an insert command with no expected output)

try {

    ### sql update command query ###
    $HostUpdateHostSQL = "UPDATE HOSTS h SET h.PHYSICAL = '$(Convert-Boolean($HW_PHYSICAL))' WHERE h.ID = '$HostID'"

    $HostUpdateCommand = New-Object System.Data.OracleClient.OracleCommand;
    $HostUpdateCommand.Connection = $OracleConnection
    $HostUpdateCommand.CommandType = [System.Data.CommandType]::Text
    $HostUpdateCommand.CommandText = $HostUpdateHostSQL

    ### execute update command query ###
    $HostUpdateCommand.ExecuteNonQuery() | Out-Null

}
catch {

    ### output exception to screen ###
    Write-Host "$Hostname : ERROR! $HostUpdateHostSQL"
    Write-Host "Dump : $($_.Exception.ToString())"

}
### dispose regardless ###
finally { $HostUpdateCommand.Dispose() }
Tagged , , , , ,