' Description:
'-------------------------------------------------------------------------------
'   This script connects to an SSH2 server, runs the Unix/Linux command
'   'uptime', and writes the current date and the results of the uptime command
'   into the first blank row in the specified Excel spreadsheet.
'
' Notes:
'-------------------------------------------------------------------------------
' - Fill in the connection information prior to running this script, or use
'   command line arguments. Command line options can be used to specify host,
'   port, username, password, and path+name of excel workbook in which to store
'   results. This allows for expanding the functionality of the script to
'   connect to other hosts without having to edit the script code. For example:
'   
'     script.vbs /host:myhost /user:myuser /pass:mypass /wkbk:"C:\uptime.xls"
'   
' - If the workbook (.xls file) does not exist prior to running this script, it
'   will be created automatically.
'   
' - Uptime information will be added to the workbook in a sheet matching the
'   name of the host/address to which the connection is made. If a sheet
'   matching the host is not found in the workbook, a new sheet with that name
'   will be added automatically and activated so that it is the selected sheet
'   the next time the workbook is opened for viewing in Excel.
'   
' - This script does minimal error checking for handling failed connections,
'   etc. by logging the time and error of any connection failures to column "G"
'   within the spreadsheet.
'-------------------------------------------------------------------------------

Const xlContinuous = 1
Const xlEdgeBottom = 9
Const xlCenter = -4108
Const xlNone = -4142
Const xlRight = -4152

Set objFso = CreateObject("Scripting.FileSystemObject")

g_strExcelWkBkFilePath = "C:\temp\test.xls"

Set License = CreateObject("vralib.License")
License.AcceptEvaluationLicense

' Create an SSH2 connection object
Set g_objConnection = CreateObject("VRALIB.Connection")
g_objConnection.AutoAcceptHostKey = True

' Set up default connection information
g_objConnection.Hostname = "192.168.0.1"
g_objConnection.Port = 22
g_objConnection.Username = "user"
g_objConnection.Password = "p4$$w0rd"

' Override defaults with options supplied on the command line:
' Handle /host:hostname command line spec:
If WScript.Arguments.Named.Exists("host") Then _
    g_objConnection.Hostname = WScript.Arguments.Named("host")
' Handle /port:port command line spec:
If WScript.Arguments.Named.Exists("port") Then _
    g_objConnection.Port = CInt(WScript.Arguments.Named("port"))
' Handle /user:username command line spec:
If WScript.Arguments.Named.Exists("user") Then _
    g_objConnection.Username = WScript.Arguments.Named("user")
' Handle /pass:password command line spec:
If WScript.Arguments.Named.Exists("pass") Then _
    g_objConnection.Password = WScript.Arguments.Named("pass")
' Handle /wkbk:path command line spec:
If WScript.Arguments.Named.Exists("wkbk") Then _
    g_strExcelWkBkFilePath = WScript.Arguments.Named("wkbk")

' Specify authentication method to attempt as "password":
g_objConnection.AuthenticationMethods = "password"

' Log to a file with a name based on the hostname to which we're connecting:
g_strLogFile = "C:\temp\vralib-rexec-log-" & g_objConnection.Hostname & ".txt"

' Keep a backup copy of the log file so that we can have the
' prior run's results to look at should we need to diagnose
' any problems.
On Error Resume Next
objFso.DeleteFile g_strLogFile & ".bak"
objFso.MoveFile g_strLogFile, g_strLogFile & ".bak"
On Error Goto 0

g_objConnection.DebugLogFile = g_strLogFile
g_objConnection.DebugLevel = 5

' Connect to the remote host, taking note if we get connected successfully:
On Error Resume Next
g_objConnection.Connect
nConnectionError = Err.Number
strConnectionErr = Err.Description
On Error Goto 0

Set objExcel = CreateObject("Excel.Application")

' If the excel file specified (either by default or as per /wkbk command line
' option), this script will create one automatically.
g_bFileExists = objFso.FileExists(g_strExcelWkBkFilePath)

If g_bFileExists Then
    ' Use the existing workbook
    Set objWorkbook = objExcel.Workbooks.Open(g_strExcelWkBkFilePath)
Else
    ' Create a new workbook
    Set objWorkbook = objExcel.Workbooks.Add
End If

' Find out if a worksheet already exists for the host we're connected to:
For nSheetIndex = 1 To objWorkbook.Worksheets.Count
    Set objWorksheet = objWorkbook.Worksheets(nSheetIndex)
    If LCase(objWorksheet.Name) = LCase(g_objConnection.Hostname) Then
        bSheetExists = True
        Exit For
    End If
Next

If Not bSheetExists Then
    ' Add a new worksheet to the workbook, naming it the same name as the
    ' host to which we're connected.
    Set objWorksheet = objWorkbook.WorkSheets.Add
    objWorksheet.Name = g_objConnection.Hostname
    objWorksheet.Range("A1").Value = "Time of Day"
    objWorksheet.Range("B1").Value = "Uptime"
    objWorksheet.Range("C1").Value = "Users"
    objWorksheet.Range("D1").Value = "LdAvg 1 Min"
    objWorksheet.Range("E1").Value = "LdAvg 5 Min"
    objWorksheet.Range("F1").Value = "LdAvg 15 Min"
    objWorksheet.Range("G1").Value = "Notes"
    objWorksheet.Range("A1:G1").Font.Bold = True
    objWorksheet.Range("A1:G1").Borders(xlEdgeBottom).LineStyle = xlContinuous
    objWorksheet.Columns("A:F").EntireColumn.AutoFit
End If

