Mailing List Archive

two questions
Hi folks!

I'm new on this list, and would like to ask two questions I can't solve.
I have to collect logs into a postgres db table. It already works. The 2
problems occured:
1, How can I rotate log tables? Is there an inbuilt solution to rename log
table at every midnights, or I have to do it from outside with a cron
script?
2, Every messages are logged into the database. I want to log local
messages to be logged as before into files, and ONLY the remotely generated
messages to be written into database. I configured tcp/5514 port for it,
and when I try it with "logger -P 5514 MESSAGETEXT" I can see in the table.
So if a message comes in through the tcp/5514 go to the database, every
other to the files as before.

thanks a lot
Istvan
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
1. As with dynamic files, rsyslog doesn't bother with rotating files and
any such maintenance (why should it? ;-)). So you can create a template
that inserts entries into a dynamically named table but removing old
tables is entirely up to you.

2. Just use a separate ruleset logging into a postgresql database and
tie it to the input on port 514. Like (very simple exapmple)

ruleset(name="log_to_pg") {

 action(type="ompgsql" [...])

}

input(type="imptcp" port="5514" ruleset="log_to_pg")

On 01.09.2021 14:14, Istvan Kassai via rsyslog wrote:
> Hi folks!
>
> I'm new on this list, and would like to ask two questions I can't solve.
> I have to collect logs into a postgres db table. It already works. The 2
> problems occured:
> 1, How can I rotate log tables? Is there an inbuilt solution to rename log
> table at every midnights, or I have to do it from outside with a cron
> script?
> 2, Every messages are logged into the database. I want to log local
> messages to be logged as before into files, and ONLY the remotely generated
> messages to be written into database. I configured tcp/5514 port for it,
> and when I try it with "logger -P 5514 MESSAGETEXT" I can see in the table.
> So if a message comes in through the tcp/5514 go to the database, every
> other to the files as before.
>
> thanks a lot
> Istvan
> _______________________________________________
> rsyslog mailing list
> https://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com/professional-services/
> What's up with rsyslog? Follow https://twitter.com/rgerhards
> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
For log "rotation" in Postgres you may want to look into postgres table
partitioning ( https://www.postgresql.org/docs/current/ddl-partitioning.html
) or even better Timescale ( https://www.timescale.com/ ) where timescale
will handle partitioning for you, both solutions offer a level of round
robin database features on top of SQL.

On Wed, Sep 1, 2021 at 8:23 AM Mariusz Kruk via rsyslog <
rsyslog@lists.adiscon.com> wrote:

> 1. As with dynamic files, rsyslog doesn't bother with rotating files and
> any such maintenance (why should it? ;-)). So you can create a template
> that inserts entries into a dynamically named table but removing old
> tables is entirely up to you.
>
> 2. Just use a separate ruleset logging into a postgresql database and
> tie it to the input on port 514. Like (very simple exapmple)
>
> ruleset(name="log_to_pg") {
>
> action(type="ompgsql" [...])
>
> }
>
> input(type="imptcp" port="5514" ruleset="log_to_pg")
>
> On 01.09.2021 14:14, Istvan Kassai via rsyslog wrote:
> > Hi folks!
> >
> > I'm new on this list, and would like to ask two questions I can't solve.
> > I have to collect logs into a postgres db table. It already works. The 2
> > problems occured:
> > 1, How can I rotate log tables? Is there an inbuilt solution to rename
> log
> > table at every midnights, or I have to do it from outside with a cron
> > script?
> > 2, Every messages are logged into the database. I want to log local
> > messages to be logged as before into files, and ONLY the remotely
> generated
> > messages to be written into database. I configured tcp/5514 port for it,
> > and when I try it with "logger -P 5514 MESSAGETEXT" I can see in the
> table.
> > So if a message comes in through the tcp/5514 go to the database, every
> > other to the files as before.
> >
> > thanks a lot
> > Istvan
> > _______________________________________________
> > rsyslog mailing list
> > https://lists.adiscon.net/mailman/listinfo/rsyslog
> > http://www.rsyslog.com/professional-services/
> > What's up with rsyslog? Follow https://twitter.com/rgerhards
> > NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
> DON'T LIKE THAT.
> _______________________________________________
> rsyslog mailing list
> https://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com/professional-services/
> What's up with rsyslog? Follow https://twitter.com/rgerhards
> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
> DON'T LIKE THAT.
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
Hi Mariusz!

Thanks for answering.
inline commented

Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
2021. szept. 1., Sze, 14:23):

