Category Archives: Programming

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

Raspberry Pi, Edimax Wifi, DYMO Printer VMUG Check-in Process Tutorial

This article is out-of-date. It should only be used for historical reference. A new article will be published to reflect the updated code located at https://github.com/tkrn/pivmugc

Project Summary

At the Cleveland VMUG events we will be using a new check-in system. The system consists of a Raspberry Pi, Edimax Wifi adapter and a DYMO thermal label printer to perform wireless iPad check-ins. The setup will sign-in preregistered individuals and capture data from walk-on attendees. Finally, producing a name tag label which is printed wireless to the DYMO LabelWriter 330. This is how it was accomplished!

Videos

 

Hardware Layout

Pi-DYMO

The diagram is simple and straight forward but to give you a visual how things are related.

Prerequisites

Most important some sort of already existing Linux knowledge is highly recommended! This (updating + installing) will require a great amount of time since the Rasberry Pi’s processor is low in computational power and low IO through output since the Secure Digital memory card is generally slow. I started with a fresh image of Raspbian. At the time of writing, this tutorial is using version, 2014-09-09-wheezy-raspbian.

Let’s start by installing all the prerequisites libraries and binaries. Please complete the apt-get commands in order to prevent php5 installing apache2 since we are leveraging lighttpd.

sudo apt-get update -y
sudo apt-get install dnsmasq lighttpd cups libcups2 libcups2-dev libcupsimage2 libcupsimage2-dev build-essential g++ p7zip-full -y
sudo apt-get install php5-common php5-cgi php5-sqlite php5 -y

CUPS Installation & DYMO Driver

If you are using the pi user, add the pi user to the lpadmin (CUPS Admin) group:

sudo usermod -a -G lpadmin pi

After the installation of CUPS we will need to modify the following sections in /etc/cups/cupsd.conf to look like the following below. This will allow remote access to the CUPS Administration page and the CUPS web server will operate on all interfaces not just the localhost.

# Allow remote access
Port 631
Listen /var/run/cups/cups.sock

...

# Restrict access to the server...
<Location />
  # Allow remote access...
  Order allow,deny
  Allow all
</Location>

# Restrict access to the admin pages...
<Location /admin>
  Order allow,deny
  Allow all
</Location>

Here we will download the Dymo CUPS drivers and compile them on the Raspberry Pi. Again, this step will take a little bit of time to complete.

cd ~
wget http://download.dymo.com/Software/Linux/dymo-cups-drivers-1.4.0.tar.gz
tar -xzvf dymo-cups-drivers-1.4.0.tar.gz
cd dymo-cups-drivers-1.4.0.5/
sudo ./configure
sudo make
sudo make install

After these changes have been made to /etc/cups/cupsd.conf, cycling the service is required and to ensure the new Dymo driver is found.

sudo service cups restart

Proceed over to the CUPS web interface to setup the Dymo printer at https://host:631/admin

Once you get to the login page and you are authenticated, proceed to add a printer by selecting Add Printer under the Administration tab.
Dymo LabelWriter 330 CUPS Setup - Step Start

Select your DYMO LabelWriter printer and press Continue.
Dymo LabelWriter 330 CUPS Setup - Step 1

Specify a Name for your printer, I’de recommend keeping it short in name.
Dymo LabelWriter 330 CUPS Setup - Step 2

Select the Model of the printer or choose the PPD file from compiling if it was not automatically detected. Press Add Printer!
Dymo LabelWriter 330 CUPS Setup - Step 3

After the printer is added, ensure the proper defaults are set for the printer such as Print Quality, Print Density and Media Size by going to Set Default Options under the Administration tab.
Dymo LabelWriter 330 CUPS Setup - Step 4

Finally, ensure the DYMO LabelWriter is set to the System Default printer which is located in the dropdown menu of the printer under the Printers tab.
Dymo LabelWriter 330 CUPS Setup - Step 6

lighttpd Setup

Add the pi user to the www-data group so that the default lightttp path can be written to by the pi user:

