24
responses

Is it possible to manipulate the ID in SQL queries done by NXLog agent? The documentation states this in im_dbi module:

The module automatically appends a WHERE id > ? LIMIT 10 clause to the statement. The result set returned by the SELECT statement must contain an id column which is then stored and used for the next query.

I have a database that does not get new reqords very often so i want to reset that id for testing and development purposes in the receiving end.

AskedJanuary 29, 2019 - 9:30am

Answer (1)

It sounds like your id field isn't always higher than the previous id then?
Have you tried something like SELECT RecordID*100 AS id, * FROM dbo.test1 WHERE RecordID > ?. to add some bulk to it? You will still need to update your query when you reset the id field though unless you maybe take current epoch time + id AS the id field? That should always give you a new id number that is higher than the last. Something like the following. depending on your language/product, you may have to cast it to int.
SELECT UNIX_TIMESTAMP()+RecordID AS id, * FROM dbo.test1 WHERE RecordID > ?

Edit: example was MySQL. To get epoch from PostgreSQL I believe its something like CAST(EXTRACT(epoch FROM NOW()) AS INT).

Comments (23)

  • JaVa's picture

    The id field contains always higher value than previous and that is not the problem. The root cause is that when i enabled that query in the configuration and piped the results to a file, nothing happens. I've seen with file sources that when you enable the NXLog to collect the logs it doesn't start from the beginning of the file but from the end and does not send anything before a new line is written to the log.

    For the database issue that i'm having, i do not get any errors in the NXLog own logs so i presume that in the initial startup the agent reads the database and finds the current most recent ID and stops there and don't forward anything before there is a new ID with stuff. That why i want to know if it is possible to manipulate the ID as we don't see, what the NXLog agent adds to the query in place of the ? -mark.

  • b0ti's picture
    (NXLog)

    we don't see, what the NXLog agent adds to the query in place of the ? -mark.

    If you set Loglevel DEBUG it should print the query in nxlog.log where you can see this.

  • JaVa's picture

    It does not work. There is nothing in the log file about the queries.

    Here's my config if there is something wrong with it?

    <Input dbi>

    Module im_dbi
    Driver pgsql
    SavePos false
    Option host 127.0.0.1
    Option username tester
    Option password xxxxxxxxxxxxxx
    Option dbname messagelog
    SQL SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord
    </Input>

    <Output file>
    Module om_file
    File "/tmp/xroad_postgresql_test"
    </Output>

    <Route 1>
    Path dbi => file
    </Route>

    We are running this on Red Hat Enterprise Linux Server release 7.6 (Maipo)

    Logs are like this after restart of the service:

    2019-01-30 10:25:29 INFO configuration OK
    2019-01-30 10:25:29 ERROR SSL error, failed to load ca cert from '/opt/nxlog/var/lib/nxlog/cert/agent-ca.pem', reason: No such file or directory, no such file, system lib
    2019-01-30 10:25:30 WARNING already running as uid 0
    2019-01-30 10:25:30 INFO nxlog-4.2.4192 started
    2019-01-30 10:25:30 ERROR SSL error, failed to load ca cert from '/opt/nxlog/var/lib/nxlog/cert/agent-ca.pem', reason: No such file or directory, no such file, system lib

  • JaVa's picture

    I also tested to log in to the database directly with those credentials that NXLog uses and it was succesfull and i also cold make queries with it. I then logged into the database with admin rights and looked at the pg_stat_activity table and it states that this tester has insufficient privilege

    messagelog=> SELECT datname, usename, query FROM pg_stat_activity where usename = 'tester';
    datname | usename | query
    ------------+---------+--------------------------
    messagelog | tester | <insufficient privilege>
    (1 row)

    Any ideas, why this is happening?

  • Zhengshi's picture
    (NXLog)

    I am unsure. It sounds like tester does not have permissions to the table.
    You may want to have your DB team check the permissions and possibly grant them again.

  • JaVa's picture

    Checked those permissions again and also the pg_stat_activity -table information and it seems the user can do those queries, but the destination file get's only newlines.

    postgres=# select pid,usename,client_port,query,query_start from pg_stat_activity;
    pid | usename | client_port | query | query_start
    -------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------
    28121 | tester | 58214 | SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord WHERE id > 761 LIMIT 50 | 2019-01-31 11:31:13.717522+02
    | 2019-01-31 11:31:10.930868+02

    I did that pg_stat_activity query with wrong user last time so it showed that insufficient privileges error there.

    I think the next step would be to get the debugging really working with NXLog. I don't know what else to do because in my opinion the configuration should be ok now.

  • b0ti's picture
    (NXLog)

    I think the next step would be to get the debugging really working with NXLog.

    Adding LogLevel DEBUG should work once you clean up your nxlog.conf as suggested. The included log4ensics.conf has LogLevel INFO which will override the DEBUG level unless you comment out the include.

  • JaVa's picture

    Now i get these log messages:

    2019-01-31 12:49:37 DEBUG PROCESS_EVENT: READ (dbi)
    2019-01-31 12:49:37 DEBUG im_dbi sql: SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord WHERE id > 683 LIMIT 50
    2019-01-31 12:49:37 DEBUG worker 1 got signal for new job
    2019-01-31 12:49:37 DEBUG worker 1 got no event to process
    2019-01-31 12:49:37 DEBUG worker 1 waiting for new event
    2019-01-31 12:49:37 DEBUG worker 2 got signal for new job
    2019-01-31 12:49:37 DEBUG worker 2 got no event to process
    2019-01-31 12:49:37 DEBUG worker 2 waiting for new event
    2019-01-31 12:49:37 DEBUG im_dbi read 50 rows
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 0
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG event added to jobqueue
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 1
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 2
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 3
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 4
    -----------------------------------------------------------------------------------------------------------------------------------------------
    2019-01-31 12:49:37 DEBUG worker 1 got signal for new job
    2019-01-31 12:49:37 DEBUG worker 1 processing event 0x7fceac01a0f0
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG PROCESS_EVENT: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG event added to jobqueue
    2019-01-31 12:49:37 DEBUG om_file_write
    2019-01-31 12:49:37 DEBUG file get_next_logdata: got (queuesize: 38)
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: MODULE_RESUME (dbi)
    2019-01-31 12:49:37 DEBUG event added to jobqueue
    2019-01-31 12:49:37 DEBUG worker 2 got signal for new job
    2019-01-31 12:49:37 DEBUG before nx_logqueue_pop, size: 39
    2019-01-31 12:49:37 DEBUG worker 2 got no event to process
    2019-01-31 12:49:37 DEBUG worker 2 waiting for new event
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 38
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG file get_next_logdata: got (queuesize: 38)
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 39
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 40
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 41
    2019-01-31 12:49:37 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (file)
    2019-01-31 12:49:37 DEBUG before nx_logqueue_push, size: 42

    Any ideas why it still produces only newlines to the destination file? The is data in that table that it queries so something is still wrong here.

  • b0ti's picture
    (NXLog)

    Looks like im_dbi does not set $raw_event so you should format that manually because om_file writes $raw_event only and discards the rest of the fields.

    You can do

    Exec to_json();

    or

    Exec $Message = to_kvp(); to_syslog_bsd();

  • JaVa's picture

    The first command produces this error:

    2019-01-31 13:34:44 DEBUG Error in Exec block: [to_json();]
    2019-01-31 13:34:44 ERROR [modules.c:489/nx_ctx_config_modules()] -;[module.c:1603/nx_module_parse_exec_block()] Couldn't parse Exec block at /opt/nxlog/etc/nxlog.conf:81;[expr-grammar.y:381/parser_do()] couldn't parse statement at line 81, character 16 in /opt/nxlog/etc/nxlog.conf;[expr.c:3363/nx_expr_statement_new_procedure()] procedure 'to_json()' does not exist or takes different arguments

    I tried it to input and output modules.

    Same for that second command. What am i missing here again?

  • JaVa's picture

    Ok, sorted this. I was missing a module. Added this to config:

    <Extension json>
    Module xm_json
    </Extension>

    Do i need to do that same conversion if i was to send those logs to remote syslog?

  • JaVa's picture

    Also i'm referring back to the question that is it possible to manipulate the ID that NXLog puts to the query after WHERE -clause? In my opinion it should be because there are some situations that you need to start the queries from a certain point. If you have data loss in the receiving end or something like that. Now as i correctted the configuration, i have to wait for new data to the database in order to verify that things are working properly.

  • b0ti's picture
    (NXLog)

    It's not possible to manipulate the ID unfortunately. It is saved in the configcache.dat file. The only option is to save a copy of this, then stop + overwrite + restart, or use a hex editor to change the Id there.

  • JaVa's picture

    Thanks for the help. One more thing though, how to forward those logs to syslog? I tried this config:

    <Output out>
    Module om_tcp
    Host 192.168.1.1
    Port 1468
    Exec to_syslog_ietf();
    </Output>

    <Output file>
    Module om_file
    File "/tmp/xroad_postgresql_test"
    </Output>

    <Route 1>
    Path dbi => file,out
    </Route>

    It is not working so should i remove that Exec to_syslog_ietf(); ?

    Also looked at the timestamps of that /opt/nxlog/var/spool/nxlog/configcache.dat file and it has not been modified since the installation of the software so how can it contain the current ID in there?

  • Zhengshi's picture
    (NXLog)

    What part is not working?
    If your Syslog server is setup to receive on 192.168.1.1:1468 TCP then you should be fine.
    to_syslog_ietf(); is needed for your Syslog if it is expecting the events in RFC5424 format (IETF) as this will create a properly formatted event. If your server is expecting in BSD style then you would use to)syslog_bsd();
    https://nxlog.co/documentation/nxlog-user-guide/syslog.html

  • JaVa's picture

    It does not send anything to the syslog server. We have dozen of other NXLog install's where we are reading file sources and they are working just fine sending data to that remote syslog location. But when we try to forward those PostgreSQL logs, it is not working. That port 1468 is working because we are also sending logs with rsyslog from the same host with the same port so this is not a network issue.

  • b0ti's picture
    (NXLog)

    What's wrong with what was suggested before?

    Exec $Message = to_kvp(); to_syslog_bsd();
    

    If you see the data written to the file it should also work with your syslog server.

  • JaVa's picture

    It should work but it doesn't. Now as i edited the configuration from this:

    <Route 1>
    Path dbi => file,out
    </Route>

    To this:

    <Route 1>
    Path dbi => out
    </Route>

    I got a TCP connection to the SIEM system in the receiving end but again no data is transfered. Apparently that Route module doesn't support multiple output paths?

    Also i deleted the configcache.dat -file and NXLog does not create it and still remembers the last read ID from the database.

    2019-02-01 13:57:40 DEBUG no entries found, not writing configcache.dat
    2019-02-01 13:57:40 DEBUG nxlog_shutdown() leave
    2019-02-01 13:57:40 DEBUG reading config cache from /opt/nxlog/var/spool/nxlog/configcache.dat

    File list at that time:

    ls -la /opt/nxlog/var/spool/nxlog/
    total 8
    drwxrwx---. 2 nxlog nxlog 4096 Feb 1 13:56 .
    drwxrwxr-x. 3 root root 4096 Jan 24 09:59 ..

    The logs are like this now when i try to send the logs to remote:

    2019-02-01 13:57:40 DEBUG RESUME: dbi
    2019-02-01 13:57:40 DEBUG module dbi already running, skipping resume
    2019-02-01 13:57:40 DEBUG worker 0 processing event 0x7f7acc009110
    2019-02-01 13:57:40 DEBUG PROCESS_EVENT: READ (dbi)
    2019-02-01 13:57:40 DEBUG im_dbi sql: SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord WHERE id > -1 LIMIT 50
    2019-02-01 13:57:40 DEBUG worker 2 got signal for new job
    2019-02-01 13:57:40 DEBUG worker 2 got no event to process
    2019-02-01 13:57:40 DEBUG worker 2 waiting for new event
    2019-02-01 13:57:40 DEBUG im_dbi read 50 rows
    2019-02-01 13:57:40 DEBUG executing statements
    2019-02-01 13:57:40 DEBUG before nx_logqueue_push, size: 0
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG event added to jobqueue
    2019-02-01 13:57:40 DEBUG executing statements
    2019-02-01 13:57:40 DEBUG before nx_logqueue_push, size: 1
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG executing statements
    2019-02-01 13:57:40 DEBUG before nx_logqueue_push, size: 2
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG executing statements

    2019-02-01 13:57:40 DEBUG event added to jobqueue
    2019-02-01 13:57:40 DEBUG worker 1 processing event 0x7f7acc0176c0
    2019-02-01 13:57:40 DEBUG PROCESS_EVENT: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG om_tcp_write
    2019-02-01 13:57:40 DEBUG add socket [21]
    2019-02-01 13:57:40 DEBUG socket already added to pollset with reqevents [21 != 21]
    2019-02-01 13:57:40 DEBUG out get_next_logdata: got (queuesize: 25)
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG event added to jobqueue
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: MODULE_RESUME (dbi)
    2019-02-01 13:57:40 DEBUG event added to jobqueue
    2019-02-01 13:57:40 DEBUG executing statements
    2019-02-01 13:57:40 DEBUG worker 2 got signal for new job
    2019-02-01 13:57:40 DEBUG worker 2 got no event to process
    2019-02-01 13:57:40 DEBUG worker 2 waiting for new event
    2019-02-01 13:57:40 DEBUG worker 3 got signal for new job
    2019-02-01 13:57:40 DEBUG worker 3 got no event to process
    2019-02-01 13:57:40 DEBUG worker 3 waiting for new event
    2019-02-01 13:57:40 DEBUG before nx_logqueue_push, size: 26
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)
    2019-02-01 13:57:40 DEBUG executing statements
    2019-02-01 13:57:40 DEBUG before nx_logqueue_push, size: 27
    2019-02-01 13:57:40 DEBUG nx_event_to_jobqueue: DATA_AVAILABLE (out)

    And the next query after that WHERE id > -1 is this:

    2019-02-01 13:57:40 DEBUG im_dbi sql: SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord WHERE id > 761 LIMIT 50
    2019-02-01 13:57:40 DEBUG im_dbi read 29 rows

    And after that its the end of the database:

    2019-02-01 13:57:40 DEBUG im_dbi sql: SELECT id, discriminator, time, queryid, message, timestamprecord, response, memberclass, membercode, subsystemcode FROM logrecord WHERE id > 789 LIMIT 50
    2019-02-01 13:57:40 DEBUG im_dbi read 3 rows

  • b0ti's picture
    (NXLog)

    If you see data in the file the same should be sent out vie tcp. It does support multiple outputs.

    2019-02-01 13:57:40 DEBUG im_dbi read 29 rows

    The above indicates that the data is being read. You can confirm with tcpdump/wireshark that the data is sent over the network. Perhaps the receiver is discarding it for some reason (format?).

  • Zhengshi's picture
    (NXLog)

    2019-01-30 10:25:29 ERROR SSL error, failed to load ca cert from '/opt/nxlog/var/lib/nxlog/cert/agent-ca.pem', reason: No such file or directory, no such file, system lib

    This is likely from the included file log4ensics.conf where it tries to talk to an NXLog Manager server. CAFile %CERTDIR%/agent-ca.pem. If you are not going to connect to Manager, you can safely comment out the include %CONFDIR%/log4ensics.conf and uncomment #LogFile %MYLOGFILE%

    2019-01-30 10:25:30 WARNING already running as uid 0

    Sounds like you have User root in your config. You can comment out that line if you need to elevate privileges.

  • JaVa's picture

    Now the debug logs work when i did that "comment out the include %CONFDIR%/log4ensics.conf and uncomment #LogFile %MYLOGFILE%". Thanks. I will continue with the investigation of the problem.