Determine the Oracle Client Release Version - Remotely

August 6, 2010

An ERP mailing list email asked if there was a way to determine which Oracle clients had not been upgraded to the Oracle 11.2.0.1 client - I also recently encountered a request to determine similar information about the Oracle client versions of potential sessions, so I thought it might be fun to develop a solution.  I might be overlooking something, but it does not appear that the Oracle Database, by default, maintains the connected client’s release version.  Is there a solution?  Assume that the following is true:

• The client computers are running Windows 2000 (client or server) or above
• The client computers are members of ActiveDirectory
• WMI (Windows Management Instrumentation) has not been disabled on the client computers
• VBS (wscript or cscript) may be run from a computer that is logged in with domain administrator permissions

First of all, I would prefer not to visit each computer to determine which Oracle client is installed.  I also do not want to type all of the computer names – so we need a way to retrieve a list of the computers that are members of the ActiveDirectory domain.  A partial code fragment might look something like this (replace oracle and com as necessary for your domain):

Dim dbActiveDirectory
Dim comData
Dim snpData
Dim strDomain

Set dbActiveDirectory = CreateObject("ADODB.Connection")
Set comData = CreateObject("ADODB.Command")
Set snpData = CreateObject("ADODB.Recordset")

dbActiveDirectory.Provider = "ADsDSOObject"
dbActiveDirectory.Open "Active Directory Provider"
comData.ActiveConnection = dbActiveDirectory
strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
With comData
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  NAME" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  NAME"

    .CommandText = strSQL          
    .Properties("Page Size") = 1000
    .Properties("Searchscope") = ADS_SCOPE_SUBTREE
End With

Set snpData = comData.Execute
If snpData.State = 1 Then
    Do While Not (snpData.EOF)
        'Do something
        snpData.MoveNext
    Loop
End If

Now that we have a method for retrieving the computer names in the domain (note that the above code might only work for the domain administrators), we need an easy method to determine which Oracle client is installed – or at least which client is listed first in the system path.  Something like this might work:

C:\> tnsping /?

TNS Ping Utility for 32-bit Windows: Version 11.1.0.7.0 - Production on 05-AUG-2010 20:54:43

Copyright (c) 1997, 2008, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

There is the Oracle client release version on the second line of the output, along with the client’s operating system (just ignore the error message).  Now we just need a way to record the output of that command – let’s redirect the output to a text file:

C:\> tnsping /? >C:\OracleClientversion.txt

Next, we need to find a way to tell the client computers on the network to execute the above command.  WMI will help, as long as our network user account is a member of the  Administrators group on the remote computer (or our network user account is a member of the Domain Adminitrators group).  If we are somehow able to create a batch file named C:\OracleClientVersion.bat on the remote client computer, we could execute a command like this in our script:

lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

The next task is to read the resulting text files that are created on each of the client computers, looking for the line that contains “TNS Ping Utility”. Obviously, we need to write the results to another text file.  We end up with something like this:

Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

Do While Not (objFile.AtEndOfStream)
    strLine = objFile.ReadLine
    If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
        Exit Do
    Else
        'Do nothing
    End If
Loop

objFile.Close

One problem remains.  If we attempt to access the WMI objects on a computer that is offline our script will lock up for a period of time.  To work around that problem we should probably try to ping the remote computer first.  The ping routine looks like this:

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

A simple, straight-forward solution, right?  Because the columns in the combined log file are tab delimited, we are able to easily open the file using Microsoft Excel.  Partial output might look something like this:

Obviously, the client computers need to be turned on for the script to work correctly.    COMP19 certainly is running an old version of the Oracle client.

Notes:
* For every computer that responds to a ping, the script pauses for two seconds to allow enough time for the tnsping command on the remote computers to execute. 
* The script execution speed may be improved by modifying the script to remember each computer name that responded to a ping, and remotely launch the tnsping command on those computers.  Once the tnsping command has been started on each computer, make a loop through the list of computers that previously reponded to a ping and collect the OracleClientversion.txt files from those computers.

The full script may be downloaded here: DetOrclClient.vbs (save as DetermineOracleClientReleaseVersion.vbs).  The full script is displayed below (the script download from WordPress seems to fail):

'Revision 1.0 Created by Charles Hooper August 6, 2010

ProbeAllComputers

