Welcome‎ > ‎Scripting‎ > ‎

ODBC connections inventory

posted Nov 30, 2010, 7:48 AM by Jurien Bosman
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()


Comments