====== 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:
<code sql>
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)
);
</code>

Spécifier le format des logs dans ''/etc/nginx/nginx.cong'':
<code nginx>
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"';
}
</code>

Il faut ensuite que toutes les directives ''access_log'' soient du format:
<code nginx>
access_log /path/log main;
</code>

Redémarrer Nginx.

Créer le fichier ''/etc/syslog-ng/conf.d/nginx.conf'':
<code systemverilog>
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); };
</code>

Arrêter ''syslog-ng''.

Tester que tout va bien avec la commande suivante:
<code bash>
syslog-ng -Fvde
</code>

Redémarrer ''syslog-ng''.