# Nftables + ulogd2 + mysql

**URL:** <https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393>\
**Category:** Support Debian\
**Created:** [Octobre 14, 2019, 3:06 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393 "2019-10-14T15:06:59Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![didebian](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/d/c5a1d2/32.png) [@didebian](https://www.debian-fr.org/u/didebian)\
**Post date:** [Octobre 14, 2019, 3:07 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/1 "2019-10-14T15:07:00Z")

</div>

Bonjour

Après la mise a jour de mon serveur vers Buster, j’ai dû passer par NFTABLES.  
Puis configurer ULOGD2 pour pousser les logs FW vers MYSQL.

Donc le fichier ulogd.conf :

```nohighlight
[global]
loglevel=3
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_NFLOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_ULOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inpflow_NFCT.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IFINDEX.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2STR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2BIN.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2HBIN.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_PRINTPKT.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_HWHDR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_PRINTFLOW.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_MARK.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_SYSLOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_MYSQL.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_raw2packet_BASE.so"

stack=log2:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL

hash_enable=0

[mysql1]
db="ULOG"
host="localhost"
user="XXXXXX"
table="ulog"
pass="XXXXXX!"
procedure="INSERT_PACKET_FULL"

```

J’ai créé les tables via le SQL Schema de **mysql-ulogd2.sql**.

J’ai bien la base qui est alimentée mais pour lire les IP au format binaire dans la base SQL : SELECT INET\_NTOA(CONV(ip\_daddr, 2, 10)) FROM view\_tcp; retourne **0.0.0.0**  
le format HEX: **SELECT HEX( ip\_saddr\_bin ) FROM ulog;**  
00000000000000000000FFFF5C355AB6  
00000000000000000000FFFFB869F7EE

C’est ici que commence les questions:  
Peut on changer le format des IP address ?

```nohighlight
| ip_saddr_bin | binary(16) | YES | | NULL | |
| ip_daddr_bin | binary(16) | YES | | NULL | |

```

Comment peut on verifier que les IP sont justes ?

En fait je crois que j’aimerais echanger avec quelqu’un ayant configurer nftabloes,ulogd2 et mysql

---

<div class="post-metadata">

**Author:** ![didebian](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/d/c5a1d2/32.png) [@didebian](https://www.debian-fr.org/u/didebian)\
**Post date:** [Octobre 15, 2019, 2:30 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/2 "2019-10-15T14:30:59Z")

</div>

Je me reponds a moi même:

```nohighlight
SELECT RIGHT(HEX(ip_saddr_bin),8) from ma_table

```

pour avoir les 8 dernier DIGIT, puis

```nohighlight
select count(inet_ntoa(conv(SADDR, 16, 10))) 

```

pour avoir un format d’addresses VARCHAR lisibles par des humains

---

<div class="post-metadata">

**Author:** ![didebian](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/d/c5a1d2/32.png) [@didebian](https://www.debian-fr.org/u/didebian)\
**Post date:** [Octobre 15, 2019, 2:32 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/3 "2019-10-15T14:32:29Z")

</div>

Quelqu’un auriat il quelques conseilles pour utiliser un analyser de log FW (NULOGS semblant abandonné)…analyser generant des rapport HTML ?

---

<div class="post-metadata">

**Author:** ![binakh](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/b/ecccb3/32.png) [@binakh](https://www.debian-fr.org/u/binakh)\
**Post date:** [Octobre 25, 2019, 8:18 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/4 "2019-10-25T08:18:15Z")

</div>

Bonjour,  
tu peux utiliser webfwlog ([http://www.webfwlog.net/](http://www.webfwlog.net/)), c’est le meilleur analyseur jusqu’a maintenant.  
jai un probleme de configurer ulogd2 pour loger to mysql. est ce que tu peux mevoyer le fichier de configuration ulog.conf et le fichier de base de donnees sql ?  
Merci

---

<div class="post-metadata">

**Author:** ![didebian](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/d/c5a1d2/32.png) [@didebian](https://www.debian-fr.org/u/didebian)\
**Post date:** [Octobre 25, 2019, 10:16 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/5 "2019-10-25T10:16:03Z")

</div>

Merci de cette reponse  
Finalement je vais m’orienter vers un Dev personnel car je ne trouve rien que me convienne vraiment.  
Concernant les fichier de conf, voici ce que j’ai (le fichier de base SQL est dans le “/usr/share/doc/ulogd2”) :

```nohighlight
#Les PLUGINS (il yb a certainement du tri a faire)

plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_NFLOG.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_ULOG.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inppkt_UNIXSOCK.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inpflow_NFCT.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IFINDEX.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2STR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2BIN.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_IP2HBIN.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_PRINTPKT.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_HWHDR.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_PRINTFLOW.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_filter_MARK.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_LOGEMU.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_SYSLOG.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_XML.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_SQLITE3.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_GPRINT.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_NACCT.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_PCAP.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_PGSQL.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_MYSQL.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_DBI.so"
plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_raw2packet_BASE.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_inpflow_NFACCT.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_GRAPHITE.so"
#plugin="/usr/lib/x86_64-linux-gnu/ulogd/ulogd_output_JSON.so"

```

```nohighlight
#Mon STACK
# this is a stack for logging packet to MySQL
stack=log2:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL

```

```nohighlight
#Confguration de connexion à la base
[mysql1]
db="MA_DATABASE"
host="localhost"
user="MON_USER"
table="ulog"
pass="MON_PASSWORD"
procedure="INSERT_PACKET_FULL"
#procedure="INSERT_BYTES"
#procedure="INSERT_CT"
# backlog configuration:
# set backlog_memcap to the size of memory that will be
# allocated to store events in memory if data is temporary down
# and insert them when the database came back.
#backlog_memcap=1000000
# number of events to insert at once when backlog is not empty
#backlog_oneshot_requests=10

```

```nohighlight
[log2]
# netlink multicast group (the same as the iptables --nflog-group param)
group=2 # Group has to be different from the one use in log1

```

Et la regle pour loguer le traffic droppé

```nohighlight
log group 2

```

---

<div class="post-metadata">

**Author:** ![binakh](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/b/ecccb3/32.png) [@binakh](https://www.debian-fr.org/u/binakh)\
**Post date:** [Décembre 29, 2019, 7:17 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/6 "2019-12-29T07:17:18Z")

</div>

Merci infiniment, je viens de voir ta reponse.  
J ai deja configurer Ulogd pour logger a Mysql.  
webfwlog ne passe pas a ce que tu voulais ?

---

<div class="post-metadata">

**Author:** ![didebian](https://www.debian-fr.org/letter_avatar_proxy/v4/letter/d/c5a1d2/32.png) [@didebian](https://www.debian-fr.org/u/didebian)\
**Post date:** [Décembre 29, 2019, 7:35 UTC](https://www.debian-fr.org/t/nftables-ulogd2-mysql/80393/7 "2019-12-29T07:35:17Z")

</div>

Bonjour

Non je ne vais pas utiliser de Web log car ça ne repond pas a mon besoin.  
Si besoin je me ferais une interface web (php/mariadb) mais pour le moment je n’en ai pas encore l’utilité  
Bonne continuation