sudo chown www-data:www-data /var/www
sudo chmod 775 /var/www
sudo usermod -a -G www-data pi

To enable the server to handle php scripts the fastcgi-php module should be enabled and lighttpd reloaded:

sudo lighty-enable-mod fastcgi-php
sudo service lighttpd force-reload

wlan0 Setup

During this section, we will give wlan0 a static ip address and ensure the interface is brought up at boot. Note this section and the dnsmasq sections are almost verbatim taken from, http://ariandy1.wordpress.com/2013/04/07/setting-up-wifi-access-point-with-edimax-ew-7811un-on-raspberry-pi/. These sections are placed here for linear order of operations and recap the article link above.

Edit /etc/network/interfaces, remove anything related to wlan0 then add this lines:

iface wlan0 inet static
address 10.0.0.1
network 10.0.0.0
netmask 255.255.255.0
broadcast 10.0.0.255

To make sure, add ifup wlan0 inside /etc/rc.local file before exit 0 so it will look like this:

...
ifup wlan0
exit 0

dnsmasq Setup

Stop dnsmasq before working on it:

sudo service dnsmasq stop
sudo mv /etc/dnsmasq.conf /etc/dnsmasq.conf.orig
sudo touch /etc/dnsmasq.conf

Edit the newly create /etc/dnsmasq.conf and make it reflect the following contents:

interface=wlan0
expand-hosts
domain=local
dhcp-range=10.0.0.10,10.0.0.50,24h
dhcp-option=6,10.0.0.1

Add the following to the /etc/hosts file to reflect the hostname of the Raspberry Pi for easy typing in the tablet web browser:

10.0.0.1        checkin checkin.local

hostapd Setup

The EW-7811Un features a Realtek RTL8192C depending on the revision. Since it uses a Realtek chipset you must use the Realtek’s version of hostapd. The majority of this following section was taken from, http://jenssegers.be/blog/43/Realtek-RTL8188-based-access-point-on-Raspberry-Pi. Proper credits goes to the author.

cd ~
wget https://github.com/jenssegers/RTL8188-hostapd/archive/v1.1.tar.gz
tar -zxvf v1.1.tar.gz
cd RTL8188-hostapd-1.1/hostapd
sudo make
sudo make install

Create the following file /etc/hostapd/hostapd.conf and tailor the following setting:

# Basic configuration
interface=wlan0
ssid=VMUGCheckin
channel=3

# WPA and WPA2 configuration
macaddr_acl=0
auth_algs=1
ignore_broadcast_ssid=0
wpa=3
wpa_passphrase=YourPassPhrase
wpa_key_mgmt=WPA-PSK
wpa_pairwise=TKIP
rsn_pairwise=CCMP

# Hardware configuration
driver=rtl871xdrv
ieee80211n=1
hw_mode=g
device_name=RTL8192CU
manufacturer=Realtek

Edit the following file to point to the newly created conf file above. Open up /etc/default/hostapd and make it reflect the following:

DAEMON_CONF="/etc/hostapd/hostapd.conf"

Enable the access point by restarting the hostapd service after all the changes:

sudo service hostapd restart

PHP Application

After everything, download the PHP binary bits included and extract everything to /var/www and follow the directions included in the 7z archive or download the complete Raspberry Pi below which includes all the work above including the application.

Download and extract the 7z archive:

wget <oldurl>
7z x -y -o/var/www VMUGWirelessCheckin_v103.7z

Ensure correct permissions:

sudo chown www-data:www-data /var/www/ -R
sudo find /var/www/ -type d -exec chmod 755 {} ;
sudo find /var/www/ -type f -exec chmod 644 {} ;
sudo chmod 664 /var/www/Checkin.*

For good house keeping delete the old landing page:

sudo rm -f /var/www/index.lighttpd.html

Be sure to set 664 Unix permissions on the *.db (SQLite database) and modify variables as needed in settings.php. Replace the background.png and logo.png under the images directory.

You should now be complete!

Application links are http://<host>/admin.php for the Administrative Functions page and http://<host>/reprint.php for the Reprint Page.