' Find the first empty row in column "A" of the worksheet.
intRow = 1
strDN = ""
Do Until objWorksheet.Range("A" & intRow).Value = "" 
      intRow = intRow + 1
Loop

' If successfully connected, execute the 'uptime' command
' and read in the results, otherwise, populate results with an error message so
' it will get logged in column "G" of the spreadsheet:
If g_objConnection.IsConnected Then
    Set g_objExec = g_objConnection.Exec("uptime")
    strResults = g_objExec.StdOut.ReadLine
    
    ' Disconnect from the SSH2 server
    g_objConnection.Disconnect
Else
    bErrorConnecting = True
End If

' Use a regular expression to parse out "interesting" information for storing in
' different columns within the spreadsheet.  Here are some example 'uptime'
' outputs from various UNIX types for which the regexp pattern below will work
' for extracting specific data:
'  16:28:50 up 18 days, 19:57, 20 users,  load average: 0.23, 0.18, 0.17
'   4:49pm  up 14 days,  4:32, 20 users,  load average: 1.93, 1.84, 1.72
'  16:50:39 up 2 min,  1 user,  load average: 1.31, 0.90, 0.36
'   2:35pm  up 15 day(s),  4:14,  2 users,  load average: 0.01, 0.01, 0.00
'   2:38pm  up 14 days,  3:08,  2 users,  load average: 0.00, 0.02, 0.04
'   2:38PM  up 19 days, 20:54, 1 user, load averages: 0.00, 0.01, 0.00
' 02:40PM   up 13 days,  23:33,  3 users,  load average: 0.04, 0.02, 0.01
'    14:43  up 41 secs, 1 user, load averages: 0.22 0.06 0.02
'  14:49:20 up  1:00,  1 user,  load average: 0.00, 0.02, 0.00

Set re = New RegExp
re.MultiLine = False
re.Global = True
re.IgnoreCase = True
re.Pattern = _
    "\s*(.*?)" & "\s+up\s+" & "(.*)," & "\s+(\d+)\s+(?:user|users)," & _
    "\s+load\s+average(?:s)*\:\s+(\d+\.\d+)[,\s]+(\d+\.\d+)[,\s]+(\d+\.\d+)"
Set objMatches = re.Execute(strResults)

' If matches were found, we'll be able to parse out individual fields;
' otherwise, store the entire output from 'uptime' in column "G".
If bErrorConnecting Then
    objWorksheet.Range("A" & intRow).Value = Time
    objWorksheet.Range("A" & intRow).NumberFormat = "h:mm:ss;@"
    objWorksheet.Range("G" & intRow).Value = _
        "[ Connection Error (" & Now & "): " & strConnectionErr & " ]" & _
        strResults
ElseIf objMatches.Count < 1 Then
    objWorksheet.Range("A" & intRow).Value = Time
    objWorksheet.Range("A" & intRow).NumberFormat = "h:mm:ss;@"
    objWorksheet.Range("G" & intRow).Value = _
        "['uptime' output pattern not recognized. Raw data follows...]" & _
        strResults
Else
    ' Sanitize the time, as needed, with a space before AM or PM so that
    ' Excel will automatically recognize it as a time value:
    strTime = UCase(objMatches(0).Submatches(0))
    strTime = Replace(strTime, "PM", " PM")
    strTime = Replace(strTime, "AM", " AM")
    
    ' Store the values into the corresponding columns of the selected row:
    objWorksheet.Range("A" & intRow).Value = strTime
    objWorksheet.Range("B" & intRow).Value = objMatches(0).Submatches(1)
    objWorksheet.Range("C" & intRow).Value = objMatches(0).Submatches(2)
    objWorksheet.Range("D" & intRow).Value = objMatches(0).Submatches(3)
    objWorksheet.Range("E" & intRow).Value = objMatches(0).Submatches(4)
    objWorksheet.Range("F" & intRow).Value = objMatches(0).Submatches(5)
    
    ' Specify data formats for cells that need special treatment in Excel for
    ' consistent display of info:
    objWorksheet.Range("A" & intRow).NumberFormat = "h:mm:ss;@"
    objWorksheet.Range("C" & intRow).NumberFormat = "0"
    objWorksheet.Range("D" & intRow).NumberFormat = "0.00"
    objWorksheet.Range("E" & intRow).NumberFormat = "0.00"
    objWorksheet.Range("F" & intRow).NumberFormat = "0.00"
End If

' Adjust column widths so they are all visible:
objWorksheet.Columns("A:G").EntireColumn.AutoFit

' Activate the spreadsheet so that it's selected within the workbook the next
' time the workbook is opened for viewing:
objWorksheet.Activate

' Save the spreadsheet:
If g_bFileExists Then
    objExcel.Workbooks(1).Save
Else
    ' We created the workbook in this instance of the script; Excel's automation
    ' interface requires calling SaveAs() the first time a workbook is created
    ' and saved.
    objExcel.Workbooks(1).SaveAs g_strExcelWkBkFilePath
End If

' Close the workbook, and shut down the Excel automation instance:
objExcel.Workbooks(1).Close
objExcel.Quit

' Report to StdOut, if available:
If strConnectionErr = "" Then
    strMsg = "Uptime information for " & g_objConnection.Hostname & " " & _
        "was added to the Excel spreadsheet (" & g_strExcelWkBkFilePath & _
        ") : " & strResults
Else
    strMsg = "Failed to connect to " & g_objConnection.Hostname & ": " & _
       strConnectionErr & vbcrlf & _
       "Unable to update spreadsheet with uptime info."
End If
On Error Resume Next
    wscript.stdOut.WriteLine strMsg
On Error Goto 0