<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hello,</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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></div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
I made some tests with the following commands:<br>
<br>
<blockquote style="margin-top:0;margin-bottom:0">-- create table scientist (id integer, firstname varchar(100), lastname varchar(100));
<div>-- insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');</div>
<div>-- insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');</div>
<div>-- insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');</div>
<div>-- select * from scientist;</div>
<div> </div>
<div>DROP TABLE "logs_192.168.75.102";</div>
<div>CREATE TABLE "logs_192.168.75.102" (datetime varchar(16), host varchar(32), program varchar(20), pid varchar(10), message varchar(800));</div>
<div>insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</div>
<div>insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</div>
<div>insert into "logs_192.168.75.102" (datetime, host, program, pid, message) values ('datetime', 'host', 'program', 'pid', 'message');</div>
<div>select * from "logs_192.168.75.102";</div>
</blockquote>
<div><br>
</div>
<div><br>
</div>
<div>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?)</div>
<div><br>
</div>
<div><br>
</div>
<div>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></div>
<div>It looks like, simply adding it to the template of the table option: <span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important">table("logs_${HOST}") --> <span style="background-color:rgb(255, 255, 255);display:inline !important">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></div>
<div><span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important"><span style="background-color:rgb(255, 255, 255);display:inline !important"><br>
</span></span></div>
<div><span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important"><span style="background-color:rgb(255, 255, 255);display:inline !important"><br>
</span></span></div>
<div><span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important"><span style="background-color:rgb(255, 255, 255);display:inline !important">Br,</span></span></div>
<div><span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important"><span style="background-color:rgb(255, 255, 255);display:inline !important">Laci</span></span></div>
<div><span style="color:rgb(32, 31, 30);font-family:Calibri, sans-serif;font-size:14.6667px;background-color:rgb(255, 255, 255);display:inline !important"><span style="background-color:rgb(255, 255, 255);display:inline !important"><br>
</span></span></div>
<div><br>
</div>
<div><br>
</div>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> syslog-ng <syslog-ng-bounces@lists.balabit.hu> on behalf of Herda, Martin <Martin.Herda@fritz-edv.de><br>
<b>Sent:</b> Tuesday, July 13, 2021 17:15<br>
<b>To:</b> 'syslog-ng@lists.balabit.hu' <syslog-ng@lists.balabit.hu><br>
<b>Subject:</b> [syslog-ng] Logging to postgresql - error creating table with IP</font>
<div> </div>
</div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif}
.x_MsoChpDefault
{font-family:"Calibri",sans-serif}
@page WordSection1
{margin:70.85pt 70.85pt 2.0cm 70.85pt}
div.x_WordSection1
{}
-->
</style>
<div lang="DE" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div style="background-color:#FFEB9C; width:100%; border-style:solid; border-color:#9C6500; border-width:1pt; padding:2pt; font-size:10pt; line-height:12pt; font-family:'Calibri'; color:Black; text-align:left">
<span style="color:#9C6500; font-weight:bold">CAUTION:</span> 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.</div>
<br>
<div>
<div class="x_WordSection1">
<p class="x_MsoNormal">Greetings to all,</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">Im on Debian 4.19.194-2 using syslog-ng 3.19.1</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">The .conf is set to send all incoming syslog packages into pg database:</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">destination d_pgsql {</p>
<p class="x_MsoNormal"> sql(type(pgsql)</p>
<p class="x_MsoNormal"> host("127.0.0.1") username("syslog")</p>
<p class="x_MsoNormal"> password("syslog") port("5432")</p>
<p class="x_MsoNormal"> database("db_syslog")</p>
<p class="x_MsoNormal"> table("logs_${HOST}")</p>
<p class="x_MsoNormal"> columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid varchar(10)", "message varchar(800)")</p>
<p class="x_MsoNormal"> values("$R_DATE", "${HOST}", "$PROGRAM", "$PID", "$MSG")</p>
<p class="x_MsoNormal"> indexes("datetime", "host", "program", "pid", "message"));</p>
<p class="x_MsoNormal">};</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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:</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal" 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))'</p>
<p class="x_MsoNormal" style="text-indent:35.4pt"> </p>
<p class="x_MsoNormal" style="text-indent:35.4pt">Error creating table, giving up; table='logs_192.168.75.102'</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">The issue seems to be the dot of the IP address.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">But dots in table names are possible AFAIK. Im able to manually create this table via HeidiSQL with no issues.</p>
<p class="x_MsoNormal"> </p>
<p class="x_MsoNormal">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?</p>
</div>
</div>
</div>
</body>
</html>