One of the very first powershell scripts I created. Checks a list of servers and gets the details of installed SQL Server instances and puts the data in excel. Uses Get-RemoteRegistryKeyProperty by Lee Holmes. It uses a csv file with the following headers "Monitor", "PhysicalServer", "POrNP".

Get-SQLServerVersionDetails.ps1

##############################################################################
##						BEGIN-HELPER FUNCTIONS								##
##############################################################################

##############################################################################
## Get-RemoteRegistryKeyProperty
## Get the value of a remote registry key property
##
## Author: Lee Holmes
## Book: Windows Powershell Cookbook
## ie:
## $registryPath =
##       "HKLM:\software\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell"
## Get-RemoteRegistryKeyProperty LEE-DESK $registryPath "ExecutionPolicy"
############################################################################## 
function Get-RemoteRegistryKeyProperty
{
	param(
		$computer = $(throw "Please specify a computer name."),
		$path = $(throw "Please specify a registry path"),
		$property = "*"
	) 
	## Validate and extract out the registry key
	if($path -match "^HKLM:\\(.*)")
	{
		$baseKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $computer)
	}
	elseif($path -match "^HKCU:\\(.*)")
	{
		$baseKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("CurrentUser", $computer)
	}
	else
	{
		Write-Error ("Please specify a fully-qualified registry path " +
			"(i.e.: HKLM:\Software) of the registry key to open.")
		return
	} 

	## Open the key	
	$key = $baseKey.OpenSubKey($matches[1])
	$returnObject = New-Object PsObject 

	## Go through each of the properties in the key
	foreach($keyProperty in $key.GetValueNames())
	{
		## If the property matches the search term, add it as a
		## property to the output
		if($keyProperty -like $property)
		{
			$returnObject |
				Add-Member NoteProperty $keyProperty $key.GetValue($keyProperty)
		}
	} 

	## Return the resulting object
	$returnObject 
	
	## Close the key and base keys
	$key.Close()
	$baseKey.Close()
}
##############################################################################
##						END-HELPER FUNCTIONS								##
##############################################################################

##############################################################################
##					   INSTANSIATE EXCEL OBJECTS							##
##############################################################################
#Create a new Excel object as type ComObject
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$WorkBook = $Excel.Workbooks.Add()
$WorkBook.WorkSheets.Item(1).Name = "VersionDetails" 
$WorkBook.WorkSheets.Item(2).Delete()
$WorkBook.WorkSheets.Item(3).Delete() 
$VersionDetails = $WorkBook.WorkSheets.Item("VersionDetails") 

#Row counters for both sheets.
$rowCntrVerSheet = 2

###########################################################
# 		BUILD HEADERS FOR THE EXCEL WORKSHEET 			  #
###########################################################
$VersionDetails.Cells.Item($rowCntrVerSheet, 1) = "PHYSICAL_SERVER_NAME"
$VersionDetails.Cells.Item($rowCntrVerSheet, 2) = "ENVIRONMENT"
$VersionDetails.Cells.Item($rowCntrVerSheet, 3) = "INSTANCE_NAME"
$VersionDetails.Cells.Item($rowCntrVerSheet, 4) = "SQL_SERVER_EXE_VERSION"
for ($col = 1; $col –le 4; $col++) { 
	$VersionDetails.Cells.Item($rowCntrVerSheet,$col).Font.Bold = $True 
	$VersionDetails.Cells.Item($rowCntrVerSheet,$col).Interior.ColorIndex = 48 
	$VersionDetails.Cells.Item($rowCntrVerSheet,$col).Font.ColorIndex = 34 
} 

################################################################################
#Read thru the contents of the SQL_Server_List.csv file, the structure of the  #
#file is as follows "Monitor", "PhysicalServer", "POrNP" are the headers   	   #
#in the SQL_Servers_List.txt file. Monitor is 'Y' or 'N', Server is the        #
#[Virtual Name] or [Physical Name] depending on what kind of instanace you are #
#connecting to, Instance is the instance name and null if default instance,    #
#POrNP is NP for non-prod and P for Prod				       				   #
################################################################################
$servers = Import-Csv "Server_List.csv"

foreach ($entry in $servers)
{
    $toMonitor = $entry.Monitor
    $sName = $entry.PhysicalServer
    $PorNP = $entry.POrNP
	$file2Search = "sqlservr.exe"
	
	$rowCntrVerSheet ++
	
    if(($toMonitor -eq "Y") -or ($toMonitor -eq "y")){
		
		$VersionDetails.Cells.Item($rowCntrVerSheet, 1) = $sName
		
		if ($PorNP -eq "P"){
			$serverType = "Prod"
			$fgColor = 35
		}else{
			$serverType = "Non-Prod"
			$fgColor = 0
		}
		$VersionDetails.Cells.Item($rowCntrVerSheet, 2) = $serverType
		$VersionDetails.Cells.Item($rowCntrVerSheet, 2).Interior.ColorIndex = $fgColor
		
		
		#Get instance names from the physical server.
		$msSqlRegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"
		$sqlInstanceList = Get-RemoteRegistryKeyProperty $sName $msSqlRegKey
		foreach ($instance in $sqlInstanceList.InstalledInstances){
			$VersionDetails.Cells.Item($rowCntrVerSheet, 3) = $instance
			$sqlInstRegKey = $msSqlRegKey + $instance + "\Setup\"
			$sqlPathInfo = Get-RemoteRegistryKeyProperty $sName $sqlInstRegKey
			$sqlPath = $sqlPathInfo.SQLPath -replace ":", "$"
			$filePath2Search = "\\" + $sName + "\$sqlPath" + "\Binn\" + $file2Search
			$fileVersion = [System.Diagnostics.FileVersionInfo]::GetVersionInfo($filePath2Search).FileVersion
			$VersionDetails.Cells.Item($rowCntrVerSheet, 4) = $fileVersion
			$rowCntrVerSheet++
		}
		
    }elseif(($toMonitor -eq "N") -or ($toMonitor -eq "n") -or ($toMonitor -eq "Null")){
		continue
    }
}
$VersionDetails.UsedRange.EntireColumn.AutoFit()
cls

##############################################################################
##					   	SAVE THE EXCEL OBJECT								##
##############################################################################
#Save file and close Excel.  
$xlExcel8 = 56 
$timeStamp = Get-Date -Format "yyyyMMdd_HH_mm"
$fileName = "\ServerVersionInfo_" + $timeStamp + ".xls"
$filePath = Join-Path (Get-Location) $fileName
#echo $filePath
$Workbook.SaveAs($filePath, $xlExcel8)

##############################################################################
##					   DESTROY THE EXCEL OBJECT								##
##############################################################################
$Excel.Quit 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
cls
[GC]::Collect()