Python + XLRD SecureCRT Import

Reading Time: 8 minutes

First of all a disclaimer. I am NOT a programer. I promise this could probably be cleaned up considerably by someone that actually does programming. Also, It may require some tweaking to work on your system. This is tested on Mac 10.12.3 and SecureCRT 8.1*

I’ve always loved using SecureCRT. I often find myself needing to add anywhere from a small to a large number of sessions to my list. Especially in my current role. I had remembered in my past at an old roll where I used Windows as my primary OS (work issued) that I had discovered a forum that had a python and VBS script to import sessions out of a CSV. Now that I am running on Apple I sought out that old forum and grabbed the python script. Drats!!! The python script doesn’t work on my new version of SecureCRT for Mac (8.1). Then I started thinking. Most of the time clients give me a nice spreadsheet of IP addresses. This got me thinking, why not write my own that uses Excel. So here it is!

 

First off, you need to install XLRD into python. I used easy_install but pip may be an option for you as well.

Second, create a folder for the script and it’s template file used to import sessions. This is where you will place the below two files named SecureCRTImport.py and template.ini

 

 

SecureCRTImport.py

#Install XLRD using the below
#pip install xlrd

#Import Modules
import xlrd
import os.path
from os.path import expanduser
from Tkinter import Tk
from tkFileDialog import askopenfilename

#Create Dialog to Request XLSX File
Tk().withdraw()
filename = askopenfilename()

#Expand ~ to Users HOMEDIR
HOMEDIR = expanduser("~")

#Clear Terminal Screen
os.system('clear')


print""
print""
print "Using File...   " + filename

#Open Workbook
wb = xlrd.open_workbook(os.path.join(filename), on_demand = True)

#Open Worksheet 1
wb.sheet_names()
sh = wb.sheet_by_index(0)
i = 1

#Count Number Of Rows Not Blank
print ""
print ""
IMPORTNUMBER = sh.nrows
DISPLAYCOUNT = IMPORTNUMBER - 1
print "Discovered...   %i   ...Sessions" % DISPLAYCOUNT
print ""
print ""




#Allow User Row Input
#IMPORTNUMBER = int(raw_input("How Many sessions are we importing...."))
#IMPORTNUMBER = IMPORTNUMBER + 1

#Modify Row into Counting from 0

ROWCHOICE = 1

#Loop While ROWCHOICE is Less than Counted Rows
while ROWCHOICE < IMPORTNUMBER:

	#Define Variables For Python
	#Folder = '../Sessions/'
	Folder2 = str(sh.cell(ROWCHOICE,2).value)
	Filename = str(sh.cell(ROWCHOICE,3).value)

	if not os.path.isdir(HOMEDIR + '/Library/Application Support/VanDyke/SecureCRT/Config/Sessions/' + Folder2 + '/'):
		os.mkdir(HOMEDIR + '/Library/Application Support/VanDyke/SecureCRT/Config/Sessions/' + Folder2 + '/')


	Folder = HOMEDIR + '/Library/Application Support/VanDyke/SecureCRT/Config/Sessions/' + Folder2 + '/'

	#Define Variable Library used in Template Files
	def replace_words(base_text, device_values):
    		for key, val in device_values.items():
        		base_text = base_text.replace(key, val)
    		return base_text

	device = {}
 
	device["$HOSTNAME"] = str(sh.cell(ROWCHOICE,0).value)
	device["$PROTOCOL"] = str(sh.cell(ROWCHOICE,1).value)

	#Check If MPLS is set to YES


	#Open MPLSHUBDMVPNTTEMPLATE
	t = open('template.ini', 'r')
	tempstr = t.read()
	t.close()

	#Replace Variables With Spreadsheet Values
	output = replace_words(tempstr, device)

	#Append output to file name HOSTNAME.txt
	fout = open(Folder + Filename + '.ini', 'a')
	fout.write(output)
	fout.close()

	ROWCHOICE = ROWCHOICE + 1



	print 'Importing Session ' + Filename + '........ into      ' + Folder2
print ""
print ""

template.ini

