Archive for September, 2010

Determine PC SAS Bundles

Here is a PoSh script that I wrote to determine what bundle of PC SAS is installed.  If you aren’t familiar, there are a lot of components for PC SAS and it’s expensive.  They are installed in “Bundles” and you are billed based on what bundle is installed.  The more components in the bundle, the higher the license cost. 

Unfortunately, when SAS is installed, there is no method for determining which bundle is installed because the SAS installer makes no note of what bundle or what components are installed.  I had to come up with a method for determining what bundle each user had on his/her computer.  The script below is a result of installing every bundle we had and analyzing what components where in each bundle…and quite a bit of swearing.

As always, feel free to use this code, but please give credit.

#############################################################################
##																			#
##	PC SAS Bundle Finder													#
##																			#
##		Written by Jim Melton												#
##
##																			#
##	Reads computer names with PC SAS installed from a spreadsheet created	
##	by SMS or some other means and determines what version of PS SAS is		#
##  installed.  Also determines what bundle is installed, as well as 		
##	whether or not the Graph and Enterprise Guide components are installed	
##																			#
##	Known Issues:															#
##		-If PC SAS installed incorrectly, the bundle cannot be determined.	#
##		-If WMI is broken on the computer, the logged on user will not be	#
##			determined.														#
##		-When Excel quits at the end, the Excel process has a tendency		#
##			to remain running.  You may need to kill these at some point.	#
##																			#
#############################################################################
 
$ErrorActionPreference = "Continue"
 
##
##  Put the path to your text,csv, or xls here
##
$computers = Get-Content "<path to csv>\pcsasinstalled.csv"
 
##
##	Create the objects to ping machines and open Excel
##
$ping = New-Object System.Net.NetworkInformation.Ping
$excel = New-Object -ComObject excel.application
 
