ODBC connections inventory

Post date: Nov 30, 2010 3:48:11 PM

Ever wondered what ODBC connections are defined on all servers in your farm?

This script leads you in the right direction, don't copy one on one because you could be missing something.

Also the results of this script are presented in Excel.

$computers = Get-QADComputer | sort | where {$_.operatingSystem -match "2003" -AND $_.description -notmatch "disabled" -and $_.name -notmatch "CTX" -and $_.name -notmatch "SEQ02" -and $_.name -notmatch "IBV_S"}

#Create a new Excel object using COM

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows

$intRow = 1

#Read all hosts

foreach ($computer in $computers){

$Srv = $computer.name

#Create column headers

$Sheet.Cells.Item($intRow,1) = "Hostname:"

$Sheet.Cells.Item($intRow,2) = $srv

$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$Sheet.Cells.Item($intRow,2).Font.Bold = $True

$intRow++

$Sheet.Cells.Item($intRow,1) = "ODBC NAME"

$Sheet.Cells.Item($intRow,2) = "Database"

$Sheet.Cells.Item($intRow,3) = "Driver"

$Sheet.Cells.Item($intRow,4) = "LastUser"

$Sheet.Cells.Item($intRow,5) = "Server"

#Format the column headers

for ($col = 1; $col –le 5; $col++)

{

$Sheet.Cells.Item($intRow,$col).Font.Bold = $True

$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34

}

$intRow++

$pingresult = ping $srv

if ($pingresult -match "reply from"){

$key = "SOFTWARE\ODBC\ODBC.ini\ODBC Data Sources"

$type = [Microsoft.Win32.RegistryHive]::LocalMachine

$regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $Srv)

$regKey = $regKey.OpenSubKey($key)

if ($regkey -ne $null){

Foreach($sub in $regKey.GetSubKeyNames()){$sub}

Foreach($val in $regkey.GetValueNames()){

$Sheet.Cells.Item($intRow,1) = $val

$odbckey = "SOFTWARE\ODBC\ODBC.ini\$val"

$valuename1 = "Database"

$valuename2 = "Driver"

$valuename3 = "LastUser"

$valuename4 = "Server"

$odbcreg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $srv)

$odbcregkey = $odbcreg.opensubkey($odbckey)

$database = $odbcregkey.getvalue($valuename1)

$driver = $odbcregkey.getvalue($valuename2)

$lastuser = $odbcregkey.getvalue($valuename3)

$server = $odbcregkey.getvalue($valuename4)

$Sheet.Cells.Item($intRow,2) = $database

$Sheet.Cells.Item($intRow,3) = $driver

$Sheet.Cells.Item($intRow,4) = $lastuser

$Sheet.Cells.Item($intRow,5) = $server

$intRow++} }

}

else {$Sheet.Cells.Item($intRow,1) = "No ODBC connections on $srv"

$intRow++}

}

$Sheet.UsedRange.EntireColumn.AutoFit()