S:"Username"=
S:"Password V2"=
S:"Login Script V3"=
D:"Session Password Saved"=00000000
S:"Local Shell Command Pre-connect V2"=02:69ed0d0044bfb68ab8e3b851eeb862e99806502e56eb5d9295733b1fbe04e693f607975b4e7600e9a56eb4459237c1be
S:"Monitor Username"=
S:"Monitor Password V2"=
B:"Normal Font v2"=000000a0
 f1 ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 01 00 00 00 01 4d 00 00 00
 65 00 00 00 6e 00 00 00 6c 00 00 00 6f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 70 00 00 00
B:"Narrow Font v2"=000000a0
 f1 ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 01 00 00 00 01 4d 00 00 00
 65 00 00 00 6e 00 00 00 6c 00 00 00 6f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 70 00 00 00
D:"Use Narrow Font"=00000000
S:"SCP Shell Password V2"=
S:"PGP Upload Command V2"=
S:"PGP Download Command V2"=
D:"Is Session"=00000001
S:"Protocol Name"=$PROTOCOL
D:"Request pty"=00000001
S:"Shell Command"=
D:"Use Shell Command"=00000000
D:"Force Close On Exit"=00000000
D:"Forward X11"=00000000
S:"XAuthority File"=
S:"XServer Host"=127.0.0.1
D:"XServer Port"=00001770
D:"XServer Screen Number"=00000000
D:"Enforce X11 Authentication"=00000001
D:"Request Shell"=00000001
D:"Max Packet Size"=00001000
D:"Pad Password Packets"=00000001
S:"Sftp Tab Local Directory V2"=/Users/mattouellette/Downloads
S:"Sftp Tab Remote Directory"=
S:"Hostname"=$HOSTNAME
S:"Firewall Name"=None
D:"Allow Connection Sharing"=00000000
D:"Disable Initial SFTP Extensions"=00000000
D:"[SSH2] Port"=00000016
S:"Key Exchange Algorithms"=ecdh-sha2-nistp521,ecdh-sha2-nistp384,ecdh-sha2-nistp256,diffie-hellman-group14-sha1,diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,gss-group1-sha1-toWM5Slw5Ew8Mqkay+al2g==,gss-gex-sha1-toWM5Slw5Ew8Mqkay+al2g==,gss-group1-sha1-m1xNP3rRAc6JVrs+BUdo5Q==,gss-gex-sha1-m1xNP3rRAc6JVrs+BUdo5Q==,gss-group1-sha1-6Em1viOOK9MUfdI34X8izQ==,gss-gex-sha1-6Em1viOOK9MUfdI34X8izQ==,gss-group1-sha1-4s+AAtlALj0s3Z3xGjNXPQ==,gss-gex-sha1-4s+AAtlALj0s3Z3xGjNXPQ==,gss-group1-sha1-B5Sl0rEWNJyWTODd+gPcDg==,gss-gex-sha1-B5Sl0rEWNJyWTODd+gPcDg==,gss-group1-sha1-eipGX3TCiQSrx573bT1o1Q==,gss-gex-sha1-eipGX3TCiQSrx573bT1o1Q==
D:"Use Global Host Key Algorithms"=00000001
S:"Host Key Algorithms"=ssh-rsa,ssh-ed25519,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,null,ssh-dss
S:"Cipher List"=aes256-ctr,aes192-ctr,aes128-ctr,aes256-cbc,aes192-cbc,aes128-cbc,twofish-cbc,blowfish-cbc,3des-cbc,arcfour
S:"MAC List"=hmac-sha2-512,hmac-sha2-256,hmac-sha1,hmac-sha1-96,hmac-md5,hmac-md5-96,umac-64@openssh.com
S:"SSH2 Authentications V2"=password,publickey,keyboard-interactive,gssapi
S:"Compression List"=none
D:"Compression Level"=00000005
D:"GEX Minimum Size"=00000400
D:"GEX Preferred Size"=00000800
D:"Use Global Public Key"=00000001
S:"Identity Filename V2"=
D:"Public Key Type"=00000000
D:"Public Key Certificate Store"=00000000
S:"PKCS11 Provider Dll"=
S:"Public Key Certificate Serial Number"=
S:"Public Key Certificate Issuer"=
S:"Public Key Certificate Username"=
D:"Use Username From Certificate"=00000000
D:"Certificate Username Location"=00000000
D:"Use Certificate As Raw Key"=00000001
S:"GSSAPI Method"=auto-detect
S:"GSSAPI Delegation"=full
S:"GSSAPI SPN"=host@$(HOST)
D:"SSH2 Common Config Version"=00000006
D:"Enable Agent Forwarding"=00000002
D:"Transport Write Buffer Size"=00000000
D:"Transport Write Buffer Count"=00000000
D:"Transport Receive Buffer Size"=00000000
D:"Transport Receive Buffer Count"=00000000
D:"Sftp Receive Window"=00000000
D:"Sftp Maximum Packet"=00000000
D:"Sftp Parallel Read Count"=00000000
D:"Preferred SFTP Version"=00000000
S:"Port Forward Filter"=allow,127.0.0.0/255.0.0.0,0 deny,0.0.0.0/0.0.0.0,0
S:"Reverse Forward Filter"=allow,127.0.0.1,0 deny,0.0.0.0/0.0.0.0,0
D:"Port Forward Receive Window"=00000000
D:"Port Forward Max Packet"=00000000
D:"Port Forward Buffer Count"=00000000
D:"Port Forward Buffer Size"=00000000
D:"Auth Prompts in Window"=00000000
S:"Transfer Protocol Name"=None
D:"ANSI Color"=00000000
D:"Color Scheme Overrides Ansi Color"=00000000
S:"Emulation"=Xterm
S:"Default SCS"=B
D:"Use Global ANSI Colors"=00000001
B:"ANSI Color RGB"=00000040
 00 00 00 00 a0 00 00 00 00 a0 00 00 a0 a0 00 00 00 00 a0 00 a0 00 a0 00 00 a0 a0 00 c0 c0 c0 00
 80 80 80 00 ff 00 00 00 00 ff 00 00 ff ff 00 00 00 00 ff 00 ff 00 ff 00 00 ff ff 00 ff ff ff 00
