<div dir="ltr"><div><div><div>Hi,<br><br></div>I was thinking about a more generic approach, but this could solve your problem:<br><br><a href="https://github.com/balabit/syslog-ng/pull/1367">https://github.com/balabit/syslog-ng/pull/1367</a><br><br></div>This creates a create-statement-append() option to the sql destination where you can specify a string that gets appended to the CREATE SQL statement.<br><br></div>If it indeed solves your problem, I'd welcome feedback on the #1367 github issue. This is just a quick and dirty patch from me, without tests etc.<br><br></div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr">-- <br>Bazsi<br></div></div></div>
<br><div class="gmail_quote">On Mon, Mar 6, 2017 at 7:58 PM, Nik Ambrosch <span dir="ltr"><<a href="mailto:nik@ambrosch.com" target="_blank">nik@ambrosch.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word"><div>Table name (macros) are not the issue, here is how I’m currently creating a table:</div><div><br></div><div><div>destination mysql {</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>sql(type(mysql)</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>host("localhost") username("syslog") password("xxx")</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>database("syslog")</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>table("syslog_${R_YEAR}${R_<wbr>MONTH}${R_DAY}")</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>columns("id int(11) unsigned not null auto_increment primary key", "host varchar(40) not null", "facility varchar(20)", "priority varchar(10)", "level varchar(10)", "program text", "date date not null", "time time not null", "message text not null")</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>values("", "$FULLHOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$PROGRAM", "$R_YEAR-$R_MONTH-$R_DAY", "$R_HOUR:$R_MIN:$R_SEC", "$MSG")</div><div><span class="m_1466267748364585615Apple-tab-span" style="white-space:pre-wrap"> </span>indexes("id","host","priority"<wbr>));</div><div>};</div></div><div><br></div><div>This creates a table like this:</div><div><br></div><div><div>syslog_20170306 | CREATE TABLE `syslog_20170306` (</div><div> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,</div><div> `host` varchar(40) NOT NULL,</div><div> `facility` varchar(20) DEFAULT NULL,</div><div> `priority` varchar(10) DEFAULT NULL,</div><div> `level` varchar(10) DEFAULT NULL,</div><div> `program` text,</div><div> `date` date NOT NULL,</div><div> `time` time NOT NULL,</div><div> `message` text NOT NULL,</div><div> PRIMARY KEY (`id`),</div><div> KEY `syslog_20170306_id_idx` (`id`),</div><div> KEY `syslog_20170306_host_idx` (`host`),</div><div> KEY `syslog_20170306_priority_idx` (`priority`)</div><div>) ENGINE=MyISAM AUTO_INCREMENT=<a href="tel:(1)%20048%205923" value="+3610485923" target="_blank">10485923</a> DEFAULT CHARSET=latin1</div></div><div><br></div><div>That is the output from my old syslog server, the new will be using InnoDB tables, not MyISAM, as listed. If you notice the last parameters, starting with “ENGINE” - that is where the additional parameters can be added. in a compressed table you would append a ROW_FORMAT variable. Here is a little information if you’re curious:</div><div><br></div><div><a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html" target="_blank">https://dev.mysql.com/doc/<wbr>refman/5.7/en/innodb-row-<wbr>format-dynamic.html</a></div><div><a href="https://signalvnoise.com/posts/3571-scaling-your-database-via-innodb-table-compression" target="_blank">https://signalvnoise.com/<wbr>posts/3571-scaling-your-<wbr>database-via-innodb-table-<wbr>compression</a></div><span class=""><div><br></div><div><br></div><div><br></div><br><div><blockquote type="cite"><div>On Mar 6, 2017, at 1:46 PM, Fekete, Róbert <<a href="mailto:robert.fekete@balabit.com" target="_blank">robert.fekete@balabit.com</a>> wrote:</div><br class="m_1466267748364585615Apple-interchange-newline"><div><div dir="ltr"><div><div><div><div>Hi, <br><br></div>syslog-ng can automatically create tables if you use macros in the table names. <br></div>As for additional options, newer syslog-ng versions have a dbd-option() that can be used to set database options when syslog-ng connects the database (it might or might not be enough for your needs), see <a href="https://www.balabit.com/documents/syslog-ng-ose-latest-guides/en/syslog-ng-ose-guide-admin/html/reference-destination-sql.html" target="_blank">https://www.balabit.com/<wbr>documents/syslog-ng-ose-<wbr>latest-guides/en/syslog-ng-<wbr>ose-guide-admin/html/<wbr>reference-destination-sql.html</a><br><br></div>HTH, <br><br></div>Robert</div></div></blockquote></div></span></div><br>______________________________<wbr>______________________________<wbr>__________________<br>
Member info: <a href="https://lists.balabit.hu/mailman/listinfo/syslog-ng" rel="noreferrer" target="_blank">https://lists.balabit.hu/<wbr>mailman/listinfo/syslog-ng</a><br>
Documentation: <a href="http://www.balabit.com/support/documentation/?product=syslog-ng" rel="noreferrer" target="_blank">http://www.balabit.com/<wbr>support/documentation/?<wbr>product=syslog-ng</a><br>
FAQ: <a href="http://www.balabit.com/wiki/syslog-ng-faq" rel="noreferrer" target="_blank">http://www.balabit.com/wiki/<wbr>syslog-ng-faq</a><br>
<br>
<br></blockquote></div><br></div>