> 1. As with dynamic files, rsyslog doesn't bother with rotating files and
> any such maintenance (why should it? ;-)).

Should or shouldn't I don't know. I used syslog-ng before, that rotated by
default. So I asked before I re-invented the hot water. :-)


> So you can create a template
> that inserts entries into a dynamically named table but removing old
> tables is entirely up to you.
>
How do you think the dynamically named table? Is there an example for it?
The only problem, if I dinamically refer to a table, the table has to be
exist. Should I create tables for a year forward if I want to log into
different tables all days?



>
> 2. Just use a separate ruleset logging into a postgresql database and
> tie it to the input on port 514. Like (very simple exapmple)
>
> ruleset(name="log_to_pg") {
> action(type="ompgsql" [...])
> }
>
> input(type="imptcp" port="5514" ruleset="log_to_pg")
>
> Yes, I use a similar ruleset. But local messages are mixed with remote
ones.
I saw in the documentation there are filtering capabilities in the config,
so I thought send only the messages from outside (from a specified host, or
through the 5514). But I found only filtering examples based on message
content:
for example: if $msg contains 'error' then /var/log/errlog
but not on source host.
Is there any documentation what kind of internal variables (as $msg in the
example) can I use in filter?


> On 01.09.2021 14:14, Istvan Kassai via rsyslog wrote:
> > Hi folks!
> >
> > I'm new on this list, and would like to ask two questions I can't solve.
> > I have to collect logs into a postgres db table. It already works. The 2
> > problems occured:
> > 1, How can I rotate log tables? Is there an inbuilt solution to rename
> log
> > table at every midnights, or I have to do it from outside with a cron
> > script?
> > 2, Every messages are logged into the database. I want to log local
> > messages to be logged as before into files, and ONLY the remotely
> generated
> > messages to be written into database. I configured tcp/5514 port for it,
> > and when I try it with "logger -P 5514 MESSAGETEXT" I can see in the
> table.
> > So if a message comes in through the tcp/5514 go to the database, every
> > other to the files as before.
> >
> > thanks a lot
> > Istvan
> > _______________________________________________
> > rsyslog mailing list
> > https://lists.adiscon.net/mailman/listinfo/rsyslog
> > http://www.rsyslog.com/professional-services/
> > What's up with rsyslog? Follow https://twitter.com/rgerhards
> > NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
> DON'T LIKE THAT.
> _______________________________________________
> rsyslog mailing list
> https://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com/professional-services/
> What's up with rsyslog? Follow https://twitter.com/rgerhards
> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
> DON'T LIKE THAT.
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
On 01.09.2021 15:08, Istvan Kassai via rsyslog wrote:
> Hi Mariusz!
>
> Thanks for answering.
> inline commented
>
> Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
> 2021. szept. 1., Sze, 14:23):
>
>> 1. As with dynamic files, rsyslog doesn't bother with rotating files and
>> any such maintenance (why should it? ;-)).
> Should or shouldn't I don't know. I used syslog-ng before, that rotated by
> default. So I asked before I re-invented the hot water. :-)

Well, I prefer the KISS approach, so I'd say that a solution should
stick to what it does best and let others do their work ;-)

>> So you can create a template
>> that inserts entries into a dynamically named table but removing old
>> tables is entirely up to you.
>>
> How do you think the dynamically named table? Is there an example for it?
https://www.rsyslog.com/doc/master/configuration/modules/ompgsql.html

As you can see in the Example 2, you can use a template to construct the
whole insert statement. You can then use any available property or
variable to dynamicaly generate the table name.

> The only problem, if I dinamically refer to a table, the table has to be
> exist. Should I create tables for a year forward if I want to log into
> different tables all days?
>
I'm afraid so.

>> 2. Just use a separate ruleset logging into a postgresql database and
>> tie it to the input on port 514. Like (very simple exapmple)
>>
>> ruleset(name="log_to_pg") {
>> action(type="ompgsql" [...])
>> }
>>
>> input(type="imptcp" port="5514" ruleset="log_to_pg")
>>

> Yes, I use a similar ruleset. But local messages are mixed with remote
> ones.

Show us the config :-) Notice that the ruleset is tied to a single input
(you can try to add stop after the action just for sure).


> I saw in the documentation there are filtering capabilities in the config,
> so I thought send only the messages from outside (from a specified host, or
> through the 5514). But I found only filtering examples based on message
> content:
> for example: if $msg contains 'error' then /var/log/errlog
> but not on source host.
> Is there any documentation what kind of internal variables (as $msg in the
> example) can I use in filter?

Sure. They are called properties.

