PowerShell: ODBC Driver Lookup

By Jason Bolonski

The Ask

I received a request to assist with “…identify which of our sql servers have a specfic ODBC driver and if that server has the newest version…”

The Timing

I’m going to try to get this working and tested script back to the requestor in 3 hours.

Research

A quick Google search for “PowerShell list ODBC drivers” returned Get-OdbcDriver commandlet that is part of the Wdac module.

Get-OdbcDriver
   [[-Name] <String>]
   [-Platform <String>]
   [-CimSession <CimSession[]>]
   [-ThrottleLimit <Int32>]
   [-AsJob]
   [<CommonParameters>]

Assess Get-OdbcDriver for Viability

I quick run of the command on my computer returned results.

PS C:\> Get-OdbcDriver

Name      : SQL Server
Platform  : 64-bit
Attribute : {APILevel, FileUsage, Driver, ConnectFunctions...}

Name      : Microsoft Access Driver (*.mdb, *.accdb)
Platform  : 64-bit
Attribute : {Driver, APILevel, FileExtns, FileUsage...}

Name      : Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
Platform  : 64-bit
Attribute : {Driver, APILevel, FileExtns, FileUsage...}

Name      : Microsoft Access Text Driver (*.txt, *.csv)
Platform  : 64-bit
Attribute : {Driver, APILevel, FileExtns, FileUsage...}

(results shortened)

I then decided to run a more targeted approach with some parameters.

PS C:\> Get-OdbcDriver -Name "SQL Server*" -Platform "64-bit"

Name      : SQL Server
Platform  : 64-bit
Attribute : {APILevel, FileUsage, Driver, ConnectFunctions...}

Name      : SQL Server Native Client 11.0
Platform  : 64-bit
Attribute : {Driver, APILevel, FileUsage, Setup...}

This looks like I am heading in the right direction. But I don’t want to have to log into each system manually and run this command. I need to be able to run this from a single location.

Run Command on Remote System

I have been successful in the past using Invoke-Command -ComputerName *RemoteHostName* to run commands on remote machines. Note: better solution further down

I ran the following test to see if this would run without error.

PS C:\> Invoke-Command -ComputerName SERVERHOSTNAME -ScriptBlock { Get-OdbcDriver -Name "Starburst*" }


Name           : Starburst Presto ODBC Driver
Platform       : 32-bit
Attribute      : {Description, Driver, Setup}
PSComputerName : SERVERHOSTNAME

Name           : Starburst ODBC Driver
Platform       : 32-bit
Attribute      : {Description, Driver, Setup}
PSComputerName : SERVERHOSTNAME

Name           : Starburst Presto ODBC Driver
Platform       : 64-bit
Attribute      : {Description, Driver, Setup}
PSComputerName : SERVERHOSTNAME

Name           : Starburst ODBC Driver
Platform       : 64-bit
Attribute      : {Description, Driver, Setup}
PSComputerName : SERVERHOSTNAME

But Wait! I noticed that the Get-OdbcDriver has -CimSession parameter. I should be able to skip the Invoke-Command and just use that parameter. I’m going to try that.

PS C:\> Get-OdbcDriver -Name "SQL Server*" -Platform "64-bit" -CimSession SERVERHOSTNAME

Name           : SQL Server
Platform       : 64-bit
Attribute      : {APILevel, FileUsage, Driver, ConnectFunctions...}
PSComputerName : SERVERHOSTNAME

Name           : SQL Server Native Client 11.0
Platform       : 64-bit
Attribute      : {Driver, APILevel, FileUsage, Setup...}
PSComputerName : SERVERHOSTNAME

Name           : SQL Server Native Client RDA 11.0
Platform       : 64-bit
Attribute      : {Driver, APILevel, FileUsage, Setup...}
PSComputerName : SERVERHOSTNAME

Well, Look at that! I can drop the Invoke command and just go direct.

Research Results

The remote invoke worked and I was able to return the expected results. Using the -CimSession parameter on the Get-OdbcDriver simplifies everything for this use case.

Building the Script

I need to pull the results into an array and then display the results.

The Attribute is an array objects. I needed to expand that out to be able to pull specific attributes into my result.

Name           : SQL Server Native Client RDA 11.0
Platform       : 64-bit
Attribute      : {Driver, APILevel, FileUsage, Setup...}
PSComputerName : SERVERHOSTNAME

The easiest what to get the properties that I want from Attribute is using Calculated Properties.