Sub ProbeAllComputers()
    'For modifying the INI file
    Const ForReading = 1
    Const ForWriting = 2
    Dim objFSO
    Dim objFile
    Dim objFileLog
    Dim objFileBatch
    Dim strLine
    Dim strOracleClientFile
    Dim strOracleClientFileF
    Dim lngResult

    'For querying active directory
    Const ADS_SCOPE_SUBTREE = 2
    Dim dbActiveDirectory
    Dim comData
    Dim snpData
    Dim strSQL
    Dim strDomain
    Dim strCommand

    On Error Resume Next

    strDomain = "DC=oracle,DC=com"            'Equivalent to oracle.com, change as needed
    strOracleClientFile = "C:\OracleClientVersion.txt"  'The location of the output file on the computers
    strCommand = "tnsping /? >C:\OracleClientVersion.txt"

    'Make the file location compatible with a UNC name
    strOracleClientFileF = Replace(strOracleClientFile, ":", "$")
    strOracleClientFileF = "\" & strOracleClientFileF

    Set dbActiveDirectory = CreateObject("ADODB.Connection")
    Set comData = CreateObject("ADODB.Command")
    Set snpData = CreateObject("ADODB.Recordset")

    dbActiveDirectory.Provider = "ADsDSOObject"
    dbActiveDirectory.Open "Active Directory Provider"

    comData.ActiveConnection = dbActiveDirectory

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  NAME" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  'LDAP://" & strDomain & "'" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  OBJECTCLASS='computer'" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  NAME"

        .CommandText = strSQL

        .Properties("Page Size") = 1000
        .Properties("Searchscope") = ADS_SCOPE_SUBTREE
    End With

    Set snpData = comData.Execute

    If Err <> 0 Then
        lngResult = MsgBox("An error happened while reading the computer list from Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        Exit Sub
    End If

    If snpData.State = 1 Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objFileLog = objFSO.CreateTextFile("C:\Oracle Remote Client Log.txt", True)

        Do While Not (snpData.EOF)
            If PingTest(CStr(snpData.Fields("Name").Value)) = True Then
                'Write the command file to the remote computer
                Set objFileBatch = objFSO.CreateTextFile("\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat", True)
                objFileBatch.Write "tnsping /? >C:\OracleClientversion.txt" & vbCrLf
                objFileBatch.Close
                Set objFileBatch = Nothing

                lngResult = GetObject("winmgmts:\\" & snpData.Fields("Name").Value & "\root\cimv2:Win32_Process").Create("C:\OracleClientVersion.bat", Null, Null, intProcessID)

                If lngResult = 0 Then
                    'Wait 2 seconds for the command to execute
                    WScript.Sleep 2 * 1000
                End If

                'Erase the batch file from the remote computer
                objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & "\C$\OracleClientVersion.bat"

                Err = 0  'Reset the error indicator
                Set objFile = objFSO.OpenTextFile("\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF, ForReading)

                If Err = 0 Then
                    Do While Not (objFile.AtEndOfStream)
                        strLine = objFile.ReadLine
                        If InStr(UCase(strLine), "TNS PING UTILITY") > 0 Then
                            objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & strLine & vbCrLf
                            Exit Do
                        Else
                            'Do nothing
                        End If
                    Loop

                    objFile.Close

                    If Err = 0 Then
                        'lngResult = MsgBox("No Errors accessing the file on " & CStr(snpData.Fields("Name").Value), 64, "Good")
                    Else
                        'lngResult = MsgBox("An error happened while accessing the output file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        Err = 0
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf
                    End If

                    'Erase the log file from the remote computer
                    objFSO.DeleteFile "\\" & CStr(snpData.Fields("Name").Value) & strOracleClientFileF
                    Err = 0
                Else
                    If Err <> 0 Then
                        'lngResult = MsgBox("An error happened while accessing the INI file on the computer " & CStr(snpData.Fields("Name").Value) & vbCrLf & Err.Description & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                        objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "Error " & Err.Description & vbCrLf

                        If lngResult = 7 Then
                            dbActiveDirectory.Close
                            Set snpData = Nothing
                            Set dbActiveDirectory = Nothing
                            Exit Sub
                        End If
                        Err = 0
                    End If
                End If

            Else
                'lngResult = MsgBox("The computer " & CStr(snpData.Fields("Name").Value) & " could not be pinged" & vbCrLf & "Continue?", 16 + 4, "Oh NO!")
                objFileLog.Write CStr(snpData.Fields("Name").Value) & vbTab & "No ping response" & vbCrLf
                If lngResult = 7 Then
                    dbActiveDirectory.Close
                    Set snpData = Nothing
                    Set dbActiveDirectory = Nothing
                    Exit Sub
                End If
            End If

            snpData.MoveNext
            Set objFile = Nothing
        Loop

        snpData.Close
        objFileLog.Close
    Else
        If Err <> 0 Then
            lngResult = MsgBox("An error happened while connecting to Active Directory" & vbCrLf & Err.Description, 16, "Oh NO!")
        End If
    End If

    dbActiveDirectory.Close
    Set snpData = Nothing
    Set dbActiveDirectory = Nothing
End Sub

Function PingTest(strComputer)
    Dim intPosition
    Dim objShell
    Dim objExec
    Dim strLine
    Dim strCommand

    On Error Resume Next

    PingTest = False
    Set objShell = CreateObject("wscript.shell")
    'command to execute
    strCommand = "PING -i 10 -w 10 -n 1 " & strComputer
    'Create Exec object
    Set objExec = objShell.Exec(strCommand)
    'skip lines that contain information about our DNS
    'server
    Do While objExec.StdOut.AtEndOfStream <> True
        strLine = objExec.StdOut.ReadLine
        intPosition = InStr(UCase(strLine), "RECEIVED =")
        If intPosition > 0 Then
            If InStr(strLines, "TTL expired in transit") = 0 Then
                If Trim(Mid(strLine, intPosition + 10, 2)) = "1" Then
                    PingTest = True
                Else
                    PingTest = False
                End If
            Else
                PingTest = False
            End If
            Exit Do
        End If
    Loop
    Set objShell = Nothing
    Set objExec = Nothing
End Function

.