3r33535. 3r3-31. Good day.
 3r33535.
 3r33535. I want to tell you how easy and easy it is to configure a network traffic metadata collection server for Mikrotik routers.
 3r33535.
 3r33535. Target: 3r3492. The goal will be to store the “chewed” firewall logs in the database for further analysis.
 3r33535.
 3r33535. Means: 3r3492. Any fresh Linux distribution with rsyslogd v8 and higher will work for implementation, perhaps the proposed syntax will work on v7. We also need a DBMS, I chose mariadb. Database growth will vary depending on the number of journal rules, because the size of the drive is at your discretion, in my case 30-40 rules are logged, which is about 1200 thousand lines per day. For the month of using the database, including indexes, it has grown to 3.8GB.
 3r33535.
 3r33535. Mechanics: The router sends a log to the remote server via UDP. The rsyslog server, using regular expressions, cleans up lines from unnecessary information, generates an SQL insert and sends it to the DBMS. The DBMS, with the help of a trigger before insertion, performs additional cleaning and partitioning of fields that could not be parsed in rsyslog.
 3r33535. Here 3r33512. there is a tool that allows you to debug regulars, try maybe you can separate the port from the address, as well as the interface name from in: and out :. Just keep in mind that some sport and dport protocols are missing.
 3r33535.
 3r33535. In general, my output was: 3r3509.  3r33535.
 3r33535.
3r33312. 20180927155341 in: ether6 out: VLAN???.???:???.???:???: 15: 17: 31: b8: d7 TCP forward BLOCKSMKNETS 60 3r33434.
 3r33535.
Here 3r33512. There is documentation on how to prepare rsyslog regulars.
 3r33535.
 3r33535. In the final form, the log receive configuration file from Mikrotik /etc/rsyslog.d/20-remote.conf will look like this:
 3r33535.
 3r33535.
