Using a csv/xls file as input to connect to a host and run a separate command on each

[gates2010]

Hi,
Requesting help with something I am trying to solve.. For something I am working on, I need to open close to 100 tabs in secure-crt (in different windows) and have it running..

This is what I am trying to accomplish with securecrt:
1.Input file will be a csv with: groupnumber,hostname,username,password,command,tab-title
2.Create a script that would:
: read the csv file and for each groupnumber it would open a new securecrt window
- open tabbed ssh connections using the hostname,username,password
- run the command (command is a streaming command, ex: top, which continuously outputs data)
- set the tab-title or the window (setting tab title or the window title itself)


Could someone help me with this? I am looking at close to 100 entries in the csv file.

Thank you.

[jdev]

Seems like there would be a lot of overhead in just creating the .csv file...

How are you generating that CSV file to begin with?

If it's a manual operation, it seems like it might be just as effective to create individual sessions in sub-folders (each named to match your group numbers) that you can then edit to perform Logon Actions to issue the commands you want to run. Then, you can connect to sessions within each folder in a new window by right-clicking the folder and choosing "Connect in Tabs in New Window".

If you desire to script this, it would possibly be fraught with complication and reliability issues since you desire to have a separate window for each group and each connection is running a separate command. Passing a "command" to run at the remote shell (once it's ready) would involve using /SCRIPT "path/to/SomeRunCommand.vbs" /ARG "command you desire to run", but since /ARG is global to SecureCRT across all existing tabs in that instance, you'd have to play with timing to make sure the first tab's RunCommand.vbs script has an opportunity to read the crt.Arguments() before the subsequent tab gets launched with its /ARG value overwriting what was originally available in that window.

You'd probably need to split it up into a master script and subscript. The master script would operate outside of SecureCRT, reading the CSV file and launches separate instances of SecureCRT for each "group". The subscript would be something the master script kicks off when launching SecureCRT, using the /SCRIPT command line argument to tell SecureCRT to start running a script once connected. The subscript would be passed in an arg informing SecureCRT as to what command to run once it's safe to start sending things to the remote device. Your master script would need to introduce delays between each launching of SecureCRT so that prior instances have an opportunity to process the /SCRIPT file and read in any /ARG values to stash into variables before the next SecureCRT gets launched.

Launching the first instance of SecureCRT for a "group" would need to be done without /T, and subsequent launches of SecureCRT for the same group would need to be with /T.

Also, the first launch of SecureCRT for a group would need to be followed by enough of a delay to allow that first instance to get up and running, prepared to handle the /T hand-offs that will be coming its way for the subsequent launchings of SecureCRT for subsequent hosts/commands part of the same group.

Don't have an example of this yet, but if something becomes available, we can try to revisit this.

The forum community is welcome to chime in with any ideas/examples they have already available...

--Jake

[jdev]

Originally Posted by gates2010

1.Input file will be a csv with: groupnumber,hostname,username,password,command,tab-title
2.Create a script that would:
: read the csv file and for each groupnumber it would open a new securecrt window
- open tabbed ssh connections using the hostname,username,password
- run the command (command is a streaming command, ex: top, which continuously outputs data)
- set the tab-title or the window (setting tab title or the window title itself)

OK. Had some time to look into this, and am providing an example of what this might look like.

Script #1, the main script that launches SecureCRT into groups...

# $language = "VBScript"
# $interface = "1.0"

Set g_fso = CreateObject("Scripting.FileSystemObject")
Set g_shell = CreateObject("WScript.Shell")

' Collection of groups we read in
Set objGroups = CreateObject("Scripting.Dictionary")

Class Connection
    Public Hostname
    Public Username
    Public Password
    Public Command
    Public TabName
End Class

Main

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Main()
    ' Read the contents of the file
    Set objFile = g_fso.OpenTextFile(g_shell.SpecialFolders("Desktop") & "\GroupListing.csv", 1)
    strFileData = objFile.ReadAll()
    objFile.Close

    ' Split it up into an array of lines read in from the file
    vLines = Split(strFileData, vbcrlf)

    ' Iterate over each line
    nCurLine = 0
    For Each strLine in vLines
        nCurLine = nCurLine + 1

        ' Split the line into tokens separated by ',' chars
        vTokens = Split(strLine, ",")
        If Ubound(vTokens) <> UBound(Split("group,hostname,username,pass,cmd,tabname", ",")) Then
            MsgBox "CSV file is messed up. Line " & nCurLine & " doesn't have enough tokens on it: [" & strLine & "]"
            Exit Sub
        End If

        ' Assign variable names for each of the tokens, by index, so they're
        ' easier for us to use/recognize
        strGroup = Trim(vTokens(0))

        ' For the hostname...through...tabname vars, we'll first create a
        ' new Connection object and populate it's members with the data
        ' from the remaining tokens we've read in from the current line.
        Set objConnection = New Connection
        objConnection.Hostname = Trim(vTokens(1))
        objConnection.Username = Trim(vTokens(2))
        objConnection.Password = Trim(vTokens(3))
        objConnection.Command = Trim(vTokens(4))
        objConnection.TabName = Trim(vTokens(5))

        ' If this is the first time we've seen this group number, we'll
        ' need to create a new collection of connections for this group.
        If Not objGroups.Exists(strGroup) Then
            Set objGroup = CreateObject("Scripting.Dictionary")
            ' Add the (still empty, cause it's new)
            ' connection collection to the list of known
            ' groups, using the group number as the key and
            ' the connection collection object as the value:
            objGroups.Add strGroup, objGroup
        End If

        ' Get a reference to the existing Connection collection for this
        ' group.
        Set objGroup = objGroups(strGroup)

        ' Add the new Connection to the current group, using its
        ' "position" as the key (value is most important
        ' here, but keys have to be unique, so we might
        ' as well just use the collection's count + 1).
        objGroup.Add objGroup.Count + 1, objConnection

        ' Put the updated group back into place within
        ' the collection of groups.
        Set objGroups(strGroup) = objGroup
    Next

    strGroupReport = ""
    For Each strGroup in objGroups.Keys()
        strGroupReport = strGroupReport & vbcrlf & _
            strGroup & ":" & vbtab & objGroups(strGroup).Count & " connections"
    Next

    If strGroupReport = "" Then
        strGroupReport = "[No groups read in]"
    Else
        strGroupReport = vbcrlf & "Group" & vbtab & "# Connections" & vbcrlf & _
            "-----------------------------------------------" & strGroupReport
    End If

    MsgBox("We've read in " & objGroups.Count & " unique groups:" & vbcrlf & strGroupReport)

    ' Now that we have groups read in, we'll need to launch SecureCRT instances that help
    ' us have a separate window for each group, and the connections w/in each group opened
    ' in tabs within that group window.
    nXPos = 10
    nYPos = 10
    ' Start iterating over groups...
    For Each strGroup In objGroups.Keys()
        ' Within each group, iterate over connctions. The first
        ' one will be different because we want it in a separate window!
        nFirst = True
        For Each objConnection in objGroups(strGroup).Items()
            If nFirst Then
                strCommand = "SecureCRT" & _
                    " /TITLEBAR """ & strGroup & """" & _
                    " /POS " & nXPos & " " & nYPos & _
                    " /N """ & objConnection.TabName & """" & _
                    "" & _
                    " /SCRIPT """ & g_shell.SpecialFolders("Desktop") & "\RunCommand.vbs""" & _
                    "   /ARG """ & objConnection.Command & """" & _
                    "" & _
                    " /SSH2 " & _
                    "   /L " & objConnection.Username & _
                    "   /PASSWORD """ & objConnection.Password & """" & _
                    "   " & objConnection.Hostname
            Else
                strCommand = "SecureCRT" & _
                    " /T " & _
                    " /N """ & objConnection.TabName & """" & _
                    "" & _
                    " /SCRIPT """ & g_shell.SpecialFolders("Desktop") & "\RunCommand.vbs""" & _
                    "   /ARG """ & objConnection.Command & """" & _
                    "" & _
                    " /SSH2 " & _
                    "   /L " & objConnection.Username & _
                    "   /PASSWORD """ & objConnection.Password & """" & _
                    "   " & objConnection.Hostname
            End If

            'MsgBox "About to run this command: " & vbcrlf & vbcrlf & strCommand
            g_shell.Run(strCommand)

            If nFirst Then
                ' Gotta give time for the above command to open a new window so that it's
                ' available for the subsequent connections to open in tabs within "that"
                ' new window. Otherwise, each new instance of SecureCRT will be started so
                ' quickly that the subsequent tabs may open in separate windows of their
                ' own or in an already-existing window.
                ' You may have to play around with this 2000 value to match what your
                ' system's response time is...
                WScript.Sleep(2000)
                nFirst = False
            Else
                WScript.Sleep(500)
            End If

        Next

        ' Poor man's stair stepping of windows...
        nXPos = nXPos + 25
        nYPos = nYPos + 25

        If nXPos > 700 Then nXPos = 10
        If nYPos > 500 Then nYPos = 10
    Next
End Sub

Script #2, runs the command on the remote host.

' RunCommand.vbs
'
' Example of a "WaitForScreenContentsToStopChanging()" method to determine
' when it's safe to run a command after first logging on to a remote host.
'
' The method protrayed in this example will work in situations where lines of
' data coming from the remote are each unique, or the data you're waiting to
' receive is the initial data received in the session and the number of lines
' of data you're waiting to recieve has fewer lines than the number of rows
' on the terminal screen.
'
' This script is designed to be run with two /ARG parameters:
'   1st /ARG param: [REQUIRED] Command to run.
'   2nd /ARG param: [OPTIONAL] Timing interval for data arrival checking (ms)
'
' For example:
'   SecureCRT /Script RunCommand.vbs /ARG "ls -al" /ARG 125 ...<host_connection_info>...

' Stash all of our arguments into a collection before we get going with any
' other script code...
Set cArguments = CreateObject("Scripting.Dictionary")
For nArgNum = 1 To crt.Arguments.Count
    ' zero-based indices for crt.Arguments retrieval means
    ' we use nArgNum - 1. Key is index, Value is arg itself.
    cArguments.Add nArgNum - 1, crt.Arguments(nArgNum - 1)
Next

nMsDataReceiveWindow = 300

Sub Main()
    ' We're only expecting 2 args max, so if we have more than two, then
    ' let's complain so that it's easier to avoid problems later (like if
    ' a command has spaces in it, and the caller fails to quote the command
    ' so that it appears as a singular arg to this script).
    If cArguments.Count > 2 Then
        crt.Session.SetStatusText "Incorrect # of args (" & _
            cArguments.Count & ") provided to script."
        Exit Sub
    End If
    
    ' If we don't have enough arguments, we cannot run a command, so let's
    ' complain:
    If cArguments.Count < 1 Then
        crt.Session.SetStatusText "Insufficient args provided to script."
        Exit Sub
    End If
    
    ' 1st /ARG is the command we're supposed to run
    strCommand = cArguments(0)

    ' 2nd /ARG is the timing interval, if present
    If cArguments.Count > 1 Then
        nMsDataReceiveWindow = CInt(cArguments(1))
    End If
    
    ' Wait for the initial "splash text" and shell prompt to appear
    ' from the remote before we attempt to send any commands... if
    ' we try to send too soon, the remote may not be ready, and our
    ' command might be dropped, or only partially issued.
    WaitForScreenContentsToStopChanging(nMsDataReceiveWindow)
    
    ' Send the Command, along with a CR to simulate pressing Enter:
    crt.Screen.Send strCommand & vbcr
    
End Sub

' -----------------------------------------------------------------------------
Sub WaitForScreenContentsToStopChanging(nMsDataReceiveWindow)
    ' This function relies on new data received being different from the
    ' data that was already received.  It won't work if, as one example, you
    ' have a screenful of 'A's and more 'A's arrive (because one screen
    ' "capture" will look exactly like the previous screen "capture").
    Dim bOrig

    ' Store Synch flag for later restoration
    bOrig = crt.Screen.Synchronous
    ' Turn Synch off since speed is of the essence; we'll turn it back on (if
    ' it was already on) at the end of this function
    crt.Screen.Synchronous = False

    strLastScreen = crt.Screen.Get(1,1,crt.Screen.Rows,crt.Screen.Columns)
    Do
        crt.Sleep nMsDataReceiveWindow

        strNewScreen = crt.Screen.Get(1,1,crt.Screen.Rows, crt.Screen.Columns)
        If strNewScreen = strLastScreen Then Exit Do

        strLastScreen = strNewScreen
    Loop

    ' Restore the Synch setting
    crt.Screen.Synchronous = bOrig
End Sub

Example CSV file:

1,192.168.232.21,user,N0tg00d,ls -al,ls
1,192.168.232.315,user,vuln3r4bl3,top,top
1,192.168.232.215,mary,p4$$w0rd,tail -f /var/log/messages,tail
200,192.168.232.15,keri,gu3$$me,dmesg,dmesg
200,192.168.232.5,jerry,b4d4ppl3s,whoami,who am i?
357,10.10.1.2,ari,pl34$3g0,strace,strace
357,10.10.2.1,user,d0n'thurtm3,ls -al,ls
357,10.10.1.1,user,l0ck3d0ff,top,top
357,10.10.2.2,mary,l0ck3d0ut,tail -f /var/log/messages,tail
9345,192.168.23.1,keri,st0ptry1ng,dmesg,dmesg
9345,192.168.23.2,jerry,g3tm30ut0fh3r3,whoami,who am I?

--Jake

[gates2010]

Jake, you are awesome!!!! I just had to do just a few tweaks here and there to match my environment and it works magically...

Thanks a lot!!!!!

[jdev]

You're welcome.

Please keep us posted on how much time this ends up saving you...

--Jake