D:"Keypad Mode"=00000000
D:"Line Wrap"=00000001
D:"Cursor Key Mode"=00000000
D:"Newline Mode"=00000000
D:"Enable 80-132 Column Switching"=00000001
D:"Ignore 80-132 Column Switching When Maximized or Full Screen"=00000000
D:"Enable Cursor Key Mode Switching"=00000001
D:"Enable Keypad Mode Switching"=00000001
D:"Enable Line Wrap Mode Switching"=00000001
D:"Enable Alternate Screen Switching"=00000001
D:"WaitForStrings Ignores Color"=00000000
D:"SGR Zero Resets ANSI Color"=00000001
D:"SCO Line Wrap"=00000000
D:"Display Tab"=00000000
S:"Display Tab String"=
B:"Window Placement"=0000002c
 2c 00 00 00 00 00 00 00 03 00 00 00 fc ff ff ff fc ff ff ff fc ff ff ff fc ff ff ff 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00
D:"Is Full Screen"=00000000
D:"Rows"=00000018
D:"Cols"=00000050
D:"Scrollback"=000001f4
D:"Resize Mode"=00000000
D:"Sync View Rows"=00000001
D:"Sync View Cols"=00000001
D:"Horizontal Scrollbar"=00000002
D:"Vertical Scrollbar"=00000002
S:"Color Scheme"=Monochrome
S:"Output Transformer Name"=UTF-8
D:"Use Unicode Line Drawing"=00000001
D:"Draw Lines Graphically"=00000001
D:"Blinking Cursor"=00000001
D:"Cursor Style"=00000000
D:"Use Cursor Color"=00000000
D:"Cursor Color"=00000000
D:"Foreground"=00000000
D:"Background"=00ffffff
D:"Bold"=00000000
D:"Map Delete"=00000000
D:"Map Backspace"=00000000
S:"Keymap Name"=Xterm
S:"Keymap Filename V2"=
D:"Use Alternate Keyboard"=00000000
D:"Emacs Mode"=00000000
D:"Emacs Mode 8 Bit"=00000000
D:"Preserve Alt-Gr"=00000000
D:"Jump Scroll"=00000001
D:"Minimize Drawing While Jump Scrolling"=00000000
D:"Audio Bell"=00000001
D:"Visual Bell"=00000000
D:"Scroll To Clear"=00000001
D:"Close On Disconnect"=00000000
D:"Clear On Disconnect"=00000000
D:"Scroll To Bottom On Output"=00000001
D:"Scroll To Bottom On Keypress"=00000001
D:"CUA Copy Paste"=00000000
D:"Use Terminal Type"=00000000
S:"Terminal Type"=
D:"Use Answerback"=00000000
S:"Answerback"=
D:"Use Position"=00000000
D:"X Position"=00000000
D:"X Position Relative Left"=00000001
D:"Y Position"=00000000
D:"Y Position Relative Top"=00000001
D:"Local Echo"=00000000
D:"Strip 8th Bit"=00000000
D:"Shift Forces Local Mouse Operations"=00000001
D:"Ignore Window Title Change Requests"=00000000
D:"Copy Translates ANSI Line Drawing Characters"=00000000
D:"Copy to clipboard as RTF and plain text"=00000000
D:"Translate Incoming CR To CRLF"=00000000
D:"Dumb Terminal Ignores CRLF"=00000000
D:"Use Symbolic Names For Non-Printable Characters"=00000000
D:"Show Chat Window"=00000002
D:"User Button Bar"=00000002
S:"User Button Bar Name"=Default
S:"User Font Map V2"=
S:"User Line Drawing Map V2"=
D:"Hard Reset on ESC c"=00000000
D:"Ignore Shift Out Sequence"=00000000
D:"Use Title Bar"=00000000
S:"Title Bar"=
D:"Show Wyse Label Line"=00000000
D:"Send Initial Carriage Return"=00000001
D:"Use Login Script"=00000000
D:"Use Script File"=00000000
S:"Script Filename V2"=
S:"Script Arguments"=
S:"Upload Directory V2"=${VDS_USER_DATA_PATH}
S:"Download Directory V2"=${VDS_USER_DATA_PATH}
D:"XModem Send Packet Size"=00000000
S:"ZModem Receive Command"=rzr
D:"Disable ZModem"=00000000
D:"ZModem Uses 32 Bit CRC"=00000000
D:"Force 1024 for ZModem"=00000000
D:"ZModem Encodes DEL"=00000001
D:"ZModem Force All Caps Filenames to Lower Case on Upload"=00000001
D:"Send Zmodem Init When Upload Starts"=00000000
S:"Log Filename V2"=
S:"Custom Log Message Connect"=
S:"Custom Log Message Disconnect"=
S:"Custom Log Message Each Line"=
D:"Log Only Custom"=00000000
D:"Generate Unique Log File Name When File In Use"=00000001
D:"Log Prompt"=00000000
D:"Log Mode"=00000000
D:"Start Log Upon Connect"=00000000
D:"Raw Log"=00000000
D:"Log Multiple Sessions"=00000000
D:"New Log File At Midnight"=00000000
D:"Trace Level"=00000000
D:"Keyboard Char Send Delay"=00000000
D:"Use Word Delimiter Chars"=00000000
S:"Word Delimiter Chars"=
D:"Idle Check"=00000000
D:"Idle Timeout"=0000012c
S:"Idle String"=
D:"Idle NO-OP Check"=00000000
D:"Idle NO-OP Timeout"=0000003c
D:"AlwaysOnTop"=00000000
D:"Line Send Delay"=00000005
D:"Character Send Delay"=00000000
D:"Wait For Prompt"=00000000
S:"Wait For Prompt Text"=
D:"Wait For Prompt Timeout"=00000000
D:"Send Scroll Wheel Events To Remote"=00000000
D:"Position Cursor on Left Click"=00000000
D:"Highlight Reverse Video"=00000001
D:"Highlight Bold"=00000000
D:"Highlight Color"=00000000
S:"Keyword Set"=<None>
S:"Ident String"=
D:"Raw EOL Mode"=00000000
D:"Eject Page Interval"=00000000
S:"Monitor Listen Address"=0.0.0.0:22
D:"Monitor Allow Remote Input"=00000000
D:"Disable Resize"=00000002
D:"Auto Reconnect"=00000002
B:"Page Margins"=00000020
 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f 00 00 00 00 00 00 f0 3f
