Having issues with mysql

Post a different question
4
responses

I'm trying to get nxlog to read from a mysql table and output any changes from the last table read to a text file in csv tab delineated format.  Right now all it's doing is injecting multiple carriage returns into the text file with no text.  Am I heading in the right direction or have I totally borked the config?  I'm working with the following config:

<Extension csv>
        Module xm_csv
        Fields $facility, $severity, $hostname, $timestamp, $application, $message
        FieldTypes string, string, string, string, string, string
        Delimiter \t
</Extension>

 

<Input dbiin>
    Module im_dbi
        SavePos TRUE
        Driver mysql
        Option host localhost
        Option username USERNAME
        Option dbname DBNAME
        Option password PASSWORD
        SQL SELECT facility, severity, hostname, timestamp, application, message FROM table
</Input>

<Output out>
       Module om_file
       File "/var/log/test.txt"
</Output>

<Route 1>

      Path dbiin => out

</Route>

 

AskedJanuary 25, 2017 - 4:24pm

Answer (1)

Looks like you forgot to call the csv conversion. The following should do that:

<Output out>
       Module om_file
       File "/var/log/test.txt"
       Exec csv->to_csv();
</Output>
AnsweredJanuary 25, 2017 - 5:31pm

Comments (3)

  • jkrautter's picture

    Sample snippet from nxlog.log.  it initially appends the contents of the table to the output file but then re-appends everything without any new input into the table rather than just appending new entries.

    2017-01-25 10:48:15 DEBUG module dbiin started (libdbi version libdbi v0.8.4/dbd_mysql v0.8.3)
    2017-01-25 10:48:15 DEBUG im_dbi sql: SELECT facility, severity, hostname, timestamp, application, message FROM testing WHERE id = 206
    2017-01-25 10:48:15 DEBUG nx_event_to_jobqueue: READ (dbiin)
    2017-01-25 10:48:15 DEBUG event added to jobqueue
    2017-01-25 10:48:15 DEBUG worker 0 processing event 0x7fada801c920
    2017-01-25 10:48:15 DEBUG PROCESS_EVENT: READ (dbiin)
    2017-01-25 10:48:15 DEBUG im_dbi sql: SELECT facility, severity, hostname, timestamp, application, message FROM table WHERE id > 206 LIMIT 10
     

    January 25, 2017 - 6:03pm
  • b0ti's picture
    (NXLog)

    As per the reference manual:

    "The result set returned by the SELECT statement must contain an id column which is then stored and used for the next query."

    Your's doesn't contain the id.

    January 25, 2017 - 6:14pm
  • jkrautter's picture

    mysql> select * from table
        -> ;
    +-----+----------+----------+----------+-----------------+-------------+---------------------------------------+
    | id  | facility | severity | hostname | timestamp       | application | message                               |
    +-----+----------+----------+----------+-----------------+-------------+---------------------------------------+
    |   1 | local7   | info     | pookie   | Jan 24 16:22:18 | sshd        | this shit is broken                   |
    | 201 | local5   | critical | barfy    | 24 Jan 17:17:08 | telnet      | lets see if this works                |
    | 202 | local1   | info     | barfy    | 24 Jan 17:17:17 | telnet      | letssssss see if this works           |
    | 203 | local2   | info     | barfy    | 24 Jan 17:17:23 | telnet      | lets see if any this works            |
    | 204 | local3   | info     | barfly   | 24 Jan 17:17:54 | telnet      | lets see if any of this works         |
    | 205 | local6   | info     | barfly   | 24 Jan 17:17:58 | apache      | lets see if sdfsdfsdany of this works |
    | 206 | local5   | info     | barfly   | 24 Jan 17:18:58 | apache      | letsdany of this works                |
    | 207 | local1   | info     | barfly   | 24 Jan 17:20:58 | sshd        | letsdanyorks                          |
    | 208 | local1   | info     | barfly   | 24 Jan 17:25:58 | sshd        | letsdanyorks                          |
    | 209 | local1   | info     | barfly   | 24 Jan 17:28:58 | sshd        | letsdanyorks                          |
    | 210 | local1   | info     | barfly   | 24 Jan 17:30:58 | sshd        | letsdanyorks                          |
    | 211 | local5   | info     | pookie   | Jan 25 08:22:19 | sshd        | this shit is still broken             |
    | 212 | local5   | info     | pookie   | Jan 25 08:35:19 | sshd        | this shit is still broken             |
    | 213 | local5   | info     | pookie   | Jan 25 08:37:19 | sshd        | this shit is still broken             |
    | 214 | local5   | info     | pookie   | Jan 25 08:40:19 | sshd        | this shit is still broken             |
    | 215 | local5   | info     | pookie   | Jan 25 08:50:19 | sshd        | this shit is still broken             |
    +-----+----------+----------+----------+-----------------+-------------+---------------------------------------+

     

    January 25, 2017 - 6:14pm