Customer uses an SQL table in SQL Server as log source. He has truncated the log table in SQL Server and he restarted uid column from 1. Now, SSB can not fetch records from table due to value of $last_read_uid variable.
How can we reset $last_read_uid value of SQL sources?
We have tried unchecking and checking again "Read old records" box but it did not work.
Syslog-ng / ssb stores the latest read record's id in its persist file, like it stores for text file sources what was the last line it read from it.
When syslog-ng starts two things could happen based on the read old records setting:
That's why we have to reset this value manually in the persist file.
This operation needs the syslog-ng service to be stopped while you manipulate the persist file!
We can minimize the downtime with using a quite long "oneliner", but keep in mind it still need to be stopped!
Please check at the source definition if the "Read old records" option is checked. Otherwise even if we reset the number to 0, the syslog-ng service at start will gather the latest number (with a SELECT MAX(uid) FROM table; query) from that sql table, and start to query the newer records only.
Please log in to the core shell. From this point on everything we do should happen there.
First you have to found out what is your source's part in the persist file. Dump the persist file with the
/opt/syslog-ng/bin/persist-tool dump /opt/syslog-ng/var/syslog-ng.persist
command.
In my case the output was like this:
(core/master/ssb)root@ssb2:~# /opt/syslog-ng/bin/persist-tool dump /opt/syslog-ng/var/syslog-ng.persist
hostid = { "value": "2E FA 72 CE" }
pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "2" }
run_id = { "value": "06 00 00 00" }
In my case the sqlsource's name was psql, the host ip was 10.21.1.1, etc. So that is the line what I need to feed back to persist-tool, except I have to replace the last_read_id to 0.
To manipulate the persist file, you MUST stop the syslog-ng service!
To reduce downtime I used this long compound command in the shell:
systemctl stop syslog-ng.service && cp -a /opt/syslog-ng/var{,.bkp01} && echo 'pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "0" }' | /opt/syslog-ng/bin/persist-tool add -o /opt/syslog-ng/var - && systemctl start syslog-ng.service && echo Done.
So my terminal was look like this:
(core/master/ssb)root@ssb2:~# systemctl stop syslog-ng.service && cp -a /opt/syslog-ng/var{,.bkp01} && echo 'pgsql,10.21.1.1,5432,sqllog,logs = { "version": 0, "big_endian": false, "last_read_id": "0" }' | /opt/syslog-ng/bin/persist-tool add -o /opt/syslog-ng/var - && systemctl start syslog-ng.service && echo Done.
Escape character is '^d'
pgsql,10.21.1.1,5432,sqllog,logs OK
Done.
In your case, the echo command MUST be adjusted to your sql source's data. You can use the very same line what persist-tool's dump gave you, except do not forget to replace the uid from that stored number to 0.
In case anything would go wrong, I included a cp -a ... command into that line, so it will make a copy of /opt/syslog-ng/var into /opt/syslog-ng/var.bkp01
So, if anything problem happens, as a rollback procedure you can copy back the old persist file and start the service manually.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center