Stocker les logs Nginx en base de données

• https://serverfault.com/questions/448140/syslog-ng-and-nginx-logs-to-mysql
• https://phelepjeremy.wordpress.com/2017/06/20/configuration-dun-serveur-syslog-ng/
• https://www.koorka.com/wiki/How_to_configure_syslog-ng_as_nginx_access_log_server
• https://stackoverflow.com/questions/21135719/full-record-url-in-nginx-log

Installer syslog-ng et libdbd-mysql.

Créer un utilisateur MySQL et une base de données. Y créer la table suivante:

CREATE TABLE access_log (
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   unixtime INT UNSIGNED,
   YEAR YEAR(4),
   MONTH INT,
   DAY INT,
   fulldate TIMESTAMP,
   remote_addr VARCHAR(50),
   remote_user VARCHAR(100),
   host VARCHAR(100),
   request_method VARCHAR(20),
   request_uri VARCHAR(2048),
   STATUS VARCHAR(10),
   body_bytes_send INT UNSIGNED,
   request_time FLOAT,
   http_referrer VARCHAR(2048),
   http_user_agent VARCHAR(500),
   http_x_forwareded_for VARCHAR(50),
   INDEX index_access_log_unixtime(unixtime),
   INDEX index_access_log_month(MONTH),
   INDEX index_access_log_day(DAY)
);

Spécifier le format des logs dans /etc/nginx/nginx.cong:

http {
    log_format  main '$remote_addr $remote_user [$time_local] "$host" $request_method "$request_uri" '
                     '$status $body_bytes_sent $request_time "$http_referer" '
                     '"$http_user_agent" "$http_x_forwarded_for"';
}

Il faut ensuite que toutes les directives access_log soient du format:

access_log /path/log main;

Redémarrer Nginx.

Créer le fichier /etc/syslog-ng/conf.d/nginx.conf:

source s_nginx {
    # il est possible de spécifier plusieurs fichiers, juste dupliquer la ligne suivante:
    file("/var/log/nginx/access.log" flags(no-parse));
};
 
parser p_nginx {
    csv-parser(
       columns("REMOTE_ADDR", "REMOTE_USER", "TIME_LOCAL", "$HTTP_HOST", "REQUEST_METHOD", "REQUEST_URI", 
               "STATUS", "BODY_BYTES_SEND", "REQUEST_TIME", 
               "HTTP_REFERER","HTTP_USER_AGENT", "HTTP_X_FORWARDED_FOR")
       flags(escape-double-char,strip-whitespace)
       delimiters(" ")
       quote-pairs('""[]')
     );
};
 
destination d_sql {
      sql(
          type(mysql)
          host("localhost")
          port(3306)
          username("nginx")
          password("yourpassword")
          database("nginx_logs")
          table("access_log")
          columns("id int unsigned auto_increment primary key",
                  "unixtime int unsigned",
                  "year year(4)",
                  "month int",
                  "day int",
                  "fulldate timestamp",
                  "remote_addr varchar(50)",
                  "remote_user varchar(100)",
                  "host varchar(100)",
                  "request_method varchar(20)",
                  "request_uri varchar(2048)",
                  "status varchar(10)",
                  "body_bytes_send int unsigned",
                  "request_time float",
                  "http_referrer varchar(2048)",
                  "http_user_agent varchar(500)",
                  "http_x_forwareded_for varchar(50)")
          values(default,"${UNIXTIME}", "${YEAR}", "${MONTH}", "${DAY}", "${YEAR}-${MONTH}-${DAY} ${HOUR}-${MIN}-${SEC}",
               "${REMOTE_ADDR}","${REMOTE_USER}","${HTTP_HOST}","${REQUEST_METHOD}","${REQUEST_URI}",
               "${STATUS}","${BODY_BYTES_SEND}","${REQUEST_TIME}",
               "${HTTP_REFERER}","${HTTP_USER_AGENT}","${HTTP_X_FORWARDED_FOR}")
          indexes("unixtime")
          indexes("month")
          indexes("day")
          null("-")
        );
};
 
log { source(s_nginx); parser(p_nginx); destination(d_sql); };

Arrêter syslog-ng.

Tester que tout va bien avec la commande suivante:

syslog-ng -Fvde

Redémarrer syslog-ng.