NxLog parsing issues with CSV

Post a different question
4
responses

Hello,

I have a CSV column that has returns in it. If I try to run NxLog, it errors out saying it expected 16 columns and got 0 (for the blank lines), got 1 (when there was one entry), etc.

The configuration I have works if I open up the CSV in excel or something and replace the returns with a ; instead.

I was wondering if there was a way to do that with NxLog. Maybe something like

Exec $Message = replace($column11, "\r\n", ";");

but i cannot seem to get NxLog to run correctly because it's spitting out the errors stated above.

Any help would be great.

Thanks

AskedAugust 10, 2018 - 8:00pm

Comments (4)

  • motts's picture

    It looks like this in a text file.

    EventID MachineName Data    Index   Category    CategoryNumber  EntryType   Message Source  ReplacementStrings  InstanceId  TimeGenerated   TimeWritten UserName

    538 tst-srv System.Byte[]   66036704    Logon/Logoff    2   SuccessAudit    **User Logoff:

        User Name:  tst-srv$

        Domain:     tst.com

        Logon ID:       (0x2,0x507D04B9)

        Logon Type: 3**
        Security    System.String[] 538 8/7/2018 19:17  8/7/2018 19:17  NT AUTHORITY\SYSTEM

    But when you open it in Excel, all the paragraph data within the ** is under the Message Column. In text, it's in quotes. I just switched to ** to pick out a little easier.

    I hope this helps

    August 10, 2018 - 9:12pm
  • Zhengshi's picture
    (NXLog)

    You are going to run into a lot of issues and gotchas trying to get this to work, I think. If this is truly meant to be CSV, I would go back to the source and have them use a different delimiter than space (or tab, since it is copied it is hard to tell). comma, pipe, semi-colon all would probably be good delimiters.

    It looks like this is from a Windows system. What is the source? there may be other, easier ways to get the same information.

    August 17, 2018 - 7:48pm
  • Zhengshi's picture
    (NXLog)

    This should show how changing the source to have a unique delimiter would help simplify things. I used pipe (|) in this example.
    I decided to take a couple minutes to show an example. I took some liberties with your input and assumed the spacing was tabs (\t) so you will need to modify that if not.
    You should notice how xm_multiline and xm_csv can work together with distinguishable delimiters.

    <Extension from_csv>
        Module xm_csv
        Fields EventID, MachineName, Data, Index, Category, CategoryNumber, EntryType, Message, Source, ReplacementStrings, InstanceId, TimeGenerated, TimeWritten, UserName
        FieldTypes string, string, string, string, string, string, string, string, string, string, string, string, string, string
        Delimiter |
</Extension>

<Extension log>
  Module xm_multiline
  HeaderLine    /^\t538.+/    # Needs to be updated to an actual REGEX, I cheated ;)
</Extension>

<Extension json>
  Module xm_json
  PrettyPrint TRUE
</Extension>

<Extension syslog>
  Module xm_syslog
</Extension>

<Input in>
  Module im_file
  File  'multiline_csv.txt'
  InputType log
  SavePos FALSE
  ReadFromLast FALSE
  <Exec>
        # Removes the header line that shows field names
        if $raw_event =~ /EventID\tMachineName\tData\tIndex\tCategory\tCategoryNumber\tEntryType\tMessage\tSource\tReplacementStrings\tInstanceId\tTimeGenerated\tTimeWritten\tUserName/ drop();
        $raw_event = replace($raw_event, "\t", "");   # Removes tabs
        $raw_event = replace($raw_event, "\n", " ");    # Replaces newline with a space (mostly for the $Message)
        parse_csv();      # Pulls apart $raw_event based on delimiter and gives us fields like $message
        to_json();      # Assembles fields into JSON (Don't know if you need JSON, but makes it pretty to read for demonstration)
  </Exec>
</Input>
<Output file>
    Module          om_file
    File            "/tmp/testout.log"
</Output>
<Route 1>
    Path        in => file
</Route>

    Output

    {
    "EventReceivedTime": "2018-08-17T14:43:45.313601-05:00",
    "SourceModuleName": "in",
    "SourceModuleType": "im_file",
    "EventID": "538",
    "MachineName": "tst-srv",
    "Data": "System.Byte[]",
    "Index": "66036704",
    "Category": "Logon/Logoff",
    "CategoryNumber": "2",
    "EntryType": "SuccessAudit",
    "Message": "User Logoff:  User Name:tst-srv$  Domain:tst.com  Logon ID:(0x2,0x507D04B9)  Logon Type:3",
    "Source": "Security",
    "ReplacementStrings": "System.String[]",
    "InstanceId": "538",
    "TimeGenerated": "8/7/2018 19:17",
    "TimeWritten": "8/7/2018 19:17",
    "UserName": "NT AUTHORITY\\SYSTEM "
}

    Input:

    EventID MachineName     Data    Index   Category        CategoryNumber  EntryType       Message Source  ReplacementStrings      InstanceId      TimeGenerated   TimeWritten     UserName

        538     |tst-srv        |System.Byte[]  |66036704       |Logon/Logoff   |2      |SuccessAudit   |"User Logoff:

                User Name:      tst-srv$

                Domain: tst.com

                Logon ID:       (0x2,0x507D04B9)

                Logon Type:     3"
        |Security       |System.String[]        |538    |8/7/2018 19:17 |8/7/2018 19:17 |NT AUTHORITY\SYSTEM

    August 17, 2018 - 9:52pm
  • b0ti's picture
    (NXLog)

    You will need to parse it when you have the full record by using xm_multiline. If you are lucky xm_csv will be able to parse it if the delimiter is set correctly.

    August 17, 2018 - 9:23pm

Answers (0)