##
## You can change this to $false if you don't want to see Excel
##
$excel.visible = $true
$worksheets = $excel.workbooks.add()
##
##	Create the spreadsheet headers
##
$sheet = $excel.worksheets.item(1)
$sheet.cells.item(1,1) = "Computer Name"
$sheet.cells.item(1,2) = "Username"
$sheet.cells.item(1,3) = "SAS Version"
$sheet.cells.item(1,4) = "Bundle"
$sheet.cells.item(1,5) = "Graph Installed?"
$sheet.cells.item(1,6) = "Enterprise Guide Installed?"
$introw = 2
$workbook = $sheet.usedrange
$workbook.font.bold = $true
##
##	Bundle finder function
##
Function Bundles {
  ForEach ($computer in $computers){
  ##
  ##	Zero variables
  ##
  $eguide = $false
  [int]$count = 0
  ##
  ##	Set variable for pinging computers
  ##
  $reply = $ping.send($computer)
  ##
  ##	Ping computer and see if it is on the network.  If it is, open the registry in $pathtest
  ##	to see if SAS is installed and if so, what version.  Also open the registry in $basepathtest
  ##	to see if the Enterprise Guide is installed.
  ##
	If ($reply.status -eq "success"){
	$registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$computer)
	$basepathtest = $registry.OpenSubKey("SOFTWARE\SAS Institute Inc.")
	  $pathtest = $registry.OpenSubKey("SOFTWARE\SAS Institute Inc.\The SAS System")
	If ($pathtest.GetSubKeyNames() -contains "9.1"){
	  $sasver = "9"
	  $components = $registry.OpenSubKey("SOFTWARE\SAS Institute Inc.\The SAS System\9.1\Setup\Components")
	}
	ElseIf
	  ($pathtest.GetSubKeyNames() -contains "8"){
	  $sasver = "8"
	  $components = $registry.OpenSubKey("SOFTWARE\SAS Institute Inc.\The SAS System\8\Setup\Components")
	}
	  ##
	  ##	Check to see if the Enterprise Guide is installed and if so, set $eguide to $true
	  ##
	  If ($basepathtest.GetSubKeyNames() -contains "Enterprise Guide"){
		$eguide = $true
		}
	  ##
	  ##	Get SAS component names and assign to $bundle.  Also, do WMI query on $computer
	  ##
	  $bundle = $components.GetValueNames()
	  $user = gwmi Win32_computersystem -ComputerName "$computer"
	##
	##	Start the analysis of each bundle installed.  Count the number of components installed by
	##	incrementing [int]$count.
	ForEach ($component in $bundle){
	  [int]$count = [int]$count + 1
	}
	  ##
	  ##	Do this if SAS 8 is installed.  When the number of components installed is determined,
	  ##	write the computer name, logged on user (from WMI query above), SAS version, whether Graph is
	  ##	installed, and whether the Enterprise Guide is installed.
	  ##
	  If ($sasver -eq "8"){
	 	Switch ($count){
	  	  8 {If ($bundle -contains "fsp"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 9"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
			      $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			}
		  7 {If ($bundle -contains "connect"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 8"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			}
			  ElseIf ($bundle -contains "eis"){
				$sheet.cells.item($introw,1) = $computer
				$sheet.cells.item($introw,2) = $user.username
				$sheet.cells.item($introw,3) = $sasver
				$sheet.cells.item($introw,4) = "Bundle 7"
				  If ($bundle -contains "graph"){
					$sheet.cells.item($introw,5) = "Yes"
					}
					If ($eguide -eq $true){
					  $sheet.cells.item($introw,6) = "Yes"
					}
				}
			}
		  6 {If ($bundle -contains "assist"){
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 6"
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				  }
				  If ($eguide -eq $true){
					$sheet.cells.item($introw,6) = "Yes"
					}
				}
			}
		  5 {If ($bundle -contains "stat"){
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 5"
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			}
			Else {
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 4"
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				 }
				}
			}
			4 {If ($bundle -contains "pcfile"){
				$sheet.cells.item($introw,1) = $computer
				$sheet.cells.item($introw,2) = $user.username
				$sheet.cells.item($introw,3) = $sasver
				$sheet.cells.item($introw,4) = "Bundle 3"
				  If ($bundle -contains "graph"){
					$sheet.cells.item($introw,5) = "Yes"
					}
				  If ($eguide -eq $true){
					$sheet.cells.item($introw,6) = "Yes"
					}
				}
				Else{
				  $sheet.cells.item($introw,1) = $computer
				  $sheet.cells.item($introw,2) = $user.username
				  $sheet.cells.item($introw,3) = $sasver
				  $sheet.cells.item($introw,4) = "Bundle 2"
					If ($bundle -contains "graph"){
					  $sheet.cells.item($introw,5) = "Yes"
					}
					  If ($eguide -eq $true){
						$sheet.cells.item($introw,6) = "Yes"
						}
					}
				}
			3 {
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 1"
			    If ($bundle -contains "graph"){
			  	  $sheet.cells.item($introw,5) = "Yes"
				}
				  If ($eguide -eq $true){
				    $sheet.cells.item($introw,6) = "Yes"
				  }
				}
			##
			##	If the SAS 8 bundle can't be determined, SAS is probably installed incorrectly.
			##	Display the number of components installed and then list them.
			##
			Default {
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "There are $count components installed and they are $bundle ."
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				}
				  If ($eguide -eq $true){
					$sheet.cells.item($introw,6) = "Yes"
					}
				}
		}
	}
	##
	##	If SAS version is not 8, it must be 9 as of 2009.  Analysis is the same as for version 8
	##
	Else {
	##
	##	If Graph is installed, it adds three components to the total.  Subtract
	##	3 components from $count to make the total components correct.
	  If ($bundle -contains "graph"){
		$count = $count - 3
		}
	    Switch ($count){
		  16 {If ($bundle -contains "fsp"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 9"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			}
		  14 {If ($bundle -contains "connect"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 8"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			}
		  13 {If ($bundle -contains "eis"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 7"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			}
		  11 {If ($bundle -contains "assist"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 6"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			}
		  10 {If ($bundle -contains "stat"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 5"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			  }
			Else {
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 4"
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				}
				  If ($eguide -eq $true){
					$sheet.cells.item($introw,6) = "Yes"
	 			  }
			}
		  }
		  8 {If ($bundle -contains "pcfile"){
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 3"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			}
			Else{
			  $sheet.cells.item($introw,1) = $computer
			  $sheet.cells.item($introw,2) = $user.username
			  $sheet.cells.item($introw,3) = $sasver
			  $sheet.cells.item($introw,4) = "Bundle 2"
				If ($bundle -contains "graph"){
				  $sheet.cells.item($introw,5) = "Yes"
				}
				  If ($eguide -eq $true){
					$sheet.cells.item($introw,6) = "Yes"
					}
			}
		  }
		  6 {
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "Bundle 1"
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
		  }
		  Default {
			$sheet.cells.item($introw,1) = $computer
			$sheet.cells.item($introw,2) = $user.username
			$sheet.cells.item($introw,3) = $sasver
			$sheet.cells.item($introw,4) = "There are $count components installed and they are $bundle ."
			  If ($bundle -contains "graph"){
				$sheet.cells.item($introw,5) = "Yes"
				}
				If ($eguide -eq $true){
				  $sheet.cells.item($introw,6) = "Yes"
				}
			}
		  }
	  }
	}
  ##
  ##	If machine doesn't respond to a ping, write the computer name and the no
  ##	response message.
  ##
  Else{
    $sheet.cells.item($introw,1) = $computer
    $sheet.cells.item($introw,4) = "Didn't respond to a ping."
  }
    ##
    ##	Increment the row counter for Excel
    ##
    $introw = $introw + 1
  }
  ##
  ##	Make the Excel columns autofit the data so nothing is cut off, save the
  ##	spreadsheet, and quit Excel.
  ##
  $workbook.entirecolumn.autofit()
  $worksheets.saveas("C:\bundlelist.xls")
  $excel.quit()
}
##
##	Execute the function
##
Bundles