The Tivoli Monitoring agent requires the Error Log Path and the SQL Server Home Path, for installation

My first problem was to derive the t-sql to gain the information for one sql server instance.
The second problem was to use a scripting framework to iterate through every sql server instance on the database server inventory

The t-sql to derive the error log file was straightforward

select ServerProperty('ErrorLogFileName')

The code for the SQL Server path is an "undocumented" method. "Undocumented" means Microsoft do not support it. "Undocumented" also means you have to figure out to how to use it without BOL.

The extended stored procedure “xp_instance_regread”, in the master database, derives values from the registry.

The code below returns the SQL Server home for a given SQL Server instance

declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot
OUTPUT
print @SmoRoot

The second problem involves getting this information from multiple sql server instances on the same network. Powershell is great for this purpose. The results were useful.

The script reads the instances from "C:\MyInstancesPROD_2005.txt" and writes them out to "C:\projects\test.txt"

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @SmoRoot OUTPUT
SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('ProductVersion') AS Version, 'itmsql' AS LOGIN,SERVERPROPERTY('ErrorLogFileName') AS ErrorLog,@SmoRoot AS SQLPath
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize  
    $dt | out-File "C:\projects\test.txt" -append -encoding ASCII
}

Republished from http://www.sqlserver-dba.com.