Learn ulogd - set ulogd with mysql

In this post, it looks like nothing. It was something fun for me. Because i was interested in shorewall and ulogd. So, I just thought how I keep log in mysql. This post wasn't value much, but helped me to learning something new.
I installed what packages were necessary (disabled iptables service). It was easy for you to use "yum" and "systemctl". First of all, I just ensured I have all packages.

[root@fedora ~]# yum list shorewall shorewall-core  ulogd ulogd-mysql mysql-server
Loaded plugins: langpacks, presto, refresh-packagekit
Installed Packages
mysql-server.i686                                                    5.5.24-1.fc17                                                      @updates
shorewall.noarch                                                     4.5.4-1.fc17                                                       @updates
shorewall-core.noarch                                                4.5.4-1.fc17                                                       @updates
ulogd.i686                                                           2.0.0-2.beta4.fc17                                                 @fedora
ulogd-mysql.i686                                                     2.0.0-2.beta4.fc17                                                 @fedora

In shorewall configuration. I just set policy for logging to NFLOG. If you are interested about it. You can read on http://www.shorewall.net/  (I did not emphasize how to configure shorewall, I just figured ulogd with mysql).

[root@fedora ~]# cat /etc/shorewall/policy
.
.
.
$FW            net                ACCEPT NFLOG
net            all                ACCEPT  NFLOG
all            all                REJECT  NFLOG

Then I configured ulogd with mysql.  
Note: mysql-ulogd2.sql file, If you downloaded "ulogd" from source. You will see it in compress file. 

