I can help you to write nginx access log to postgres. Lets go
Nginx log format
log_format logtodb '$time_iso8601;$http_host;$remote_addr;$http_x_forwarded_for;$request_method;$request;$status;$body_bytes_sent;$http_referer;$request_time;$upstream_http_x_cache;$uri;$upstream_addr;$host;$upstream_response_length;$upstream_status;$server_name;$newurl;$upstream_response_time';
access_log /var/log/nginx/access_logtodb.log logtodb;
Let's go.
Configure syslog-ng
nano /etc/syslog-ng/syslog.ng
#########################################################################
###### tailing nginx accesslog and sending to syslog-ng##################
source nginx_acceess_log { file( "/var/log/nginx/access_logtodb.log"
follow_freq(1)
flags(no-parse)
);
};
parser p_nginx_acceess_log {
csv-parser(columns("NGINX_TIME", "NGINX_http_host", "NGINX_remote_addr", "NGINX_http_x_forwarded_for", "NGINX_request", "NGINX_request_method","NGINX_status", "NGINX_body_bytes_sent", "NGINX_http_referer", "NGINX_request_time", "NGINX_upstream_http_x_cache", "NGINX_uri", "NGINX_upstream_addr", "NGINX_host", "NGINX_upstream_response_length", "NGINX_upstream_status", "NGINX_server_name", "NGINX_newurl", "NGINX_upstream_response_time")
flags(escape-double-char,strip-whitespace)
delimiters(";")
quote-pairs('""[]')
);
};
destination d_postgres_nginx_acceess_log{
sql(type(pgsql)
host("10.12.1.1") port("5432") username("postgres") password("A1s2gdfgdfgdgdfgd")
database("nginx_logs")
table("access_log_nginx_223")
columns("NGINX_TIME text", "NGINX_http_host text", "NGINX_remote_addr text", "NGINX_http_x_forwarded_for text", "NGINX_request_method text", "NGINX_request text", "NGINX_status varchar(3)", "NGINX_body_bytes_sent text", "NGINX_http_referer text", "NGINX_request_time text", "NGINX_upstream_http_x_cache text", "NGINX_uri text", "NGINX_upstream_addr text", "NGINX_host text", "NGINX_upstream_response_length text", "NGINX_upstream_status varchar(3)", "NGINX_server_name text", "NGINX_newurl text", "NGINX_upstream_response_time text")
values("${NGINX_TIME}", "${NGINX_http_host}", "${NGINX_remote_addr}", "${NGINX_http_x_forwarded_for}", "${NGINX_request_method}", "${NGINX_request}", "${NGINX_status}", "${NGINX_body_bytes_sent}", "${NGINX_http_referer}", "${NGINX_request_time}", "${NGINX_upstream_http_x_cache}", "${NGINX_uri}", "${NGINX_upstream_addr}", "${NGINX_host }", "${NGINX_upstream_response_length}", "${NGINX_upstream_status}", "${NGINX_server_name}", "${NGINX_newurl}", "${NGINX_upstream_response_time}")
indexes("NGINX_request", "NGINX_uri", "NGINX_server_name"));
};
log {source(nginx_acceess_log); parser(p_nginx_acceess_log); destination(d_postgres_nginx_acceess_log); };
Install packets for send to db
apt-get install libdbd-pgsql -y
Configure db-postgres
Create database nginx_logs. Grant acceess in pg_hba.conf
Okay, I found a way. It turns out the $ActionForwardDefaultTemplate by default is set to :
$ActionForwardDefaultTemplate RSYSLOG_ForwardFormat
Per this rsyslog documentation, the RSYSLOG_ForwardFormat is specifically used to maintain interoperability between different syslogs. No shocker then that if you modify this Forward template as I described originally, some functionality for other syslogs break:
$template MyTemplate, "%timestamp% <FQDN> %syslogtag%%msg%"
$ActionForwardDefaultTemplate MyTemplate
The workaround I found was to dig up the back-end template that RSYSLOG_ForwardFormat uses and mimic it. After digging through the source I eventually found that RSYSLOG_ForwardFormat is actually this:
"<%PRI%>%TIMESTAMP% %HOSTNAME% %syslogtag:1:32%%msg:::sp-if-no-1st-sp%%msg%"
So, if I create a custom template with almost the same content, but substitute my FQDN in place of the %HOSTNAME% macro, syslog-ng facility separation works properly and the system logs with FQDN:
$template MyForwardTemplate, "<%PRI%>%TIMESTAMP% <fqdn> %syslogtag:1:32%%msg:::sp-if-no-1st-sp%%msg%"
$ActionForwardDefaultTemplate MyForwardTemplate
Best Answer
I do not like the instructions on the opensystems blog you pointed to, as it replaces the default service in SMF. This is bad as it is likely that a future system patch will revert your changes. Here's my take on it, from a default system state:
svcadm disable system-log
http:// sunfreeware.com/programlistintel10.html#syslogng
Do not forget to also download and install its dependencies.
svccfg import /var/svc/manifest/site/syslog-ng.xmlsvcadm enable syslog-ngNow, your system is not vanilla if you followed the procedure on Open Systems blog. Here's what you need to do to revert:
Hope this helps.