Nxlog im_dbi is not working

Question

I am able to insert data into PostgreSQL using nxlog(om_dbi).

But I am not able to select data(or fetch data) from PostgreSQL using nxlog. I tried many options nothing is working.

And in nxlog document also for IM_DBI module description has only "FIXME" mentioned.

Document Link: http://nxlog.org/documentation/nxlog-community-edition-reference-manual-v20928#im_dbi

Please help me to solve this.

Logs:

<Input dbiin>
Module im_dbi
SavePos TRUE
SQL SELECT * FROM NEW_TABLE
Driver pgsql
Option host 127.0.0.1
Option username chitta
Option password ''
Option dbname db
</Input>

2014-10-16 14:29:17 WARNING nxlog-ce received a termination request signal, exiting...
2014-10-16 14:29:18 INFO nxlog-ce-2.8.1248 started
2014-10-16 14:29:18 ERROR im_dbi failed to execute SQL statement. ERROR: column "id" does not exist;LINE 1: SELECT * FROM NEW_TABLE WHERE id = 1;

Note:
the module will automatically prepends a "WHERE id > %d" clause.

Answer 1

Not an answer, but here's some help.

The most important directive is missing: SQL Select ID as id, DateOccured as EventTime, data from logtable

Source: https://www.mail-archive.com/[e-mail]/msg00225.html

I'm currently in the same boat. My assumption is that your data is not formatted in a way that nxlog can interpret. Troubleshooting and will get back to you if I can find a resolution.

Also digging through the source code for the im_dbi module.

https://github.com/lamby/pkg-nxlog-ce/blob/master/src/modules/input/dbi/im_dbi.c

Answer 2

The answer by SoMuchToGrok is valid. Actually the question already has this: "ERROR: column "id" does not exist". The table must have an id column or you must use SELECT x as id so that the result set has id in it