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

  • 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.

  • 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
    

  • 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.

Answers (0)