https://www.rsyslog.com/doc/master/configuration/properties.html

But it's easier (and more effective, I believe) to tie proper rulesets
to proper outputs and just pass the event streams separately than to
engage rsyslog into filtering them.



_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
Hi Mariusz!

Finally I solved the main problem from another point-of-view. (I've a
little bit over-complicated :-) )
Filtered the messages, and only send to database the messages from remote
hosts with this match:
*if $hostname contains '.net.local' then
:ompgsql:127.0.0.1,postgres,password*
With this filter the db only contains the remote hosts' log records thanks
for the link you sent.

My last problem is, the two time columns (defined timestamp) in the db
table (receivedat, devicereportedtime) contains only YYYY-MM-DD HH:mm:SS.
The remote host sends time in a YYYY-MM-DD HH:mm:SS.XYZ, but if I extract
devicereportedtime, I can see .000 on the millisecond place, so the rsyslog
drops this info instead of sending to the db. (as it logs a realtime
software's logs, it would be important)

I updated the config with:

$template millisecFormat,"%timegenerated:1:10:date-rfc3339%
%timegenerated:12:24:date-rfc3339% %syslogtag%%msg%\n"
$ActionFileDefaultTemplate millisecFormat

Already in /var/log/messages file I can see record timestamps in
milliseconds format, but in the datamase remains the second resolution
format.
How can I tell rsyslog to sent the same date into the db as does to the
file?

thanks a lot!
Istvan





Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
2021. szept. 1., Sze, 15:28):

> On 01.09.2021 15:08, Istvan Kassai via rsyslog wrote:
> > Hi Mariusz!
> >
> > Thanks for answering.
> > inline commented
> >
> > Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
> > 2021. szept. 1., Sze, 14:23):
> >
> >> 1. As with dynamic files, rsyslog doesn't bother with rotating files and
> >> any such maintenance (why should it? ;-)).
> > Should or shouldn't I don't know. I used syslog-ng before, that rotated
> by
> > default. So I asked before I re-invented the hot water. :-)
>
> Well, I prefer the KISS approach, so I'd say that a solution should
> stick to what it does best and let others do their work ;-)
>
> >> So you can create a template
> >> that inserts entries into a dynamically named table but removing old
> >> tables is entirely up to you.
> >>
> > How do you think the dynamically named table? Is there an example for it?
> https://www.rsyslog.com/doc/master/configuration/modules/ompgsql.html
>
> As you can see in the Example 2, you can use a template to construct the
> whole insert statement. You can then use any available property or
> variable to dynamicaly generate the table name.
>
> > The only problem, if I dinamically refer to a table, the table has to be
> > exist. Should I create tables for a year forward if I want to log into
> > different tables all days?
> >
> I'm afraid so.
>
> >> 2. Just use a separate ruleset logging into a postgresql database and
> >> tie it to the input on port 514. Like (very simple exapmple)
> >>
> >> ruleset(name="log_to_pg") {
> >> action(type="ompgsql" [...])
> >> }
> >>
> >> input(type="imptcp" port="5514" ruleset="log_to_pg")
> >>
>
> > Yes, I use a similar ruleset. But local messages are mixed with remote
> > ones.
>
> Show us the config :-) Notice that the ruleset is tied to a single input
> (you can try to add stop after the action just for sure).
>
>
> > I saw in the documentation there are filtering capabilities in the
> config,
> > so I thought send only the messages from outside (from a specified host,
> or
> > through the 5514). But I found only filtering examples based on message
> > content:
> > for example: if $msg contains 'error' then /var/log/errlog
> > but not on source host.
> > Is there any documentation what kind of internal variables (as $msg in
> the
> > example) can I use in filter?
>
> Sure. They are called properties.
>
> https://www.rsyslog.com/doc/master/configuration/properties.html
>
> But it's easier (and more effective, I believe) to tie proper rulesets
> to proper outputs and just pass the event streams separately than to
> engage rsyslog into filtering them.
>
>
>
> _______________________________________________
> rsyslog mailing list
> https://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com/professional-services/
> What's up with rsyslog? Follow https://twitter.com/rgerhards
> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
> DON'T LIKE THAT.
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
Re: two questions [ In reply to ]
OK. So you updated the template for writing to the files.
You have to do similar thing to the template used with the ompgsql.

