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>
Comments (3)
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
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.
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 |
+-----+----------+----------+----------+-----------------+-------------+---------------------------------------+