Retrive iptables output from remote server to an excel sheet in Windows using plink

get-iptables.bat

@echo off
set plink=plink.exe
set putty_profile=PuttyProfile
set putty_user=yourself
set putty_port=22

echo === mangle ==============================================================
%plink% %putty_profile% -P %putty_port% -ssh -l %putty_user% -batch ^
    -C "iptables --table mangle --list -n -v --line-numbers"
echo === nat =================================================================
%plink% %putty_profile% -P %putty_port% -ssh -l %putty_user% -batch ^
    -C "iptables --table nat --list -n -v --line-numbers"
echo === filter ==============================================================
%plink% %putty_profile% -P %putty_port% -ssh -l %putty_user% -batch ^
    -C "iptables --list -n -v --line-numbers"
echo =========================================================================
@echo on

iptables.bas

Option Explicit

Public Function ShellRun(sCmd As String) As String

    'Run a shell command, returning the output as a string

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object
    Dim s As String
    Dim sLine As String
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    ShellRun = s

End Function


Public Function IpTablesString(Optional script As String = "") As String
    
    If Len(script) = 0 Then
        script = ActiveWorkbook.Path + "\get-iptables.bat"
    End If
    IpTablesString = ShellRun(script)
End Function


Public Function startsWith( _
        ByVal target As String, _
        ByVal to_search As String, _
        Optional b_case_insensitive As Boolean = True _
        ) As Boolean
        
    If b_case_insensitive Then
        target = Strings.LCase(target)
        to_search = Strings.LCase(to_search)
    End If
    If Len(to_search) > Len(target) Then
        startsWith = False
    ElseIf Len(to_search) = 0 Then
        startsWith = False
    Else
        startsWith = Strings.Mid(target, 1, Len(to_search)) = to_search
    End If
End Function


Public Function endsWith( _
        ByVal target As String, _
        ByVal to_search As String, _
        Optional b_case_insensitive As Boolean = True _
        ) As Boolean
        
    If b_case_insensitive Then
        target = Strings.LCase(target)
        to_search = Strings.LCase(to_search)
    End If
    If Len(to_search) > Len(target) Then
        endsWith = False
    ElseIf Len(to_search) = 0 Then
        endsWith = False
    Else
        endsWith = Strings.Mid(target, Len(target) - Len(to_search), Len(to_search)) = to_search
    End If
End Function


Public Sub SplitFinalPart( _
        value As String, _
        ByRef opts As String, _
        ByRef comments As String, _
        ByRef other As String)
        
    opts = ""
    comments = ""
    other = ""
    
    Dim comment_start As Long, comment_end As Long
    comment_start = Strings.InStr(1, value, "/*")
    comment_end = Strings.InStr(1, value, "*/")
    
    If (comment_start = 0 Or comment_end = 0) Or (comment_end < comment_start) Then
        opts = value
    Else
        If comment_start > 1 Then
            opts = Strings.Trim(Strings.Mid(value, 1, comment_start - 1))
        End If
        If comment_end < Len(value) - 2 Then
            other = Strings.Trim(Strings.Mid(value, comment_end + 2, Len(value)))
        End If
        comments = Strings.Trim(Strings.Mid(value, comment_start, comment_end - comment_start + 3))
    End If
    
End Sub

