' ExcelSpreadsheets-SearchingAndLookup-GatewayLogins.vbs
' 
' Questions about scripting in SecureCRT with VBScript?
' Consult the SecureCRT VBScript User Guide online:
'    https://www.vandyke.com/support/tips/scripting/index.html
'
' Description:
' ------------
' This example Script shows how to search for (look up) data
' within an Excel spreadsheet using VBScript running within
' SecureCRT, and read data from other cells within the row
' that contained the data that was found.
' 
' This specific example uses an excel spreadsheet that has the
' following general format:
'          A       |     B    |     C    |     D     |
'   +--------------+----------+----------+-----------+
' 1 | IP Address   | Protocol | Username | Password  |
'   +--------------+----------+----------+-----------+
' 2 | 192.168.0.1  | SSH2     | admin    | p4$$w0rd  |
'   +--------------+----------+----------+-----------+
' 3 | 192.168.0.2  | Telnet   | root     | NtheCl33r |
'   +--------------+----------+----------+-----------+
' 4 | 192.168.0.3  | SSH2     | root     | s4f3rN0w! |
'   +--------------+----------+----------+-----------+
' ... and so on
' 
'
'   - NOTE: SecureCRT must currently be connected to a gateway machine or  
'     jump host on which commands can be issued to connect to secondary hosts 
'     (eg: 'ssh hostb', 'telnet hostc', etc.)
'
'   - A User is prompted for text to search for (in Column "A", as represented
'     by the 'strSearchFor' variable in Sub Main()).
'     
'   - When the user provides a search term, the workbook specified in the
'     'g_strWkBkPath' variable is opened using Excel COM object API calls, and
'     the first sheet in the workbook is searched for the term provided by
'     calling the Lookup() function.  See the Lookup() function definition below
'     for more details on what parameters are needed, etc.
'     
'   - If the user-provided text is found in column "A" of the first spreadsheet
'     in the workbook, the Lookup() function returns as its value the number of
'     the row on which the text was found, and an array is populated with the
'     column values for that row (up to a specified column passed to the Lookup
'     function.)  If the user-provided text is not found, the user is notified
'     and allowed to enter another search term.
'     
'   - Using the information from the array, another function is
'     called: DoWorkWithRowData().  In this example, this function:
'        - Detects the shell prompt automatically using the GetShellPrompt()
'          function defined below
'        - Connects via Telnet or SSH to another remote device from 
'          the shell of the currently connected session
'        - Runs a command on the remote machine, if desired
'     
'   - Once a search has been done, and the appropriate work has been done in the
'     DoWorkWithRowData() function, the script loops back up to prompting the
'     user for a search term until the search prompt is cancelled.

Dim g_shell
Set g_shell = CreateObject("WSCript.Shell")

Dim g_objExcel
Set g_objExcel = Nothing

Dim g_strMyDocs, g_strWkBkPath, g_strMainPrompt
g_strMyDocs = g_shell.SpecialFolders("MyDocuments")
g_strWkBkPath = g_strMyDocs & "\MyExcelData.xls"

' g_strError is a global variable that is used to store error messages
' within called functions so that the error messages are available to
' other (calling) functions or subroutines.
Dim g_strError

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Main()

    crt.Screen.Synchronous = True
    crt.Screen.IgnoreEscape = True

    ' Use the GetShellPrompt() function defined below within this script
    ' to automatically detect the shell prompt using a heuristic as
    ' demonstrated in a couple of example script solutions in the SecureCRT
    ' VBScript Guide:
    '   https://www.vandyke.com/support/tips/scripting/index.html
    strPrompt = GetShellPrompt()
    
    ' Loop, prompting for input until either the user cancels, or until
    ' a match is found within the spreadsheet.
    Dim strSearchFor, strSearchCol
    strSearchFor = "192.168.0.1"
    
    ' Specify the column in which the data you're searching for is expected
    ' to be found
    strSearchCol = "A"
    
    Do
        ' Prompt user for data to find
        strSearchFor = crt.Dialog.Prompt(_
            "Specify what to look for", _
            "Search Spreadsheet For...", _
            strSearchFor)
        
        ' Check for "cancel"
        If strSearchFor = "" Then Exit Do
        
        Dim nRowFound, vData
        ' Call routine to load spreadsheet and look for 'strSearchFor' text
        ' in 'strSearchCol' and retrieve corresponding row data into
        ' vRowData.
        ' Note: When calling this Lookup function, you should remember to
        '       change the last parameter to match the letter that represents
        '       the last (right-most) column of data that you need to be
        '       included as part of the vRowData that is returned when a match
        '       is found.  In this example, the last column of data in the
        '       Excel spreadsheet that is needed is "D".  If the last data
        '       you care about in your spreadsheet is "E", for example, make
        '       sure you change the "D" below to an "E".
        
        nRow = Lookup(g_strWkBkPath, strSearchFor, strSearchCol, vRowData, "D")
   
        ' Check the return value to see if a match was found on any of the
        ' rows within the search column:
        If nRow = 0 Then
            crt.Dialog.MessageBox _
                """" & strSearchFor & """ was not found in column " & _
                """" & strSearchCol & """ in the specified spreadsheet."
        Else
            g_strMainPrompt = GetShellPrompt
            DoWorkWithRowData(vRowData)
        End If

    Loop
    
    ' Close Excel (only if it was ever opened)
    If Not g_objExcel Is Nothing Then g_objExcel.Quit
    
