A way to import/parse this Astaro firewall log format (ulogd) into an Excel workbook

Hi, Carlo-Giuliani.

Please see attached. A warning - there's very little intelligence in the macro so that it will have problems if the file layout is even slightly different or there are embedded double-quotes.

The code is

Option Explicit

Sub Astaro_Log()
Dim FList As Variant
Dim xLast_Row As Long

FList = Application.GetOpenFilename(filefilter:="Astaro Logs(*.txt),*.txt,All Files (*.*), *.*", MultiSelect:=False)
If FList = False Then
    MsgBox "No files selected. Astaro Log processing terminated."
    Exit Sub
End If

Workbooks.OpenText Filename:=FList, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

Rows("1:1").Insert Shift:=xlDown
xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

Range("A1:Z1").Value = Array("Source", "Date", "Time", "Unknown", "ulogd", "id", "severity", "sys", "sub", "name", "action", "fwrule", "initf", "outitf", "srcmac", "dstmac", "srcip", "dstip", "proto", "length", "tos", "prec", "ttl", "srcport", "dstport", "tcpflags")
Range("B2").Formula = "=DATE(MID(A2,1,4),MID(A2,6,2),MID(A2,9,2))"
Range("C2").Formula = "=TIME(MID(A2,12,2),MID(A2,15,2),MID(A2,18,2))"
Range("D2").Formula = "=MID(A2,FIND("" "",A2,1)+1,FIND("" "",A2,FIND("" "",A2,1)+1)-FIND("" "",A2,1)-1)"
Range("E2").Formula = "=MID(A2,FIND(""ulogd["",A2,1)+6,FIND(""]"",A2,FIND(""ulogd["",A2,1)+6)-FIND(""ulogd["",A2,1)-6)"
Range("F2").Formula = "=MID($A2,FIND(F$1&""="""""",$A2,1)+LEN(F$1)+2,FIND("""""""",$A2,FIND(F$1&""="""""",$A2,1)+LEN(F$1)+2)-FIND(F$1&""="""""",$A2,1)-LEN(F$1)-2)"
Range("F2").Copy Destination:=Range("G2:Z2")
Range("B2:Z2").Copy Destination:=Range("B2:Z" & xLast_Row)

With Range("B2:Z" & xLast_Row)
    .Copy
    .PasteSpecial xlPasteValues
    .EntireColumn.AutoFit
End With

Range("B2").Select

FList = "Astaro_Log_" & Format(Now(), "yyyy_mm_dd_hh-mm-ss")

ActiveWorkbook.SaveAs Filename:=FList _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

MsgBox ("Log file saved as " & FList)

End Sub

Select allOpen in new window

Regards,
Brian.Astaro-Log.xlsm

Wow, i wasn't expecting somebody to write code for this!

I'm not familiar with Excel automation and am not sure how to use this code.  First I tried to save it as a .vbs file but got a compilation error...looks this is VBA??  I then groped my way through the process of enabling the developer tab in Excel, importing the code as a module, and ran the code from there.  Success!  It works (with some minor problems I think I can figure out myself.).

But perhaps you can explain to me how to better use the code??  I'm going to accept this as solution one way or the other, but understanding how best to use the code makes the difference between rating the solution as "good" or "excellent"

Carlo-Giuliani,

Just press the blue "Astaro_Log" button in the file. This then prompts you for the log file.

Regards,
Brian.

I don't see any such button.  Where should I be looking?

Carlo-Giuliani,

Please see attached. What do you see when you open the spreadsheet?

Regards,
Brian.
Astaro-Log.jpg

All I see is the data. To get to the macro I have to go to the developer toolbar and select "Macros".

Can you spell out for me what you expected me to do with the code?

What I actually did was:
- Saved the code in a .vbs file (wrong, I now know..)
- Opened Excel (default new, empty, workbook)
- File...Options...enabled Developer toolbar
- On developer toolbar, selected "Visual Basic"
     - Selected file...import....imported the .vbs file that I had created
- back to developer toolbar, selected "Macros"
    - selected "Astaro_Log" and "Run"

This works, but I have the feeling I am doing things the hard way....

Thanks, Carlo-Giuliani.