Public Sub IpTableStringToWorksheet( _
        sht As Worksheet, _
        str As String, _
        Optional srow As Long = 1, _
        Optional scol As Long = 1)
        
    Const tot_col As Long = 13
    ' Debug.Print str
    Dim lines, line
    Dim parts, part
    Dim i As Long
    lines = Split(str, vbCrLf)
    
    Dim row As Long
    row = srow
    
    Dim chains_location As Dictionary
    Set chains_location = New Dictionary
    
    With sht.Range(sht.Cells(row, scol), sht.Cells(row, scol + tot_col))
        .Font.Underline = True
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
    sht.Cells(row, scol + 0).value = "Nr. crt."
    sht.Cells(row, scol + 1).value = "Pakets"
    sht.Cells(row, scol + 2).value = "Bytes"
    sht.Cells(row, scol + 3).value = "Target"
    sht.Cells(row, scol + 4).value = "Protocol"
    sht.Cells(row, scol + 5).value = "Options"
    sht.Cells(row, scol + 6).value = "Inbound"
    sht.Cells(row, scol + 7).value = "Out-bound"
    sht.Cells(row, scol + 8).value = "Source"
    sht.Cells(row, scol + 9).value = "Desti-nation"
    sht.Cells(row, scol + 10).value = "Filter"
    sht.Cells(row, scol + 11).value = "Comment"
    sht.Cells(row, scol + 12).value = "Other"
    
    sht.Columns(scol + 0).Font.Color = RGB(11, 101, 143)
    sht.Columns(scol + 1).Font.Color = RGB(4, 135, 127)
    sht.Columns(scol + 2).Font.Color = RGB(1, 130, 61)
    sht.Columns(scol + 3).Font.Color = RGB(103, 110, 5)
    sht.Columns(scol + 4).Font.Color = RGB(117, 48, 5)
    sht.Columns(scol + 5).Font.Color = RGB(110, 8, 72)
    sht.Columns(scol + 6).Font.Color = RGB(105, 5, 120)
    sht.Columns(scol + 7).Font.Color = RGB(51, 9, 110)
    sht.Columns(scol + 8).Font.Color = RGB(5, 88, 105)
    sht.Columns(scol + 9).Font.Color = RGB(17, 92, 53)
    sht.Columns(scol + 10).Font.Color = RGB(117, 83, 52)
    sht.Columns(scol + 11).Font.Color = RGB(92, 150, 15)
    sht.Columns(scol + 12).Font.Color = RGB(21, 157, 191)
    
    For Each line In lines
        Dim stripped As String
        stripped = line
        For i = 1 To 10
            stripped = Strings.Replace(stripped, "  ", " ")
        Next
        parts = Split(stripped, " ")
        If startsWith(line, "=== ") Then
            row = row + 5
            With sht.Range(sht.Cells(row, scol), sht.Cells(row, scol + tot_col - 1))
                .Merge
                .value = "table " + Strings.Trim(Strings.Replace(line, "=", ""))
                .Interior.Color = RGB(129, 253, 119)
                .Font.Underline = False
                .Font.Bold = True
                .Font.Color = RGB(0, 0, 255)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .RowHeight = .RowHeight * 2
            End With
            row = row + 1
        ElseIf startsWith(line, "Chain ") Then
            row = row + 2
            With sht.Range(sht.Cells(row, scol), sht.Cells(row, scol + tot_col - 1))
                .Merge
                .value = parts(1)
                .Interior.Color = RGB(224, 179, 139)
                .Font.Underline = False
                .Font.Bold = True
                .Font.Color = RGB(0, 0, 0)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                ' .RowHeight = .RowHeight * 2
            End With
            If parts(1) <> "FORWARD" And _
               parts(1) <> "INPUT" And _
               parts(1) <> "OUTPUT" And _
               parts(1) <> "PREROUTING" And _
               parts(1) <> "POSTROUTING" _
            Then
                chains_location.Add parts(1), row
            End If
            row = row + 1
        ElseIf startsWith(line, "num ") Then
        ElseIf UBound(parts) - LBound(parts) <= 0 Then
            
        Else
            Dim track As String
            track = ""
            
            For i = 0 To 9
                If Len(track) = 0 Then
                    track = parts(i)
                Else
                    track = track + " " + parts(i)
                End If
                If Not IsNumeric(parts(i)) Then
                    parts(i) = "'" & parts(i)
                End If
                sht.Cells(row, scol + i) = parts(i)
            Next
            Dim rest As String
            rest = Strings.Trim(Strings.Replace(stripped, track, ""))
            
            Dim opts As String, comments As String, other As String
            SplitFinalPart rest, opts, comments, other
            sht.Cells(row, scol + 10) = opts
            sht.Cells(row, scol + 11) = comments
            sht.Cells(row, scol + 12) = other
            
            Dim target As String
            target = sht.Cells(row, scol + 3).value
            If target = "ACCEPT" Then
                sht.Cells(row, scol + 3).Font.Bold = True
                sht.Cells(row, scol + 3).Font.Color = RGB(0, 255, 0)
                sht.Cells(row, scol + 3).Interior.Color = RGB(10, 10, 10)
            ElseIf target = "DROP" Then
                sht.Cells(row, scol + 3).Font.Bold = True
                sht.Cells(row, scol + 3).Font.Color = RGB(50, 255, 255)
                sht.Cells(row, scol + 3).Interior.Color = RGB(10, 10, 10)
            ElseIf target = "REJECT" Then
                sht.Cells(row, scol + 3).Font.Bold = True
                sht.Cells(row, scol + 3).Font.Color = RGB(255, 0, 0)
                sht.Cells(row, scol + 3).Interior.Color = RGB(10, 10, 10)
            ElseIf target = "RETURN" Then
                sht.Cells(row, scol + 3).Font.Bold = True
                sht.Cells(row, scol + 3).Font.Color = RGB(255, 255, 0)
                sht.Cells(row, scol + 3).Interior.Color = RGB(10, 10, 10)
            End If
            row = row + 1
        End If
    Next
    With sht.Range(sht.Cells(srow, scol), sht.Cells(row, scol + tot_col))
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Columns.AutoFit
    End With
    
    For i = srow To row
        Dim crt_chain As String
        crt_chain = sht.Cells(i, scol + 3).value
        If Len(crt_chain) > 0 Then
            If chains_location.Exists(crt_chain) Then
                Dim drow As Long
                drow = chains_location.Item(crt_chain)
                sht.Hyperlinks.Add _
                    Anchor:=sht.Cells(i, scol + 3), _
                    Address:="", _
                    SubAddress:="'" & sht.Name & "'!" & Strings.Replace(CStr(sht.Cells(drow, scol).Address), "$", "")
            End If
        End If
    Next
    
    
End Sub


Public Sub cmdNewSheetFromLiveServer()
    Dim sht As Worksheet
    Set sht = ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Sheets.Item(ActiveWorkbook.Sheets.Count))
    Dim this_time
    this_time = now
    sht.Name = CStr(Year(this_time)) + "-" + CStr(Month(this_time)) + "-" + CStr(Day(this_time)) + " " + _
                CStr(Hour(this_time)) + "-" + CStr(Minute(this_time))
    IpTableStringToWorksheet sht, IpTablesString
End Sub