Summary

This give you everything you need to setup the hardware portion of this project. The software portion is simple, extract and deploy. Please post any questions or comments below.

Download / Git Repository

The Git repository for this project can be found here at GitHub, https://github.com/tkrn/pivmugc for the latest version.

Tagged , , , , , , , , , , , , , ,

YaBB SE 1.5.5 MySQL Unknown Column Fix

For retro purposes, I wanted to make an old instance of YaBB SE 1.5.4 a read-only version to look back with friends to get some kicks and laughs. I first upgraded from YaBB SE 1.5.4 to YaBB SE 1.5.5c. This was a small project try bring this up on a modern operating system, primarily around the MySQL versioning. First I had to stand up a temporary virtual machine running CentOS 4.5 Linux. Followed by that, I was able to successfully to restore the backups that I had to the virtual machine as it was in the early 2000’s. An issue occurred when trying to run MySQL 4.x queries on a MySQL 5.x version. PHP did not pose the problem even though all functions within YaBB SE were built for the 4.x version of PHP. Below are my findings to get the MySQL SQL 4.x queries to work properly on MySQL 5.x instance. Please refer to the line number for the corresponding files.

Error:
Unknown column ‘m.ID_MEMBER’ in ‘on clause’
File: /home/www/Sources/MessageIndex.php
Line: 269

Original Code:

$result = mysql_query("
			SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
			FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
				LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
				LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
				LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
				LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
			WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
				AND m.ID_MSG=t.ID_LAST_MSG
				AND m2.ID_MSG=t.ID_FIRST_MSG
			ORDER BY $stickyOrder m.posterTime DESC") or database_error(__FILE__, __LINE__);

Modified Code:

$result = mysql_query("
			SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead
			FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
				LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
				LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
				LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
				LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
			WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
				AND m.ID_MSG=t.ID_LAST_MSG
				AND m2.ID_MSG=t.ID_FIRST_MSG
			ORDER BY $stickyOrder m.posterTime DESC") or database_error(__FILE__, __LINE__);

Error:
Unknown column ‘b.ID_LAST_TOPIC’ in ‘on clause’
File: /home/www/Sources/Recent.php
Line: 45

Original Code:

$request = mysql_query("
	SELECT m.posterTime, m2.subject, m.ID_TOPIC, t.ID_BOARD, m.posterName, t.numReplies, t.ID_FIRST_MSG
	FROM {$db_prefix}boards AS b, {$db_prefix}categories AS c
		LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC=b.ID_LAST_TOPIC)
		LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG=t.ID_LAST_MSG)
		LEFT JOIN {$db_prefix}messages AS m2 ON (m2.ID_MSG=t.ID_FIRST_MSG)
	WHERE c.ID_CAT=b.ID_CAT
		AND (FIND_IN_SET('$settings[7]', c.memberGroups) != 0 OR c.memberGroups='' OR '$settings[7]' LIKE 'Administrator' OR '$settings[7]' LIKE 'Global Moderator')
	ORDER BY m.posterTime DESC
	LIMIT 1;") or database_error(__FILE__, __LINE__);

Modified Code:

$request = mysql_query("
	SELECT m.posterTime, m2.subject, m.ID_TOPIC, t.ID_BOARD, m.posterName, t.numReplies, t.ID_FIRST_MSG
	FROM ({$db_prefix}boards AS b, {$db_prefix}categories AS c)
		LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC=b.ID_LAST_TOPIC)
		LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG=t.ID_LAST_MSG)
		LEFT JOIN {$db_prefix}messages AS m2 ON (m2.ID_MSG=t.ID_FIRST_MSG)
	WHERE c.ID_CAT=b.ID_CAT
		AND (FIND_IN_SET('$settings[7]', c.memberGroups) != 0 OR c.memberGroups='' OR '$settings[7]' LIKE 'Administrator' OR '$settings[7]' LIKE 'Global Moderator')
	ORDER BY m.posterTime DESC
	LIMIT 1;") or database_error(__FILE__, __LINE__);
Tagged , , ,