Category 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!

Advertisements
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 , , , , ,

Setup Linux RedHat Up.time SSL Agent

The Windows version of the SSL Up.time Agent was cover by the following article, Setup an Windows Up.time SSL Agent (stunnel & Scripted).

This version goes over setting up a Linux agent on a RedHat based distribution. The distribution that I was working with through this guide was RedHat Enterprise Linux 5.6. First start off by going to the Up.time Software website and downloading the latest rpm package of the Linux Up.time Agent.

Here is the step by step break down of how I was able to configure the Up.time Agent to use SSL in RHEL.

Install the up.time Linux Agent by issuing the following command.

sudo rpm -ivh uptimeagent-5.3.0-linux-x86_64.rpm

Copy-paste the contents to ‘/opt/uptime-agent/conf/agent.conf’, overwrite the existing agent.conf file.

port=9997
user=uptimeagent
mode=xinetd
timeout=60
log_file=/opt/uptime-agent/logs/uptimeagent.log
log_level=NONE

Create ‘uptimeagent.conf’ and copy the contents below to the file

cert=/etc/stunnel/uptime_agent.pem
exec=/opt/uptime-agent/bin/uptimeagent

Generate the SSL certificate

openssl req -x509 -nodes -days 3650 -subj '/C=US/ST=Ohio/L=Cleveland/O=My Company/OU=My Department/CN=uptime-agent' -newkey rsa:1024 -keyout uptime_agent.pem -out uptime_agent.pem

Copy ‘uptimeagent.conf’ and ‘uptime_agent.pem’ to ‘/etc/stunnel’.

sudo cp uptimeagent.conf /etc/stunnel
sudo cp uptime_agent.pem /etc/stunnel

Set the correct ownership and permissions by running the following:

sudo chown root:nobody /etc/stunnel/uptime_agent.pem
sudo chmod 640 /etc/stunnel/uptime_agent.pem
sudo chmod 755 /etc/stunnel/uptimeagent.conf

Replace the contents of ‘/etc/xinetd.d/uptimeagent’ with

service uptimeagent
{
disable = no
flags = REUSE
socket_type = stream
wait = no
user = nobody
server = /usr/sbin/stunnel
server_args = /etc/stunnel/uptimeagent.conf
}

In ‘/etc/services’ ensure the last lines look like the following

# *** Installed by the uptimeagent installer
uptimeagent 9997/tcp # uptimeagent agent

Restart the ‘xinetd’ service

sudo /etc/init.d/xinetd restart
Tagged , , , , , , ,

Setup Windows Up.time Monitoring SSL Agent

This guide will show you how to install and configure an Up.time Agent for Windows using SSL. Up.time provides a guide which give a good outline of the steps required to get an Up.time agent configured in Windows using SSL but I think many will find this information very useful since it will automate installations using a batch file and will determine the architecture of Windows then places the files/registry keys in the correct location based on the processor architecture.

First, a certificate must be generated for the Up.time agent to use. OpenSSL tools will be required to generate the appropriate certificate. To generate the certificate, issue the following command from the bin directory of the OpenSSL installation.

openssl req -x509 -nodes -days 3650 -subj '/C=US/ST=Ohio/L=Cleveland/O=My Company/OU=My Department/CN=uptime-agent' -newkey rsa:1024 -keyout uptime_agent.pem -out uptime_agent.pem

Stunnel is the piece of software that wraps around the Up.time agent port and encrypts the traffic since the Up.time agent is not natively using SSL. Stunnel is an open-source project and can be downloaded at www.stunnel.org.

Download Stunnel and extract it to a directory. Place the newly generate certificate in the directory. Overwrite the downloaded stunnel.conf with the stunnel.conf that is listed below. Modify the up.time-stunnel-agent.bat script to the correct UNC/SMB/SAMBA paths and save the file.

Finally, place the remaining files into the directory structure. Follow the directory structure below
Uptime Agent SSL Directory View Screenshot

Don’t forget to goto the Up.time Software website to get the .exe version of the Windows Up.time Agent.

NOTE: If you wish to change the port numbers that is in hexadecimal in the UptimeCMDPassword_x86.reg and UptimeCMDPassword_x64.reg files and must be changed in the stunnel.conf file.

The rest should explain it self. If you have any questions, post a comment and I will try to help you the best that I can.

Continue reading

Tagged , , , , , ,