End Sub


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub DoWorkWithRowData(vRowData)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
        strIP = vRowData(Col("A"))
        strProtocol = vRowData(Col("B"))
        strUsername = vRowData(Col("C"))
        strPassword = vRowData(Col("D"))

        Dim strCmd
        Select Case UCase(strProtocol)
            Case "TELNET"
                strCmd = "telnet " & strIP
                crt.Screen.Send strCmd & vbcr

            Case "SSH2", "SSH1"
                strCmd = "ssh " & strUsername & "@" & strIP
                crt.Screen.Send strCmd & vbcr
                           
        End Select

        ' Look for username and/or password prompts
        
        ' Wait for our command to be echoed back to us so we know that it has been
        ' received before we attempt to wait for all possible responses.
        crt.Screen.WaitForString strCmd & vbcr

        ' The order of elements in this array should be in the order of: 
        '  - Expected login prompts (eg: "-->", "#", etc.)
        '  - Special cases (accept host key, enter pwd, enter username, etc.)
        '  - Fail/Bail cases (wrong pwd/login, hostname not found, time out, etc.)
        vPossibleResponses = Array(_
            "-->",_
            "$",_
            "ogin:", _
            "ame:", _
            "(yes/no)?",_
            "sword:",_
            "Permission denied",_
            "incorrect", _
            "not known", _
            "timed out")
            
        ' Start an inner Do..Loop that looks for all defined possible responses to
        ' the Telnet... or SSH... command we just sent.
        Do
            crt.Screen.WaitForStrings vPossibleResponses
            If crt.Screen.MatchIndex > 0 Then
                strStringFound = vPossibleResponses(crt.Screen.MatchIndex - 1)
            End If
            Select Case crt.Screen.MatchIndex
                Case 1,2
                    ' Found "-->" or "$" which in our example indicate successful
                    ' authentications.
                    
                    ' Do work we need to complete on host including sending the exit
                    ' command which will disconnect us from the secondary host
                    ' allowing us to loop up to the top and connect to the next
                    ' host.
                    ' .
                    ' .
                    ' .
                    
                    ' Now that we have done the necessary work (including waiting
                    ' for an indication that the last command we sent above has
                    ' completed, send "exit" to disconnect from secondary host.
                    crt.Screen.Send "exit" & vbcr
                    
                    ' Wait for an indication that the exit command was successful
                    ' before attempting to connect to next host.
                    crt.Screen.WaitForString g_strMainPrompt
                    
                    ' Exit inner Do..Loop since we are done with the success case.
                    Exit Do
                    
                Case 3,4
                    ' Found "ogin" or "ame" which means waiting for user account.
                    crt.Screen.Send strUsername & vbcr

                    ' Fall through to the top of the inner Do..Loop to continue
                    ' waiting for strings until all possibilities are exhausted.

                Case 5
                    ' Found new hostkey prompt.  This indicates that this is the
                    ' first time we have connected to the remote machine, and we
                    ' need to accept the hostkey.    
                    crt.Screen.Send "yes" & vbcr
                    
                    ' Fall through to the top of the inner Do..Loop to continue
                    ' waiting for strings until all possibilities are exhausted.
                
                Case 6
                    ' We are being prompted for a password.  Send it.
                    crt.Screen.Send strPassword & vbcr
                    
                    ' Fall through to the top of the inner Do..Loop to continue
                    ' waiting for strings until all possibilities are exhausted.
                    
                Case 7
                    ' SSH Password was denied or login was incorrect.  Exit this   
                    ' inner Do..Loop and move on to the next host.
                    
                    ' First cancel the current authentication attempt to the
                    ' secondary host.
                    crt.Screen.SendKeys "^c"
                    
                    ' Wait for an indication that the Ctrl+C was successful before
                    ' attempting to connect to next host.
                    crt.Screen.WaitForString g_strMainPrompt

                    ' Exit inner Do..Loop and move to next host.
                    Exit Do
                    
                Case 8
                    ' Telnet login or password was incorrect.  Exit this inner  
                    ' Do..Loop and move on to the next host.
                    
                    ' First cancel the current authentication attempt to the
                    ' secondary host.
                    crt.Screen.SendKeys "^]"
                    crt.Screen.WaitForString "telnet>"
                    crt.Screen.Send "quit" & vbcr
                    
                    ' Wait for an indication that the Ctrl+C was successful before
                    ' attempting to connect to next host.
                    crt.Screen.WaitForString g_strMainPrompt
                    
                    ' Exit inner Do..Loop and move to next host.
                    Exit Do
                    
                Case 9,10
                    ' Not able to reach secondary host.  Connection timed out.  
                    ' Wait for primary host prompt before exiting inner Do..Loop.
                    crt.Screen.WaitForString g_strMainPrompt
                    
                    ' Exit inner Do..Loop and move to next host.
                    Exit Do
                    
                Case Else
                    ' Let user know that there is an unhandled case
                    crt.Session.SetStatusText _
                        "Unhandled """ & strStringFound & """"
                    ' Yikes... Never expect to be here, but if we got here, it's
                    ' probably a programming error you've introduced with the
                    ' 'vPossibleResponses' variable that you'll need to fix
                    crt.Dialog.MessageBox "Yikes!" & vbcrlf & vbcrlf & _
                        "We never expect to get here. if you see this, you" & _
                        vbcrlf & _
                        "have probably introduced a programming error into" & _
                        vbcrlf & _
                        "your script code which will you will need to fix." & _
                        vbcrlf & vbcrlf & _
                        "Chances are you added a string to vPossibleResponses " & _
                        vbcrlf & _
                        "but you haven't added the code to handle " & _
                        vbcrlf & _
                        "what to do when that special string was found:" & _
                        vbcrlf & vbcrlf & vbtab & """" & strStringFound & """"
                    crt.Screen.SendSpecial "MENU_SCRIPT_CANCEL"
                    
                End Select
        Loop ' Inner loop waiting for all possible responses.

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Lookup(strWkBkPath, _
                strSearchFor, _
                strColumnToSearch, _
                ByRef vRowDataArray, _
                nLastColData)
    ' Looks in spreadsheet found in strSpreadSheetPath for text held
    ' in strSearchFor and stores all the data in the found row in
    ' vRowDataArray which is accessible by caller afterwards
    ' 
    '       strWkBkPath:  String w/the full path to the Excel spreadsheet that
    '                     contains the data you're searching for.
    '                     
    ' strColumnToSearch:  String containing the range specification for the
    '                     area of the spreadsheet you expect the data to be
    '                     found.  For example, if you're keying off of column
    '                     'A' in your spreadsheet, you would pass in "A".
    '                     
    '     strLookupText:  String variable containing the text you're searching
    '                     for within the spreadsheet. For example: "192.168.0.1"
    '
    '     vRowDataArray:  Reference to a variable that will become an array
    '                     if this function successfully finds the search term
    '                     provided.
    '                     
    '      nLastColData:  String variable representing the column reference
    '                     that indicates the last column of the row that you
    '                     want returned as part of the data array representing
    '                     the columns on the row that was found. For examle, if
    '                     your spreadsheet has data up to column 'Y' you would
    '                     pass in "Y".
    ' 
    ' Returns row number on which data was found.
    '    If nothing was found, returns zero (which works great since in an Excel
    '    since there is no such thing as Row #0)
    
    ' First, load up the spreadsheet
    If g_objExcel Is Nothing Then
        On Error Resume Next
        Set g_objExcel = CreateObject("Excel.Application")
        nError = Err.Number
        strErr = Err.Description
        On Error Goto 0
        
        If nError <> 0 Then
            crt.Dialog.Prompt _
                "Error: " &  strErr & vbcrlf & _
                vbcrlf & _
                "Perhaps you need MS Excel to be installed or repaired."
            Exit Function
        End If
    End If
    
    ' Now load the workbook (Read-Only), and get a reference to the first sheet
    On Error Resume Next
    Set objWkBk = g_objExcel.Workbooks.Open(strWkBkPath, 0, True)
    nError = Err.Number
    strErr = Err.Description
    On Error Goto 0
    
    If nError <> 0 Then
        g_objExcel.Quit
        crt.Dialog.MessageBox _
            "Error loading spreadsheet """ & strWkBkPath & """:" & vbcrlf & _
            vbcrlf & _
            strErr
        Exit Function
    End If
    
    Set objSheet = objWkBk.Sheets(1)
    
    ' Look in specified column for the given data:
    Set objSearchRange = objSheet.Columns(strColumnToSearch)
    Set objFoundRange = Nothing
    Set objFoundRange = objSearchRange.Find(strSearchFor)
    If Not objFoundRange Is Nothing Then
        Lookup = objFoundRange.Row
        ReDim vRowDataArray(Col(nLastColData))
        For nColIndex = 1 To Col(nLastColData)
            vRowDataArray(nColIndex) = objFoundRange.Cells(1, nColIndex).Value
        Next
    Else
        Lookup = 0
        Exit Function
    End If
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function Col(strLetterRef)
    ' Converts any Excel letter combination into its corresponding column
    ' number. For example "A" is column #1, C is col #3, AA is column #27, etc.
    '
    '   strLetterRef: a string indicating which Excel Column (by letters)
    '                 needs to be converted to a number.  For example "AA".
    '                 Lower-case values are accepted.
    
    Dim nColumnValue
    nColumnValue = 0

    ' Performing base 26 arithmetic...
    For nLetterIndex = Len(strLetterRef) To 1 Step -1
        ' nMultiplier is the number of times 26 (number of letters in English
        ' alphabet) is multiplied by the ASCII value of the current letter.
        nMultiplier = Len(strLetterRef) - nLetterIndex
        
        ' nCurLetter is the ASCII value of the current letter being worked on
        ' (right->left since the right-most letters represent lower-significance
        ' values)
        nCurLetter = ASC(UCase(Mid(strLetterRef, nLetterIndex, 1))) - 64
        nColumnValue = nColumnValue + (nCurLetter * (26 ^ nMultiplier))
    Next
    
    Col = nColumnValue
End Function

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function GetShellPrompt()
    ' Heuristically dermine the shell's prompt.  Crt.Screen.Synchronous must
    ' already have been set to True.  In general, Crt.Screen.Synchronous should
    ' be set to True immediately after a successful crt.Session.Connect().  In
    ' this script, SecureCRT should already be connected -- otherwise, a script
    ' error will occur.
    Do
        ' Simulate pressing "Enter" so the prompt appears again...
        crt.Screen.Send vbcr
        ' Attempt to detect the command prompt heuristically by waiting for the
        ' cursor to stop moving... (the timeout for WaitForCursor above might
        ' not be enough for slower- responding hosts, so you will need to adjust
        ' the timeout value above to meet your system's specific timing
        ' requirements).
        Do
            bCursorMoved = crt.Screen.WaitForCursor(1)
        Loop Until bCursorMoved = False
        ' Once the cursor has stopped moving for about a second, it's assumed
        ' it's safe to start interacting with the remote system. Get the shell
        ' prompt so that it's known what to look for when determining if the
        ' command is completed. Won't work if the prompt is dynamic (e.g.,
        ' changes according to current working folder, etc.)
        nRow = crt.Screen.CurrentRow
        strPrompt = crt.screen.Get(nRow, _
                                   0, _
                                   nRow, _
                                   crt.Screen.CurrentColumn - 1)
        ' Loop until a line of non-whitespace text actually appears:
        strPrompt = Trim(strPrompt)
        If strPrompt <> "" Then Exit Do
    Loop
    
    GetShellPrompt = strPrompt
    
End Function