I will retrived the drivers and expand the attributes propertie to get the ODBC Version.

$InstalledDrivers = Get-OdbcDriver -Name "SQL Server*" -Platform "64-bit" -CimSession SERVERHOSTNAME

$InstalledDrivers |  Select -Property Name,Platform,PSComputerName,@{Name='Ver';Expression={$_.Attribute.DriverODBCVer}}

Small Success

This was a success. But, it wasn’t the driver I was attempting to get.

It turns out that not all drivers will respond with the same properties.

Name                              Platform PSComputerName  Ver
----                              -------- --------------  ---
SQL Server                        64-bit   SERVERHOSTNAME 03.50
SQL Server Native Client 11.0     64-bit   SERVERHOSTNAME 03.80
SQL Server Native Client RDA 11.0 64-bit   SERVERHOSTNAME 03.80

What about the driver I was asked to look for on the systems?

When I run the same command the DriverODBCVer is not one of the properties. The only attributes available are Description, Driver, Setup.

$InstalledDrivers = Get-OdbcDriver -Name "Starburst*" -Platform "All" -CimSession SERVERHOSTNAME

$InstalledDrivers |  Select -Property Name,Platform,PSComputerName,@{Name='Ver';Expression={$_.Attribute.DriverODBCVer}}

Name                         Platform PSComputerName  Ver
----                         -------- --------------  ---
Starburst Presto ODBC Driver 32-bit   SERVERHOSTNAME
Starburst ODBC Driver        32-bit   SERVERHOSTNAME
Starburst Presto ODBC Driver 64-bit   SERVERHOSTNAME
Starburst ODBC Driver        64-bit   SERVERHOSTNAME

For this request the path to the driver returned in the Driver attribute should work.

The newer version of the driver doesn’t have the word Presto in it.

Reworked PowerShell Script

$InstalledDrivers = Get-OdbcDriver -Name "Starburst*" -Platform "All" -CimSession SERVERHOSTNAME
$InstalledDrivers |  Select -Property Name,Platform,PSComputerName,@{Name='Ver';Expression={$_.Attribute.DriverODBCVer}},@{Name='Descr';Expression={$_.Attribute.Description}},@{Name='Driver';Expression={$_.Attribute.Driver}}

Reworked Results

Name           : Starburst Presto ODBC Driver
Platform       : 32-bit
PSComputerName : PANE3SSISQL19D1
Ver            :
Descr          : Starburst Presto ODBC Driver
Driver         : C:\Program Files (x86)\Starburst Presto ODBC Driver\lib\PrestoODBC_sb32.dll

(results shortened)

Driver Comparisons

| Old Driver          | New Driver             |
| ------------------- | ---------------------- |
| PrestoODBC_sb32.dll | StarburstODBC_sb32.dll |
| PrestoODBC_sb64.dll | StarburstODBC_sb64.dll |

What is Next?

Currently I can:

  • Query the ODBC drivers on the remote system
  • Retrieve properties that identify the drivers
  • Determine if the new version is installed

My Next steps:

  • Load a list of host names from a text file.
  • Query each host
  • Flag hosts that only have the old driver
  • Write a results report for the admin to work off

Create Reusable Script

I have only been working directly with CLI commands Up until now. The tests are complete and I can start putting the script together.

Create the File

I am going to call the file Get-ODBCDriver-Info.ps1.

I like to paste in my hardcoded CLI tests and give the file a quick run to make sure everything is happy. Of course it will work. What could go wrong? It’s just moving the commands into a file…. (After 40 years of writing code my paranoia has save me a lot of headaches) Call me a Code Conspiracy Theorist, but I think all code is out to get me.

Let’s run the PowerShell Script and make sure I get results. nice!

.\Get-ODBCDriver-Info.ps1

Name           : Starburst Presto ODBC Driver
Platform       : 32-bit
PSComputerName : SERVERHOSTNAME
Ver            :
Descr          : Starburst Presto ODBC Driver
Driver         : C:\Program Files (x86)\Starburst Presto ODBC Driver\lib\PrestoODBC_sb32.dll

Create a Hosts file for input

Now I should create a sample input file to work with. Let’s just call it host_list.txt. We will put that in a subdirectory called datafiles since I like to keep my data separate from my logic.

Load Hosts and Check

We can use the Get-Content to read the host_list.txt file.

$inputHosts = @(Get-Content ".\datafiles\host_list.txt")

