I spent a couple of hours this morning coming up with a way to see which of a large number of windows hosts have Oracle client installed. In particular, I wanted the path to the Oracle home for each machine. We have some 3rd party agent-based tools that gather data about windows machines, but licensing prohibits installing them everywhere.

Eventually I decided to create a powershell script that iterates through a list of windows hosts and outputs the value of all ORACLE_HOME entries in the registry plus the main PATH variable. A number of machines are likely to have more than one Oracle client. Or they may have multiple values for ORACLE_HOME even though some have been uninstalled. Getting the PATH value will allow me to see which one was installed most recently, since the most recently installed client should be foremost in the PATH.

I’ve posted the powershell script below. It requires an input file and creates an output file that can be opened in Excel. It illustrates using FOREACH loops, invoking the REG QUERY command against remote machines, using IF logic, and reading and writing to a file and some grep style filtering.

# Written with powershell 2.0. Not sure if it works with 1.0.

# File containing list of hosts.
$host_inputfile = "host_list.txt"

# Name of output data file and of log file.
$ofile = "ohome_values.txt"
$olog = "ohome_run.log"

# Initialize counters for hosts and Oracle Homes.
$total_hosts = 0
# Total homes found
$total_homes = 0

# Update log file to say that processing is starting.
echo "Starting new run of get_oracle_client." | out-file $olog -append
$start_time = get-date
echo $start_time | out-file $olog -append

# Start output data file by putting a header row in.
echo "HOSTNAME, KEY_NAME, KEY_TYPE, KEY_VALUE" | out-file $ofile

# Read into a variable the file containing the list of hosts.
$host_list = get-content $host_inputfile

# Iterate through each host in the list.
foreach ($HOST_NAME in $host_list) {

	# Update the user on progress.
	echo "Working on $HOST_NAME..."

	# Run a a command to get a list of all values from the registry for
	# ORACLE_HOME. Several strings need to be excluded, such as
	# ORACLE_HOME_NAME and agent10g.

	$OH_RAW = reg query \\$HOST_NAME\HKLM\Software\Oracle /s | select-string "ORACLE_HOME" 

	$OH = echo $OH_RAW | sort | get-unique | select-string -notmatch "ORACLE_HOME_KEY", "ORACLE_HOME_NAME", "agent10g"

	# Write Oracle Home values to file. Include a column for the host name.
	# Iterate through each line returned from the remote machine.
	foreach ($line in $OH) {

		# Initialize output var
	  $out_var = "$HOST_NAME"

		$split_line = -split $line

		foreach ($word in $split_line) {
			$out_var="$out_var,$word"
		}

		# If there was nothing to be processed, then don't write to file.
		if ($out_var -eq "$HOST_NAME,") {
			break
		}

		# Increment Oracle Homes counter variable and write values to file.
		$total_homes = $total_homes + 1
		echo $out_var | out-file $ofile -append

	}

	# Key for getting PATH:
	# HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

	$path_var = reg query "\\$HOST_NAME\HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Environment"
	$path_var = echo $path_var | select-string "Path"
	$path_var = echo $path_var | select-string -notmatch "PATHEXT", "TRACE_FORMAT_SEARCH_PATH", "PSModulePath", "Os2LibPath"

	# Write path value to file. Include a column for the host name.
	foreach ($line in $path_var) {

		# Initialize output var
	  $out_var = "$HOST_NAME"

		$split_line = -split $line

		foreach ($word in $split_line) {
			$out_var="$out_var,$word"
		}

		# If there was nothing to be processed, then don't write to file.
		if ($out_var -eq "$HOST_NAME,") {
			break
		}		

		# Write values to file.
		echo $out_var | out-file $ofile -append
	}

	# Increment hosts counter variable.
	$total_hosts = $total_hosts + 1

}

$end_time = get-date

# Update screen.
echo "Start time was: $start_time."
echo "End time was: $end_time."
echo "$total_hosts hosts processed."
echo "$total_homes Oracle Homes found." 

# Append to log file.
echo "Ending run of get_oracle_client." | out-file $olog -append
echo "$total_hosts hosts processed." | out-file $olog -append
echo "$total_homes Oracle Homes found." | out-file $olog -append
echo $end_time | out-file $olog -append
Advertisements