<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p.xmsonormal, li.xmsonormal, div.xmsonormal
{mso-style-name:x_msonormal;
margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Thanks for your reply,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">I did indeed apply the single quotes and also tried with escaped double quotes. In either case the SQL statment string gets malformed to this:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"> CREATE TABLE _logs_192.168.75.130_<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Which will result in the same error as before:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='syslog', database='db_syslog', error='6819553: FEHLER:
</span>ERROR: Syntax error at <span style="mso-fareast-language:EN-US">».168«\x0aLINE 1: CREATE TABLE _logs_192.168.75.130_ (datetime varchar(16), ho...\x0a ^\x0a', query='CREATE TABLE _logs_192.168.75.130_ (datetime varchar(16),
host varchar(32), program varchar(20), pid varchar(10), message varchar(800))'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Error creating table, giving up; table='_logs_192.168.75.130_'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Br,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">MH<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Von:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> <b>
Im Auftrag von </b>Laszlo Szemere (lszemere)<br>
<b>Gesendet:</b> Dienstag, 13. Juli 2021 19:24<br>
<b>An:</b> Syslog-ng users' and developers' mailing list <syslog-ng@lists.balabit.hu><br>
<b>Betreff:</b> Re: [syslog-ng] Logging to postgresql - error creating table with IP<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Hello,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"> as a quick and dirty test I found an online Postgress test env. <a href="https://extendsclass.com/postgresql-online.html">https://extendsclass.com/postgresql-online.html</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">I made some tests with the following commands:<o:p></o:p></span></p>
<blockquote>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">-- create table scientist (id integer, firstname varchar(100), lastname varchar(100));
<o:p></o:p></span></p>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">-- insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">-- insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">-- insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">-- select * from scientist;<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">DROP TABLE "logs_192.168.75.102";<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">CREATE TABLE "logs_192.168.75.102" (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800));<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">select * from "logs_192.168.75.102";<o:p></o:p></span></p>
</div>
</blockquote>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">As it turned out, Postgres indeed accepts dots in the table name, however the SQL statement tourns out to be syntactically invalid without quoting it. (Please confirm:
Did you apply quotes during the manual test?)<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">Syslog-ng do not apply any (unnecessary?) quotes to the table name: <a href="https://github.com/syslog-ng/syslog-ng/blob/master/modules/afsql/afsql.c#L551">https://github.com/syslog-ng/syslog-ng/blob/master/modules/afsql/afsql.c#L551</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black">It looks like, simply adding it to the template of the table option: </span><span style="color:#201F1E;background:white">table("logs_${HOST}") --> table("'logs_${HOST}'")
should do the trick. However, I cannot test it right now. Sorry. (Please inform me if the problem persists, and I will investigate it further when I am back to my machine.)</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#201F1E;background:white">Br,</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#201F1E;background:white">Laci</span><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Arial",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
</div>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="divRplyFwdMsg">
<p class="MsoNormal"><b><span style="color:black">From:</span></b><span style="color:black"> syslog-ng <<a href="mailto:syslog-ng-bounces@lists.balabit.hu">syslog-ng-bounces@lists.balabit.hu</a>> on behalf of Herda, Martin <<a href="mailto:Martin.Herda@fritz-edv.de">Martin.Herda@fritz-edv.de</a>><br>
<b>Sent:</b> Tuesday, July 13, 2021 17:15<br>
<b>To:</b> 'syslog-ng@lists.balabit.hu' <<a href="mailto:syslog-ng@lists.balabit.hu">syslog-ng@lists.balabit.hu</a>><br>
<b>Subject:</b> [syslog-ng] Logging to postgresql - error creating table with IP</span>
<o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</div>
<div>
<div style="border:solid #9C6500 1.0pt;padding:2.0pt 2.0pt 2.0pt 2.0pt">
<p class="MsoNormal" style="line-height:12.0pt;background:#FFEB9C"><b><span style="font-size:10.0pt;color:#9C6500">CAUTION:</span></b><span style="font-size:10.0pt;color:black"> This email originated from outside of the organization. Do not follow guidance,
click links, or open attachments unless you recognize the sender and know the content is safe.<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="xmsonormal">Greetings to all,<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Im on Debian 4.19.194-2 using syslog-ng 3.19.1<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">The .conf is set to send all incoming syslog packages into pg database:<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">destination d_pgsql {<o:p></o:p></p>
<p class="xmsonormal"> sql(type(pgsql)<o:p></o:p></p>
<p class="xmsonormal"> host("127.0.0.1") username("syslog")<o:p></o:p></p>
<p class="xmsonormal"> password("syslog") port("5432")<o:p></o:p></p>
<p class="xmsonormal"> database("db_syslog")<o:p></o:p></p>
<p class="xmsonormal"> table("logs_${HOST}")<o:p></o:p></p>
<p class="xmsonormal"> columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid varchar(10)", "message varchar(800)")<o:p></o:p></p>
<p class="xmsonormal"> values("$R_DATE", "${HOST}", "$PROGRAM", "$PID", "$MSG")<o:p></o:p></p>
<p class="xmsonormal"> indexes("datetime", "host", "program", "pid", "message"));<o:p></o:p></p>
<p class="xmsonormal">};<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">When there is a hostname available, the table is created successfully. When hostname can not be resolved, IP Address is used instead. But in this case syslog-ng is unable to create a new table:<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal" style="text-indent:35.4pt">syslog-ng[499]: Error running SQL query; type='pgsql', host='127.0.0.1', port='5432', user='syslog', database='db_syslog', error='6819553: ERROR: Syntax error at ».168«\x0aLINE 1: CREATE TABLE logs_192.168.75.102
(datetime varchar(16), ho...\x0a ^\x0a', query='CREATE TABLE logs_192.168.75.102 (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800))'<o:p></o:p></p>
<p class="xmsonormal" style="text-indent:35.4pt"> <o:p></o:p></p>
<p class="xmsonormal" style="text-indent:35.4pt">Error creating table, giving up; table='logs_192.168.75.102'<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">The issue seems to be the dot of the IP address.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">But dots in table names are possible AFAIK. Im able to manually create this table via HeidiSQL with no issues.<o:p></o:p></p>
<p class="xmsonormal"> <o:p></o:p></p>
<p class="xmsonormal">Does the CREATE TABLE statement need to be escaped in some way? Or is there a way to replace the dots from the $HOST macro with underscores?<o:p></o:p></p>
</div>
</div>
</div>
</div>
</body>
</html>