W dniu 12.09.2021 o 18:22, Istvan Kassai via rsyslog pisze:
> Hi Mariusz!
>
> Finally I solved the main problem from another point-of-view. (I've a
> little bit over-complicated :-) )
> Filtered the messages, and only send to database the messages from remote
> hosts with this match:
> *if $hostname contains '.net.local' then
> :ompgsql:127.0.0.1,postgres,password*
> With this filter the db only contains the remote hosts' log records thanks
> for the link you sent.
>
> My last problem is, the two time columns (defined timestamp) in the db
> table (receivedat, devicereportedtime) contains only YYYY-MM-DD HH:mm:SS.
> The remote host sends time in a YYYY-MM-DD HH:mm:SS.XYZ, but if I extract
> devicereportedtime, I can see .000 on the millisecond place, so the rsyslog
> drops this info instead of sending to the db. (as it logs a realtime
> software's logs, it would be important)
>
> I updated the config with:
>
> $template millisecFormat,"%timegenerated:1:10:date-rfc3339%
> %timegenerated:12:24:date-rfc3339% %syslogtag%%msg%\n"
> $ActionFileDefaultTemplate millisecFormat
>
> Already in /var/log/messages file I can see record timestamps in
> milliseconds format, but in the datamase remains the second resolution
> format.
> How can I tell rsyslog to sent the same date into the db as does to the
> file?
>
> thanks a lot!
> Istvan
>
>
>
>
>
> Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
> 2021. szept. 1., Sze, 15:28):
>
>> On 01.09.2021 15:08, Istvan Kassai via rsyslog wrote:
>>> Hi Mariusz!
>>>
>>> Thanks for answering.
>>> inline commented
>>>
>>> Mariusz Kruk via rsyslog <rsyslog@lists.adiscon.com> ezt írta (id?pont:
>>> 2021. szept. 1., Sze, 14:23):
>>>
>>>> 1. As with dynamic files, rsyslog doesn't bother with rotating files and
>>>> any such maintenance (why should it? ;-)).
>>> Should or shouldn't I don't know. I used syslog-ng before, that rotated
>> by
>>> default. So I asked before I re-invented the hot water. :-)
>>
>> Well, I prefer the KISS approach, so I'd say that a solution should
>> stick to what it does best and let others do their work ;-)
>>
>>>> So you can create a template
>>>> that inserts entries into a dynamically named table but removing old
>>>> tables is entirely up to you.
>>>>
>>> How do you think the dynamically named table? Is there an example for it?
>> https://www.rsyslog.com/doc/master/configuration/modules/ompgsql.html
>>
>> As you can see in the Example 2, you can use a template to construct the
>> whole insert statement. You can then use any available property or
>> variable to dynamicaly generate the table name.
>>
>>> The only problem, if I dinamically refer to a table, the table has to be
>>> exist. Should I create tables for a year forward if I want to log into
>>> different tables all days?
>>>
>> I'm afraid so.
>>
>>>> 2. Just use a separate ruleset logging into a postgresql database and
>>>> tie it to the input on port 514. Like (very simple exapmple)
>>>>
>>>> ruleset(name="log_to_pg") {
>>>> action(type="ompgsql" [...])
>>>> }
>>>>
>>>> input(type="imptcp" port="5514" ruleset="log_to_pg")
>>>>
>>
>>> Yes, I use a similar ruleset. But local messages are mixed with remote
>>> ones.
>>
>> Show us the config :-) Notice that the ruleset is tied to a single input
>> (you can try to add stop after the action just for sure).
>>
>>
>>> I saw in the documentation there are filtering capabilities in the
>> config,
>>> so I thought send only the messages from outside (from a specified host,
>> or
>>> through the 5514). But I found only filtering examples based on message
>>> content:
>>> for example: if $msg contains 'error' then /var/log/errlog
>>> but not on source host.
>>> Is there any documentation what kind of internal variables (as $msg in
>> the
>>> example) can I use in filter?
>>
>> Sure. They are called properties.
>>
>> https://www.rsyslog.com/doc/master/configuration/properties.html
>>
>> But it's easier (and more effective, I believe) to tie proper rulesets
>> to proper outputs and just pass the event streams separately than to
>> engage rsyslog into filtering them.
>>
>>
>>
>> _______________________________________________
>> rsyslog mailing list
>> https://lists.adiscon.net/mailman/listinfo/rsyslog
>> http://www.rsyslog.com/professional-services/
>> What's up with rsyslog? Follow https://twitter.com/rgerhards
>> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
>> of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
>> DON'T LIKE THAT.
> _______________________________________________
> rsyslog mailing list
> https://lists.adiscon.net/mailman/listinfo/rsyslog
> http://www.rsyslog.com/professional-services/
> What's up with rsyslog? Follow https://twitter.com/rgerhards
> NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.
>

_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE THAT.