Rsyslog parser for Mikrotik proxy

RouterOS Scripting
1

Hi
can any one write a parser script for rsyslog so that i can parse and store mysql proxy log to database
current message format is

Receivedat: 2013-09-27 17:11:07
Facility: 1
Priority: 5
FromHost: 172.30.0.xxx
Message: 172.16.0.xxx GET http://google.co.in action=allow cache=MISS
SysLogTag: web-proxy,account

i want it to be stored in database like
Receivedat: 2013-09-27 17:11:07
Client IP: 172.16.0.xxx
Url: http://google.co.in
status: MISS
Provider: web-proxy

2

Looks like you never got a reply to this.
I am wondering about whether to do the same thing,

…or attempt to write a script to clean things up before leaving the Mikrotik router.. e.g. just keeping the Response to "GET http:’ and ‘Content-Length:’ lines and then send those via a HTTP GET?
Mark

3

For what its worth, the webproxylogtomysql.php in Proxylizer does a pretty good job of recording URL requests, domains and HIT/MISS requests in Mysql via syslog-ng. It comes with a small crontab wrapper script that keeps it running. It takes the output of a named pipe fed by syslog-ng and pushes everything into Mysql.

The rest of Proxylizer (report generation, mailing etc) is a little broken now, especially if your running PHP5, as theirs a lot of deprecated stuff in there now. (e.g ereg and use of DB.php)

4

yes, proxylizer is a bit outdated now. And since there is allmost no interest in it, it will stay like this.

5

Want to share my script, wich convert rsyslog mkaccess.log file to squid format.
mkaccess.log: no debug, only simple one-record-per-line format.
There is no size(fake size, 1024 per string), no dst-host ip(fake ip 8.8.8.8), only datetime, local ip and url persist
Output can be used on linux-tools like lightsquid etc.


#!/usr/bin/perl -w
use Date::Parse;

$num_args = $#ARGV + 1;
if ($num_args != 1) {
    # mkaccess.log - syslog's file, mikrotik remote logging.
    print "\nUsage: ".$0." /path/to/mkaccess.log\n";
    exit;
}

open (IN, "<".$ARGV[0]) || die $!;
while(<IN>){
    chomp;
    ($line)=join " ",(split / /)[1,0,2,3,4,5,6,7,8];
    $line =~ s/^\s+//gi;
    $line =~ s/(\s+)/ /gi;
    my($day,$mns,$time,$hname,$host,$method,$site,$access) = split('\s+', $line);
    ($date)=join " ",$day,$mns,$time;
    $dateepoch=str2time($date." +0200");
    if ($access eq 'action=allow') {
        $string=$dateepoch.".000 999 ".$host." TCP_MISS/200 1024 ".$method." ".$site." - DIRECT/8.8.8.8 text/html\n";
        print $string;
    }
}
close IN;
6

Hi, guys!

I wrote simple one-line script based on script posted near year ago. Script worked like a charm(i think) and faster than perl-script. 8 min vs more than 10 hours. I hope this line help some one.

Thats it: cat log_from_mikrotik.log |grep -v ‘action=deny’|awk -F"MSRRTR01 proxy:" ‘{system(“date +%s.000 -d "”$1""");print("1 "$2)}’|sed ‘:a;N;0~2!ba;s/\n/ /g;’|awk -F" " {‘print $1" “$2” “$3” TCP_MISS/200 100 “$4” “$5” - DIRECT/0.0.0.0 text/html\n\r"’} > converted.log