[root@fedora ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database nulog;
Query OK, 1 row affected (0.00 sec)
mysql>mysql> grant all privileges on nulog.* to surachart@localhost identified by 'password';

Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 

[root@fedora ~]# mysql -h localhost -u surachart -p nulog
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source mysql-ulogd2.sql

After mysql no error, I configured ulogd.conf. 

[root@fedora ~]# cp /etc/ulogd.conf /etc/ulogd.conf-orig
[root@fedora ~]# vi /etc/ulogd.conf
[root@fedora ~]# diff /etc/ulogd.conf-orig /etc/ulogd.conf
45c45
< #plugin="/usr/lib/ulogd/ulogd_output_MYSQL.so"
---
> plugin="/usr/lib/ulogd/ulogd_output_MYSQL.so"
77c77
< #stack=log2:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL
---
> stack=log1:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL
166c166
< user="nupik"
---
> user="surachart"
168c168
< pass="changeme"
---
> pass="password"

In configuration file, I used  log1 in stack.
After I ensured configuration, that's fine. I just started ulogd and shorewall.

[root@fedora ~]# service ulogd start
Starting ulogd (via systemctl):                            [  OK  ]
[root@fedora ~]# service shorewall start
Redirecting to /bin/systemctl  start shorewall.service

[root@fedora ~]# /bin/systemctl  status  ulogd.service
ulogd.service - LSB: start and stop ulogd
          Loaded: loaded (/etc/rc.d/init.d/ulogd)
          Active: active (running) since Thu, 12 Jul 2012 15:10:28 +0700; 17min ago
         Process: 26609 ExecStop=/etc/rc.d/init.d/ulogd stop (code=exited, status=0/SUCCESS)
         Process: 26632 ExecStart=/etc/rc.d/init.d/ulogd start (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/ulogd.service
                  â”” 26638 /usr/sbin/ulogd -d
Jul 12 15:10:28 fedora.surachartopun.com ulogd[26632]: [33B blob data]
[root@fedora ~]# /bin/systemctl  status  shorewall.service
shorewall.service - Shorewall IPv4 firewall
          Loaded: loaded (/usr/lib/systemd/system/shorewall.service; enabled)
          Active: active (exited) since Thu, 12 Jul 2012 15:10:36 +0700; 17min ago
         Process: 26475 ExecStop=/sbin/shorewall $OPTIONS stop (code=exited, status=0/SUCCESS)
         Process: 26649 ExecStart=/sbin/shorewall $OPTIONS start (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/shorewall.service
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Martian Logging...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Proxy ARP...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Traffic Control...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Preparing iptables-restore input...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Running /sbin/iptables-restore...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: IPv4 Forwarding Enabled
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Processing /etc/shorewall/start ...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Processing /etc/shorewall/started ...
Jul 12 15:10:36 fedora.surachartopun.com logger[26915]: Shorewall started
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: done.

 [root@fedora ~]# iptables -LChain INPUT (policy DROP)
target     prot opt source               destination
net2fw     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain FORWARD (policy DROP)
target     prot opt source               destination
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain OUTPUT (policy DROP)
target     prot opt source               destination
fw2net     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain Broadcast (1 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type BROADCAST
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type MULTICAST
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type ANYCAST
DROP       all  --  anywhere             base-address.mcast.net/4
Chain Invalid (1 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ctstate INVALID
Chain NotSyn (1 references)
target     prot opt source               destination
DROP       tcp  --  anywhere             anywhere             tcpflags:! FIN,SYN,RST,ACK/SYN
Chain Reject (3 references)
target     prot opt source               destination
           all  --  anywhere             anywhere
reject     tcp  --  anywhere             anywhere             tcp dpt:auth /* --co */
Broadcast  all  --  anywhere             anywhere
ACCEPT     icmp --  anywhere             anywhere             icmp fragmentation-needed /* --comment */
ACCEPT     icmp --  anywhere             anywhere             icmp time-exceeded /* --comment */
Invalid    all  --  anywhere             anywhere
reject     udp  --  anywhere             anywhere             multiport dports epmap,microsoft-ds /* --c */
reject     udp  --  anywhere             anywhere             udp dpts:netbios-ns:netbios-ssn /* --c */
reject     udp  --  anywhere             anywhere             udp spt:netbios-ns dpts:1024:65535 /* --c */
reject     tcp  --  anywhere             anywhere             multiport dports epmap,netbios-ssn,microsoft-ds /* --c */
DROP       udp  --  anywhere             anywhere             udp dpt:ssdp /* --co */
NotSyn     tcp  --  anywhere             anywhere
DROP       udp  --  anywhere             anywhere             udp spt:domain /* --comment */
Chain dynamic (1 references)
target     prot opt source               destination
Chain fw2net (1 references)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
ACCEPT     all  --  anywhere             anywhere
Chain logdrop (0 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere
Chain logreject (0 references)
target     prot opt source               destination
reject     all  --  anywhere             anywhere
Chain net2fw (1 references)
target     prot opt source               destination
dynamic    all  --  anywhere             anywhere             ctstate INVALID,NEW
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
ACCEPT     all  --  anywhere             anywhere
Chain reject (9 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ADDRTYPE match src-type BROADCAST
DROP       all  --  base-address.mcast.net/4  anywhere
DROP       igmp --  anywhere             anywhere
REJECT     tcp  --  anywhere             anywhere             reject-with tcp-reset
REJECT     udp  --  anywhere             anywhere             reject-with icmp-port-unreachable
REJECT     icmp --  anywhere             anywhere             reject-with icmp-host-unreachable
REJECT     all  --  anywhere             anywhere             reject-with icmp-host-prohibited
Chain sfilter (0 references)
target     prot opt source               destination
LOG        all  --  anywhere             anywhere             LOG level info prefix "--log-prefix"
DROP       all  --  anywhere             anywhere
Chain shorewall (0 references)
target     prot opt source               destination

Test & Test. After that, I checked in mysql

mysql> SELECT tcp_dport,count(*) from view_tcp GROUP BY view_tcp.tcp_dport;
+-----------+----------+
| tcp_dport | count(*) |
+-----------+----------+
|        22 |        7 |
|        80 |        3 |
|       443 |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

It looked like work now. I plan to learn more about it.  
so far so good :)