B:"Printer Font v2"=000000a0
 f3 ff ff ff 00 00 00 00 00 00 00 00 00 00 00 00 90 01 00 00 00 00 00 00 03 02 01 31 43 00 00 00
 6f 00 00 00 75 00 00 00 72 00 00 00 69 00 00 00 65 00 00 00 72 00 00 00 20 00 00 00 4e 00 00 00
 65 00 00 00 77 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 64 00 00 00
D:"Page Orientation"=00000001
D:"Paper Size"=00000001
D:"Paper Source"=00000007
S:"Printer Name"=
D:"Disable Pass Through Printing"=00000000
D:"Buffer Pass Through Printing"=00000000
D:"Force Black On White"=00000000
D:"Use Raw Mode"=00000000
D:"Printer Baud Rate"=00009600
D:"Printer Parity"=00000000
D:"Printer Stop Bits"=00000000
D:"Printer Data Bits"=00000008
D:"Printer DSR Flow"=00000000
D:"Printer DTR Flow Control"=00000001
D:"Printer CTS Flow"=00000000
D:"Printer RTS Flow Control"=00000001
D:"Printer XON Flow"=00000000
S:"Printer Port"=
S:"Printer Name Of Pipe"=
D:"Use Printer Port"=00000000
D:"Use Global Print Settings"=00000001
D:"Operating System"=00000000
S:"Time Zone"=
S:"Last Directory"=
S:"Initial Local Directory V2"=
S:"Default Download Directory V2"=
D:"File System Case"=00000000
S:"File Creation Mask"=
D:"Disable Directory Tree Detection"=00000002
D:"Verify Retrieve File Status"=00000002
D:"Resolve Symbolic Links"=00000002
B:"RemoteFrame Window Placement"=0000002c
 2c 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 fc ff ff ff fc ff ff ff 00 00 00 00
 00 00 00 00 00 00 00 00 00 00 00 00