This will give us a string array of lines in the host_list.txt file.

Iterate the hosts

The next step would be to run the Get-OdbcDriver command against each of the hosts in our array.

I will take code used to get the ODBC Driver information and put it into a function.

function Get-OdbcInfo {    
    param( $hostname )
    Write-Host "Assessing $hostname"
    try{
        $InstalledDrivers = Get-OdbcDriver -Name "Starburst*" -Platform "All" -CimSession $hostname -ErrorAction Stop
        $InstalledDrivers |  Select-Object -Property Name,Platform,PSComputerName,@{Name='Ver';Expression={$_.Attribute.DriverODBCVer}},@{Name='Descr';Expression={$_.Attribute.Description}},@{Name='Driver';Expression={$_.Attribute.Driver}}
    } catch {
        Write-Host "Error Connecting to $hostname"
    }
}

I added some error checking because I always assume that the incoming data is going to be bad. I also want to make sure I note the exception in the output so that the administrator knows that they need to manually check the host.

The next step is to feed the hostnames into the function.

foreach( $hostname in $inputHosts){
    Get-OdbcInfo $hostname
}

Running this script should now give us some output.

Script So Far


$inputHosts = @(Get-Content ".\datafiles\host_list.txt")

function Get-OdbcInfo {    
    param( $hostname )
    Write-Host "Assessing $hostname"
    try{
        $InstalledDrivers = Get-OdbcDriver -Name "Starburst*" -Platform "All" -CimSession $hostname -ErrorAction Stop
        $InstalledDrivers |  Select-Object -Property Name,Platform,PSComputerName,@{Name='Ver';Expression={$_.Attribute.DriverODBCVer}},@{Name='Descr';Expression={$_.Attribute.Description}},@{Name='Driver';Expression={$_.Attribute.Driver}}
    } catch {
        Write-Host "Error Connecting to $hostname"
    }
}

foreach( $hostname in $inputHosts){
    Get-OdbcInfo $hostname
}

Running this script runs through the list of hosts and checks each one for the ODBC Drivers that we are looking to assess.

Oh No. Here we Go.

There comes a time when your output makes you realized that you missed something. Now is that time for me.

I started testing the output of the results to CSV file using the Export-CSV module.

When querying one of the servers the results coming back were different than I have seen before. One of the hosts was returning more values than I was expecting. Object Array Result

I need to rethink my function because it should expect an array as a result and I was treating the results as a single record.

However, this was pretty straightforward in PowerShell because += has a lot of magic infused in it.

Oh Yeah. The fix

The Current working file is doing the following:

  • Read the host_list.txt file
  • Lookup the ODBC drivers for the server
  • Export the results to a CSV file

Final Script

Get-ODBCDriver-Info.ps1

$inputHosts = @(Get-Content ".\datafiles\host_list.txt")

function Get-ErrorRecord{
    param ( $hostname, $note)
    
    return [PSCustomObject]@{
        Host=$hostname; Name=''; Platform=''; HostName=''; Description=''; Driver=''; Note=$note
    }
}

function Get-OdbcInfo {    
    param( $hostname )
    Write-Host "Assessing $hostname"
    $result = @()

    try{
        $InstalledDrivers = Get-OdbcDriver -Name "Starburst*" -Platform "All" -CimSession $hostname -ErrorAction Stop
        $DriverResults = ($InstalledDrivers |  Select-Object -Property @{Name='Host';Expression={$_.PSComputerName}},Name,Platform,@{Name='Descr';Expression={$_.Attribute.Description}},@{Name='Driver';Expression={$_.Attribute.Driver}},@{Name='Note';Expression={""}} )

        $result += $DriverResults

        if( $DriverResults.Count -eq 0){
            $result += Get-ErrorRecord $hostname "No Matching Drivers Found"
        }        
    } catch {
        Write-Host "Error Connecting to $hostname"
        $result += Get-ErrorRecord $hostname "Error Connecting"
    } 

    return $result
}

$results = @()
foreach( $hostname in $inputHosts){
    $results += Get-OdbcInfo $hostname
}

$results | Export-CSV -Path "output.csv" -encoding "UTF8" -NoTypeInformation

Summary

This took me about 4 hours to put together because I spent so much time trying to write it up here. My actual time coding was probably less than an hour which includes testing.

I am going to run this by the requestor to make sure it is workable. I will most likely add another function to identify hosts that need the upgrade. Instead of making them go through the spreadsheet manually.