VRALib Scripting — Issue Commands and Save Output to Excel File

VRALib is a Windows COM and .NET interface that is part of the VanDyke ClientPack for Windows automation toolkit. Using VRALib you can script SSH2 connections to send commands, open port forwarding connections, perform file transfers and file management with SFTP, and manage keys.

The example script below first connects to an SSH2 server, runs the Unix/Linux command uptime, and then writes the current date and the results of the uptime command into the first blank row in the specified Excel spreadsheet.

Here is the link to the example script: vralib-rexec-writeresultstoexcel.vbs.txt

To get the script ready to run in your environment, edit the script code to replace all occurrences of C:\Temp\ with the location on your file system where you desire log files and the resulting workbook file to be saved.

Once you've edited the script to replace C:\Temp\ with the folder location of your choosing, you can launch the script with command line parameters to control the machine to which the connection is made, credentials for authentication, etc. The path to the workbook in which to save the output can also be provided on the command line. For example:

  CScript.exe "path to this script.vbs" /host:myhost /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"

Once you've confirmed the script is working as desired, a batch file can be created that will cause the script to be run across multiple target hosts, with the results stored in the same workbook (one sheet per host specified). For example:

  ::MyFile.bat
  CScript.exe "path to this script.vbs" /host:host1 /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
  CScript.exe "path to this script.vbs" /host:host2 /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
  CScript.exe "path to this script.vbs" /host:host3 /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
  CScript.exe "path to this script.vbs" /host:host4 /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
  CScript.exe "path to this script.vbs" /host:host5 /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
  :: etc...

Note that the script example contains minimal error checking to handle any connection errors that may occur.

The script could be adapted to automatically generate reports to track remote system factors like disk space, uptime, performance, or to test output. The script could be added to the Windows Task Scheduler to be run on a daily, weekly or other interval. New data is appended to the Excel spreadsheet by the script every time it runs. Charts could be added to the spreadsheet so that the report could be used to do trend analysis.