S:"Remote ExplorerFrame State"=1,-1,-1
S:"Remote ListView State"=1,1,1,0,0
S:"SecureFX Remote Tab State"=1,-1,-1
D:"Restart Data Size"=00000000
S:"Restart Datafile Path"=
D:"Max Transfer Buffers"=00000004
D:"Filenames Always Use UTF8"=00000000
D:"Use Multiple SFTP Channels"=00000000
D:"Disable MLSX"=00000000
D:"SecureFX Trace Level"=00000001
D:"SecureFX Use Control Address For Data Connections"=00000001
D:"Use PGP For All Transfers"=00000000
D:"Disable Remote File System Watches"=00000000
Z:"Port Forward Table V2"=00000000
Z:"Reverse Forward Table V2"=00000000
Z:"Keymap v3"=00000000
Z:"MAC Log File Tags"=00000000
Z:"Description"=00000000
Z:"SecureFX Post Login User Commands"=00000000
Z:"SecureFX Bookmarks"=00000000
Z:"SCP Shell Prompts"=00000001
 "? ",0,"n"

This next part is simple. Create or modify a spreadsheet so the first four columns are as follows:

Hostname
Protocol
Folder
Sessions Name

Make sure the columns are in this order and then begin populating the data. Protocol may be (SSH2, SSH1 or Telnet). You may do nested folders as well. Here is an example screenshot.

Screen Shot 2017-03-07 at 2.31.48 PM

The next step is to open terminal and move to the folder you stored your scripts in and run this command

python SecureCRTImport.py

This will launch a dialog box asking you to select your .xlsx file.

Screen Shot 2017-03-07 at 2.35.24 PM

Once it reads your xlsx file it will indicate how many sessions it found and import them into SecureCRT into the folders you described.

Screen Shot 2017-03-07 at 2.36.35 PM

Now if we launch SecureCRT we will see the new sessions.

Screen Shot 2017-03-07 at 2.39.42 PM

Share this article:

Permanent link to this article: https://www.packetpilot.com/python-xlrd-securecrt-import/