3r33312. $ template tpl_traflog, "insert into traflog.traffic (datetime, inif, outif, src, dst, smac, proto, chain, logpref, len) values ​​('% timereported ::: date-mysql%', '% msg: R , ERE, ? BLANK, 0: in:[a-zA-Z]+ W2w2w251. + | In: <[a-zA-Z]+ -[a-zA-Z]+> - end% ','% msg: R, ERE, ? BLANK, 0: out :[a-zA-Z]+[0-9]+ | out: <[a-zA-Z]+ -[a-zA-Z]+> - end% ','% msg: R, ERE, ? BLANK, 0 :([0-9]+.) {3}[0-9]+[:]? ([0-9]+)? - end% ','% msg: R, ERE, ? BLANK, 1 :([0-9]+.) {3}[0-9]+[:]? ([0-9]+) ? - end% ','% msg: R, ERE, ? BLANK :([0-f]+:) {5}[0-f]+ - end% ','% msg: R, ERE, ? DFLT: b[A-X]{?4} b - end% ','% msg: R, ERE, ? BLANK:[a-x]+ - end% ','% msg: F, 32: 2% ','% msg : R, ERE, ? BLANK:[0-9]+ $ - end% ') ", SQL 3r3523. if ($ from host-ip == '???.230') then {action (type = "ommysql" server = "localhost" serverport = "3306" db = "traflog" uid = "rsyslogger" pwd = "" template = "tpl_traflog") stop}
3r33434.
 3r33535. In the first line of the description of the template (template) - a string of SQL code to transfer it to the database.
 3r33535. The second line is the condition when the action will take place, that is, the record in the DBMS.
 3r33535. The condition looks like this: if the source of the log = ???.230 (3r-3312. If ($ fromhost-ip == '???.230') 3r-?63467.), Then use the ommysql module with connection parameters ( Then {action (type = "ommysql "server = localhost" serverport = "3306" db = "traflog" uid = "rsyslogger" pwd = "" ) call the tpl_traflog template ( template = "tpl_traflog") ), and then stop further processing of the line ( stop} ).
 3r33535.
 3r33535. It is possible that something will go wrong in your case, it may be due to interface names or log prefixes, maybe something else. To debug, let's do the following, comment the second line, add a new template and two new conditions: 3r35050.  3r33535.
 3r33535.

  3r33312. $ template tpl_traflog_test, "% timereported ::: date-mysql%",% msg: R, ERE, ? BLANK, 0: in:[a-zA-Z]+[0-9]+ | in: <[a-zA-Z]+ -[a-zA-Z]+ > - end% ','% msg: R, ERE, ? BLANK, 0: out:[a-zA-Z]+[0-9]+ | out: <[a-zA-Z]+ -[a-zA-Z]+> - end% ','% msg : R, ERE, ? BLANK, 0 :([0-9]+.) {3}[0-9]+ W2w2w235.? (W2w2w251. +)? - end% ','% msg: R, ERE, ? BLANK, 1 :([0-9]+.) {3}[0-9]+ W2w2w235.? (W2w2w251. +)? - end% ','% msg: R, ERE, ? BLANK :([0-f]+:) {5}[0-f]+ - end% ','% msg: R, ERE, ? BLANK: b[A-X]{?4} b - end% ','% msg: R, ERE, ? BLANK:[a-x]+ - end% ','% msg: F, 32: 2% ','% msg: R, ERE, ? BLANK:[0-9]+ $ - end% '"3r3233523. if ($ fromhost-ip == '???.230') then {action (type = "omfile" file = "/var /log /remote /???.230.log")}
if ($ from host-ip == '???.230') then {action (type = "omfile" file = "/var /log /remote /???.230.log" template = "tpl_traflog_test") stop}    3r33434.  
   3r33535. Restart the logger.  
   3r33535.  
   3r33535. The tpl_traflog_test template is similar to tpl_traflog but without SQL INSERT.  
   3r33535.  
   3r33535. The first condition adds the unprocessed line% msg% to the /var/log/remote/???.230.log file.  
   3r33535.  
   3r33535. The second condition adds the processed string to the same file. So it will be more convenient to compare.  
   3r33535. Next, prepare the database.  
   3r33535.  
   3r33535. 3r3165.    We are preparing the database    3r3168.  
   3r33535. Setting the DBMS is omitted, everything is standard here.  
   3r33535.  
   3r33535. We start the mysql console and execute the following code:  
   3r33535.  
   3r33535.  

    --Add a database
create database traflog character set utf8 collate utf8_bin; 3r33535. use traflog; 3r33535. 3r33535. --add a table
create table traffic id 3r33523. Dst VARCHAR (21), 3r33535. Dport INT (5), 3r3-33523. Smac VARCHAR (17), 3r33535. Proto VARCHAR (4), 3r33535. Chain VARCHAR (8), 3r3353523. len INT (5)) ENGINE = MYISAM; 3r33535. 3r33535. - add user
create user rsyslogger @ localhost identified by ''; 3r33535. grant all privileges on traflog. * to rsyslogger @ localhost; 3r33535.    3r33434.  
   3r33535. The table is ready, the user is.  
   3r33535.  
   3r33535. Now we add a trigger, it will do what the logger failed, it will separate the address from the port and the names of the interfaces:  
   3r33535.  
   3r33535.  

    - add trigger 3r32323. DELIMITER //
create TRIGGER delim_ip_port BEFORE insert ON traffic
FOR EACH ROW
begin
set NEW.inif = REGEXP_REPLACE ((NEW.inif), 'in:', ''); 3r33535. set NEW.outif = REGEXP_REPLACE ((NEW.outif), 'out:', ''); 3r33535. set NEW.sport = REGEXP_REPLACE ((NEW.src), '([0-9]+.) {3}[0-9]+: | (([0-9]+.) {3}[0-9]+', ''); 3r33535. set NEW.src = REGEXP_REPLACE ((NEW.src), ':[0-9]+', ''); 3r33535. set NEW.dport = REGEXP_REPLACE ((NEW.dst), '([0-9]+.) {3}[0-9]+: | (([0-9]+.) {3}[0-9]+', ''); 3r33535. set NEW.dst = REGEXP_REPLACE ((NEW.dst), ':[0-9]+', ''); 3r33535. end //3r32323. delimiter;    3r33434.  
   3r33535. REGEXP_REPLACE searches for the second after comma parameter (regular) and replaces it with the third parameter, in our case there is nothing in quotes, so it simply removes what it found.  
   3r33535.  
   3r33535. Let's make a test insert, in the same way as a logger will do:  
   3r33535.  
   3r33535.  

    --insert the test line
insert into traffic (datetime, inif, outif, src, dst, smac, proto, chain, logpref)
values ​​(2018073007543? 'in: ether6', 'out: VLAN55', '???.234:4997', '???.18:65535', '00: 15: 17: 31: b8: d7 ',' TCP ', 'forward', 'BLOCKSMKNETS');    3r33434.  
   3r33535. Let's see what happened:  
   3r33535.  
   3r33535.  

    select * from tarffic;    3r33434.  
   3r33535. If everything is correct, then go ahead. If not, look for what is the error.  
   3r33535.  
   3r33535. Add at least one index. I am not a wizard to create indexes, but as I understand it, in mysql for different queries it is more correct to use indexes with different junction fields, since one query can use only one index (or am I wrong?). If you understand, do at your discretion. For an example, that's enough:  
   3r33535.  
   3r33535.  

    - Supplementing the index
create index traffic_index on traffic (src, dst, dport, datetime);    3r33434.  
   3r33535. Is done.  
   3r33535.  
   3r33535. Now you need to start sending on the router, add the settings of the remote log server and the action to it, add the log option to one of the firewall rules, add the prefix no more than 24 characters.  
   3r33535.  
   3r33535. In a micro console, it looks like this: 3r3509.  3r33535.  
   3r33535.  

  3r33312. /system logging action
set 3 remote = ???.94 src-address = ???.230 3r3353523. add name = remote2 remote = ???.19 syslog-facility = local6 target = remote
/system logging
add action = remote topics = error, account, critical, event, info
add action = remote2 topics = firewall
3r33535. /ip firewall filter
3r33523. add action = drop chain = input comment = "drop ssh brute forcers" dst-port = 2?8291 log = yes log-prefix = DROP_SSH_BRUTE protocol = tcp src-address-list = ssh_blacklist
3r33523.    3r33434.  
   3r33535. Where ???.230 is the address of the router, ???.19 is the address of the server log for the firewall logs, and ???.94 is another log server, my microtic system logs fall there, we do not need it now. Our setting is remote2.  
   3r33535.  
   3r33535. Further look that falls in the file:  
   3r33535.  
   3r33535.  

  3r33312. tail -f /var/log/remote/???.230.log    3r33434.  
   3r33535. The file should be sprinkled with lines from the router, unless of course your rule works often enough.  
   3r33535.  
   3r33535. If there are not enough fields, that is, the datetime, inif, outif, src, dst, smac, proto, chain, logpref, len sequence is not observed, then you can try changing the parameter in the debugging templates of the logger, replacing BLANK with DLFT. Then, instead of the emptiness of any field, some letters will appear,nude already what. If this happens, then something is wrong with the regular season and it should be corrected.  
   3r33535.  
   3r33535. If everything went as it should, then disable the test conditions and the template.  
   3r33535.  
   3r33535. Also, the default config in /etc/rsyslog.d/needs to be lowered below, I renamed it to 50-default.conf, so that remote logs are not dumped into the system log /var /log /message  
   3r33535. Restart the logger.  
   3r33535.  
   3r33535. Let's wait a bit until our database is full. Then we can start the sample.  
   3r33535.  
   3r33535.    A few queries for example:    
   3r33535.  
   3r33535. 3r33434. 3r33434. To see the size of the database and the number of rows:    3r33434.  

    MariaDB[traflog]> select table_schema as "database", round (sum (data_length + index_length) /1024 /102?2) as "size Mb", TABLE_ROWS as "count rows" from information_schema.tables group by table_schema; + -------------------- + --------- + ------------ +
| database | size Mb | count rows | 3r33535. + -------------------- + --------- + ------------ +
| information_schema | ??? | NULL | 3r33535. | traflog | ??? | 21839553 | 3r33535. + -------------------- + --------- + ------------ +
2 rows in set (??? sec)
  3r33434.  
   3r33535. Over the month, almost 4GB has grown, but it depends on the number and properties of the logged firewall rules  
   3r33535. 3r? 3519. 3r? 3519.  
   3r33535. 3r33434. 3r33434. The number of logged prefixes    3r33434. The number of logged prefixes is not equal to the number of rules, some rules work with one prefix, but still how many total prefixes? and how many rules are worked out for them ?:  
   3r33535.  
   3r33535.  

    MariaDB[traflog]> select logpref, count (logpref) from traffic group by logpref order by count (logpref) desc; 3r33535. + ---------------------- + ---------------- +
| logpref | count (logpref) | 3r33535. + ---------------------- + ---------------- +
| ACCEPT_TORF_INET | 14582602 | 3r33535. | ACCEPT_SMK_PPP | 1085791 | 3r33535. | DROP_FORWARD_INVALID | 982374 | 3r33535. | REJECT_BNK01 | 961503 | 3r33535. | ACCEPT_MMAX_TORF | 802455 | 3r33535. | ACCEPT_TORF_PPP | 736803 | 3r33535. | SMTP_DNAT | 689533 | 3r33535. | ACCEPT_SMK_INET | 451411 | 3r33535. | ACCEPT_INET_TORF | 389857 | 3r33535. | BLOCK_SMKNETS | 335424 | 3r33535. | DROP_SMTP_BRUTE | 285850 | 3r33535. | ACCEPT_ROZN_TORF | 154811 | 3r33535. | ACCEPT_TORF_MMAX | 148393 | 3r33535. | DROP_ETHALL_ETHALL | 80679 | 3r33535. | ACCEPT_SMTP | 48921 | 3r33535. | DROP_SMTP_DDOS | 32190 | 3r33535. | RDP_DNAT | 28757 | 3r33535. | ACCEPT_TORF_ROZN | 18456 | 3r33535. | SIP_DNAT | 15494 | 3r33535. | 1CWEB_DNAT | 6406 | 3r33535. | BLOCKSMKNETS | 5789 | 3r33535. | DROP_SSH_BRUTE | 3162 | 3r33535. | POP_DNAT | 1997 | 3r33535. | DROP_RDP_BRUTE | 442 | 3r33535. | DROP_BNK01 | 291 | 3r33535. | DROPALL | 138 | 3r33535. | ACCEPT_RTP_FORWARD | 90 | 3r33535. | REJECT_SMTP_BRUTE | 72 | 3r33535. | L2TP_INPUT_ACCEPT | 33 | 3r33535. + ---------------------- + ---------------- +
29 rows in set (2 min ??? sec)    3r33434.  
   3r33535. ACCEPT_TORF_INET is in the lead, by this prefix you can find everyone who went to the Internet from our local network, the protocols and ports are recorded, the time will come and access will be closed to some people. There is reference data for future work on the bugs.  
   3r33535. 3r? 3519. 3r? 3519.  
   3r33535. 3r33434. 3r33434. The leader smtp poke  [/b]  3r33434. Let's see who was trying to get to the smtp server today:  
   3r33535.  
   3r33535.  

    MariaDB[traflog]> select src, count (dport) from traffic where logpref = 'SMTP_DNAT' and datetime> '2018101600000000' group by src order by count (dport) desc limit 10; 3r33535. + ---------------- + -------------- +
| src | count (dport) | 3r33535. + ---------------- + -------------- +
| ???.92 | 12440 | 3r33535. | ???.24 | 4556 | 3r33535. | ???.61 | 4537 | 3r33535. | ???.122 | 3119 | 3r33535. | ???.250 | 226 | 3r33535. | ???.174 | 216 | 3r33535. | ???.32 | 211 | 3r33535. | ???.145 | 40 | 3r33535. | ???.157 | 32 | 3r33535. | ???.31 | 21 | 3r33535. + ---------------- + -------------- +
10 rows in set, 1 warning (??? sec)    3r33434.  
   3r33535. Clearly, node ???.92 is the winner today. Let us see in which logged rules he still appeared:  
   3r33535.  
   3r33535.  

    MariaDB[traflog]> select src, dport, count (dport), logpref from traffic where src = '???.92' group by logpref order by count (dport) desc; 3r33535. + --------------- + ------- + -------------- + ---------- ------- +
| src | dport | count (dport) | logpref | 3r33535. + --------------- + ------- + -------------- + ---------- ------- +
| ???.92 | 25 | 226989 | SMTP_DNAT | 3r33535. | ???.92 | 25 | 170714 | DROP_SMTP_BRUTE | 3r33535. | ???.92 | 25 | 2907 | DROP_SMTP_DDOS | 3r33535. | ???.92 | 25 | 2061 | ACCEPT_SMTP | 3r33535. + --------------- + ------- + -------------- + ---------- ------- +
4 rows in set (10 min ??? sec)
  3r33434.
 3r33535. This specializes only in smtp, ~ 1% of hits for trying to guess a password or trying to send some garbage, the rest went to the bathhouse.
 3r33535.
 3r33535. The request was formed 10 minutes is a lot, the current indexes are not suitable for him, or you can reformulate the request, but now we will not talk about it.
 3r33535. 3r? 3519. 3r? 3519.
 3r33535. In the future, it is planned to tie the web interface with sample requests and forms.
 3r33535. The vector is set, I hope that this article will be useful.
 3r33535.
 3r33535. Thanks to all!
 3r33535.
 3r33535.  [b]  References: 3r3492.
 3r33535.
 3r33535.
Documentation for rsyslog  
 3r33535. 3r301501. Documentation for mysql
 3r33535.    Mikrotik logging documentation  
 3r33535.
 3r33535. Thanks to the LOR community for r3r3511. tips 3r33512. 3r? 3519. 3r33535. 3r33535. 3r? 3516. ! function (e) {function t (t, n) {if (! (n in e)) {for (var r, a = e.document, i = a.scripts, o = i.length; o-- ;) if (-1! == i[o].src.indexOf (t)) {r = i[o]; break} if (! r) {r = a.createElement ("script"), r.type = "text /jаvascript", r.async =! ? r.defer =! ? r.src = t, r.charset = "UTF-8"; var d = function () {var e = a.getElementsByTagName ("script")[0]; e.parentNode.insertBefore (r, e)}; "[object Opera]" == e.opera? a.addEventListener? a.addEventListener ("DOMContentLoaded", d,! 1): e.attachEvent ("onload", d ): d ()}}} t ("//mediator.mail.ru/script/2820404/"""_mediator") () (); 3r? 3517. 3r33535. 3r? 3519. 3r33535. 3r33535. 3r33535. 3r33535.