homer-api-mysql 5.0.6+dfsg2-3 / etc / homer / kamailio-homer.cfg

#!KAMAILIO
#
# Example configuration file for a sipcapture node
#

####### Global Parameters definitions #########
#
# Please, make all your configuration changes here
#
# *** To enable extra stats
#     - define WITH_STATISTIC_METHOD_EXTRA
#     - define WITH_STATISTIC_INVITE_1XX



#!substdef "!HOMER_DB_USER!homer_user!g"
#!substdef "!HOMER_DB_PASSWORD!homer_password!g"
#!substdef "!HOMER_LISTEN_PROTO!udp!g"
#!substdef "!HOMER_LISTEN_IF!0.0.0.0!g"
#!substdef "!HOMER_LISTEN_PORT!9060!g"
#!substdef "!HOMER_STATS_SERVER!tcp:HOMER_LISTEN_IF:8888!g"

####### Global Parameters #########

debug=1
log_stderror=no
log_name="homer"

memdbg=5
memlog=5

##!define KAMAILIO_4_3
##!define WITH_HOMER_GEO
##!define WITH_HOMER_CUSTOM_STATS #enable it for HTTP custom stats

log_facility=LOG_LOCAL1

fork=yes
children=5

/* uncomment the next line to disable TCP (default on) */
disable_tcp=yes

/* IP and port for HEP capturing) */
listen=HOMER_LISTEN_PROTO:HOMER_LISTEN_IF:HOMER_LISTEN_PORT

#!ifdef WITH_HOMER_CUSTOM_STATS
listen=HOMER_STATS_SERVER
#!endif

#Max loops
max_while_loops=100

mpath="/usr/lib/x86_64-linux-gnu/kamailio/modules/"
loadmodule "pv.so"
loadmodule "db_mysql.so"
loadmodule "sipcapture.so"
loadmodule "textops.so"
loadmodule "rtimer.so"
loadmodule "xlog.so"
loadmodule "sqlops.so"
loadmodule "htable.so"
loadmodule "tm.so"
loadmodule "sl.so"
loadmodule "siputils.so"
loadmodule "exec.so"

#!ifdef WITH_HOMER_GEO
loadmodule "geoip.so"
#!endif

#!ifdef WITH_HOMER_CUSTOM_STATS
loadmodule "xhttp.so"
loadmodule "jansson.so"
loadmodule "avpops.so"
#!endif


modparam("htable", "htable", "a=>size=8;autoexpire=400")
modparam("htable", "htable", "b=>size=8;autoexpire=31")
# TODO: tune autoexpire setting for htable "c"
modparam("htable", "htable", "c=>size=8;autoexpire=31")

#!ifdef WITH_HOMER_CUSTOM_STATS
modparam("htable", "htable", "d=>size=8;autoexpire=400")
modparam("xhttp", "url_match", "^/api/v1/stat")
#!endif

modparam("rtimer", "timer", "name=ta;interval=60;mode=1;")
modparam("rtimer", "exec", "timer=ta;route=TIMER_STATS")

modparam("sqlops","sqlcon","cb=>mysql://HOMER_DB_USER:HOMER_DB_PASSWORD@127.0.0.1/homer_statistic")

# ----- mi_fifo params -----

####### Routing Logic ########
modparam("sipcapture", "db_url", "mysql://HOMER_DB_USER:HOMER_DB_PASSWORD@127.0.0.1/homer_data")
modparam("sipcapture", "capture_on", 1)
modparam("sipcapture", "hep_capture_on", 1)
modparam("sipcapture", "insert_retries", 5)
modparam("sipcapture", "insert_retry_timeout", 10)
#modparam("sipcapture", "capture_node", "homer01")

#!ifdef WITH_HOMER_GEO
modparam("geoip", "path", "/usr/share/GeoIP/GeoIPCity.dat")
#!endif

#Stats time
stats.min = 5 desc "My stats TIME min"


# Main SIP request routing logic
# - processing of any incoming SIP request starts with this route
route {

	if($sht(a=>method::total) == $null) $sht(a=>method::total) = 0;
	$sht(a=>method::total) = $sht(a=>method::total) + 1;
	if($sht(a=>packet::count) == $null) $sht(a=>packet::count) = 0;
        if($sht(a=>packet::size) == $null) $sht(a=>packet::size) = 0;
	
	 #Packets
        $sht(a=>packet::count) = $sht(a=>packet::count) + 1;
        $sht(a=>packet::size) = $sht(a=>packet::size) + $ml;


	if($sht(b=>$rm::$cs::$ci) != $null) {
		#$var(a) = "sip_capture_call" + "_%Y%m%d";
		#Store
		route(STORE);
		drop;
	}

	$sht(b=>$rm::$cs::$ci) = 1;	
	if($sht(a=>method::all) == $null) $sht(a=>method::all) = 0;
	$sht(a=>method::all) = $sht(a=>method::all) + 1;
	
	if (is_method("INVITE|REGISTER")) {

		if($ua =~ "(friendly-scanner|sipvicious|sipcli)") {
			sql_query("cb", "INSERT INTO alarm_data_mem (create_date, type, total, source_ip, description) VALUES(NOW(), 'scanner', 1, '$si', 'Friendly scanner alarm!') ON DUPLICATE KEY UPDATE total=total+1");
			route(KILL_VICIOUS);
		}

		#IP Method
		sql_query("cb", "INSERT INTO stats_ip_mem ( method, source_ip, total) VALUES('$rm', '$si', 1) ON DUPLICATE KEY UPDATE total=total+1");

#!ifdef WITH_HOMER_GEO
		if(geoip_match("$si", "src")) {
                        xlog("REGISTER|INVITE SIP message [$si] from: $gip(src=>cc)\n");
                        sql_query("cb", "INSERT INTO stats_geo_mem ( method, country, lat, lon, total) VALUES('$rm', '$gip(src=>cc)', '$gip(src=>lat)', '$gip(src=>lon)', 1) ON DUPLICATE KEY UPDATE total=total+1");
                }
#!endif


		if($au != $null)  $var(anumber) = $au;
		else $var(anumber) = $fU;

		if($(var(anumber){s.substr,0,5}) == "+204231") {
                	if($sht(a=>alarm::scam) == $null) $sht(a=>alarm::scam) = 0;
                        $sht(a=>alarm::scam) = $sht(a=>alarm::scam) + 1;
		}

		if($(rU{s.substr,0,5}) == "+204231") {
			if($sht(a=>alarm::scam) == $null) $sht(a=>alarm::scam) = 0;
			$sht(a=>alarm::scam) = $sht(a=>alarm::scam) + 1; 
		}
	
	
		#if($sel(contact.uri.host) =~ "^([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}))$") {
		if($sel(contact.uri.host) =~ "^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$") {
			if($sht(a=>alarm::dns) == $null) $sht(a=>alarm::dns) = 0;
			$sht(a=>alarm::dns) = $sht(a=>alarm::dns) + 1; 
		}


		if($sel(contact.uri.host) != $si) {
			if($sht(a=>alarm::spoofing) == $null) $sht(a=>alarm::spoofing) = 0;
			$sht(a=>alarm::spoofing) = $sht(a=>alarm::spoofing) + 1; 
		}

		if($au =~ "(\=)|(\-\-)|(\')|(\#)|(\%27)|(\%24)") {
			if($sht(a=>alarm::sqlinjection) == $null) $sht(a=>alarm::sqlinjection) = 0;
			$sht(a=>alarm::sqlinjection) = $sht(a=>alarm::sqlinjection) + 1; 
		}

		if($(hdr(Record-Route)[0]{nameaddr.uri}) != $si) {
			if($sht(a=>alarm::spoofing) == $null) $sht(a=>alarm::spoofing) = 0;
			$sht(a=>alarm::spoofing) = $sht(a=>alarm::spoofing) + 1; 
		}
		

		if (is_method("INVITE")) {			

		        if (has_totag()) {
				if($sht(a=>method::reinvite) == $null) $sht(a=>method::reinvite) = 0;
				$sht(a=>method::reinvite) = $sht(a=>method::reinvite) + 1; 				
			}
			else {
				if($sht(a=>method::invite) == $null) $sht(a=>method::invite) = 0; 
				$sht(a=>method::invite) = $sht(a=>method::invite) + 1; 
				if($adu != $null) {
					if($sht(a=>method::invite::auth) == $null) $sht(a=>method::invite::auth) = 0; 
					$sht(a=>method::invite::auth) = $sht(a=>method::invite::auth) + 1; 
				}

				if($ua != $null) {
					sql_query("cb", "INSERT INTO stats_useragent_mem (useragent, method, total) VALUES('$ua', 'INVITE', 1) ON DUPLICATE KEY UPDATE total=total+1");
				}

			}					
		}
		else {
			if($sht(a=>method::register) == $null) $sht(a=>method::register) = 0; 		
			$sht(a=>method::register) = $sht(a=>method::register) + 1; 

			if($adu != $null) {
				if($sht(a=>method::register::auth) == $null) $sht(a=>method::register::auth) = 0; 
				$sht(a=>method::register::auth) = $sht(a=>method::register::auth) + 1; 
			}
			
			if($ua != $null) {
				sql_query("cb", "INSERT INTO stats_useragent_mem (useragent, method, total) VALUES('$ua', 'REGISTER', 1) ON DUPLICATE KEY UPDATE total=total+1");
			}
		}

	}
	else if(is_method("BYE")) {
		if($sht(a=>method::bye) == $null) $sht(a=>method::bye) = 0; 
		$sht(a=>method::bye) = $sht(a=>method::bye) + 1; 		
		if(is_present_hf("Reason")) {
                       $var(cause) = $(hdr(Reason){param.value,cause}{s.int});
                       if($var(cause) != 16 && $var(cause) !=17) {
				if($sht(a=>stats::sdf) == $null) $sht(a=>stats::sdf) = 0; 
				$sht(a=>stats::sdf) = $sht(a=>stats::sdf) + 1; 
		       }
		}

#		if($si != $sht(a=>ipinit::aleg::$ci) && $si != $sht(a=>ipinit::bleg::$ci)) {
#			if($sht(a=>alarm::sessiontd) == $null) $sht(a=>alarm::sessiontd) = 0;
#			$sht(a=>alarm::sessiontd) = $sht(a=>alarm::sessiontd) + 1; 
#		}

	}
	else if(is_method("CANCEL")) {
		if($sht(a=>method::cancel) == $null) $sht(a=>method::cancel) = 0; 
		$sht(a=>method::cancel) = $sht(a=>method::cancel) + 1; 

	}
	else if(is_method("OPTIONS")) {
		if($sht(a=>method::options) == $null) $sht(a=>method::options) = 0; 
		$sht(a=>method::options) = $sht(a=>method::options) + 1; 
		#xlog("OPTIONS COUNTING.... $sht(a=>method::options)\n");

	}
	else if(is_method("REFER")) {
		if($sht(a=>method::refer) == $null) $sht(a=>method::refer) = 0; 
		$sht(a=>method::refer) = $sht(a=>method::refer) + 1; 

	}
	else if(is_method("UPDATE")) {
		if($sht(a=>method::update) == $null) $sht(a=>method::update) = 0; 
		$sht(a=>method::update) = $sht(a=>method::update) + 1; 
	}
	
	else if(is_method("PUBLISH"))
        {
                if($cT == "application/vq-rtcpxr" && $(rb{s.substr,0,1}) != "x") {
                        $var(table) = "report_capture";
                        #$var(callid) = $(rb{re.subst,/(.*)CallID:([0-9A-Za-z_.@-]{5,120})(.*)$/\2/s});
                        $var(callid) = $(rb{re.subst,/.*CallID:([0-9A-Za-z@-_\.]{5,120}).*$/\1/s});

			#Local IP. Only for stats
			#$var(localaddress) = $(rb{re.subst,/.*LocalAddr:IP=([^\S]+).*$/\1/s});

                        #xlog("CALLID: $var(callid)");
#!ifdef KAMAILIO_4_3
			xlog("report_capture is not in < 4.3");
#!else
			#Workaround for Kamailio < 5
			report_capture("$var(table)", "$var(callid)", "$mb");
			#report_capture("$var(table)", "$var(callid)");
#!endif                        
                        drop;
                }
        }
	
#!ifdef WITH_STATISTIC_METHOD_EXTRA
	else if(is_method("ACK")) {
                if($sht(a=>method::ack) == $null) $sht(a=>method::ack) = 0; 
                $sht(a=>method::ack) = $sht(a=>method::ack) + 1;
        }
        else {
                if($sht(a=>method::unknown) == $null) $sht(a=>method::unknown) = 0; 
                $sht(a=>method::unknown) = $sht(a=>method::uknown) + 1; 
        }     
#!endif     

	#Store
	route(STORE);
	drop;
}

onreply_route {

	if($sht(a=>method::total) == $null) $sht(a=>method::total) = 0;
	$sht(a=>method::total) = $sht(a=>method::total) + 1;

	if($sht(b=>$rs::$cs::$rm::$ci) != $null) {
		#Store
		route(STORE);
		drop;
	}

	$sht(b=>$rs::$cs::$rm::$ci) = 1;
	
	if($sht(a=>method::all) == $null) $sht(a=>method::all) = 0;
	$sht(a=>method::all) = $sht(a=>method::all) + 1;

	#413 Too large
	if(status == "413") {	
		if($sht(a=>response::413) == $null) $sht(a=>response::413) = 0; 
		$sht(a=>response::413) = $sht(a=>response::413) + 1; 

		if($sht(a=>alarm::413) == $null) $sht(a=>alarm::413) = 0; 
		$sht(a=>alarm::413) = $sht(a=>alarm::413) + 1; 
	}
	#403 Unauthorize
        else if(status == "403") {
                if($sht(a=>response::403) == $null) $sht(a=>response::403) = 0; 
                $sht(a=>response::403) = $sht(a=>response::403) + 1; 

                if($sht(a=>alarm::403) == $null) $sht(a=>alarm::403) = 0; 
                $sht(a=>alarm::403) = $sht(a=>alarm::403) + 1; 
        }
	# Too many hops
	else if(status == "483") {	
		if($sht(a=>response::483) == $null) $sht(a=>response::483) = 0; 
		$sht(a=>response::483) = $sht(a=>response::483) + 1; 

		if($sht(a=>alarm::483) == $null) $sht(a=>alarm::483) = 0; 
		$sht(a=>alarm::483) = $sht(a=>alarm::483) + 1; 

	}
	# loops
	else if(status == "482") {	
		if($sht(a=>response::482) == $null) $sht(a=>response::482) = 0; 
		$sht(a=>response::482) = $sht(a=>response::482) + 1; 

		if($sht(a=>alarm::482) == $null) $sht(a=>alarm::482) = 0; 
		$sht(a=>alarm::482) = $sht(a=>alarm::482) + 1; 

	}
	# Call Transaction Does not exist
	else if(status == "481") {	
		if($sht(a=>alarm::481) == $null) $sht(a=>alarm::481) = 0; 
		$sht(a=>alarm::481) = $sht(a=>alarm::481) + 1; 
	}
	# 408 Timeout
	else if(status == "408") {	
		if($sht(a=>alarm::408) == $null) $sht(a=>alarm::408) = 0; 
		$sht(a=>alarm::408) = $sht(a=>alarm::408) + 1; 
	}
	# 400
	else if(status == "400") {	

		if($sht(a=>alarm::400) == $null) $sht(a=>alarm::400) = 0; 
		$sht(a=>alarm::400) = $sht(a=>alarm::400) + 1; 

	}
	# MOVED
	else if(status =~ "^(30[012])$") {	
		if($sht(a=>response::300) == $null) $sht(a=>response::300) = 0; 
		$sht(a=>response::300) = $sht(a=>response::300) + 1; 
	}

	if($rm == "INVITE") {
		#ISA
		if(status =~ "^(408|50[03])$") {	
			if($sht(a=>stats::isa) == $null) $sht(a=>stats::isa) = 0; 
			$sht(a=>stats::isa) = $sht(a=>stats::isa) + 1; 	
		}
		#Bad486
		if(status =~ "^(486|487|603)$") {	
			if($sht(a=>stats::bad::invite) == $null) $sht(a=>stats::bad::invite) = 0; 
			$sht(a=>stats::bad::invite) = $sht(a=>stats::bad::invite) + 1; 	
		}

		#SD
		if(status =~ "^(50[034])$") {	
			if($sht(a=>stats::sd) == $null) $sht(a=>stats::sd) = 0; 
			$sht(a=>stats::sd) = $sht(a=>stats::sd) + 1; 	
		}

		if(status == "407") {	
			if($sht(a=>response::407::invite) == $null) $sht(a=>response::407::invite)= 0;
			$sht(a=>response::407::invite) = $sht(a=>response::407::invite) + 1; 
		}
		else if(status == "401") {			
			if($sht(a=>response::401::invite) == $null) $sht(a=>response::401::invite)= 0;
			$sht(a=>response::401::invite) = $sht(a=>response::401::invite) + 1; 
		}
		else if(status == "200") {			
			if($sht(a=>response::200::invite) == $null) $sht(a=>response::200::invite)= 0;
			$sht(a=>response::200::invite) = $sht(a=>response::200::invite) + 1; 
		}
		#Aditional stats
#!ifdef WITH_STATISTIC_INVITE_1XX		
	        else if(status == "100") {
                        if($sht(a=>response::100::invite) == $null) $sht(a=>response::100::invite)= 0;
                        $sht(a=>response::100::invite) = $sht(a=>response::100::invite) + 1;
                }
                else if(status == "180") {
                        if($sht(a=>response::180::invite) == $null) $sht(a=>response::180::invite)= 0;
                        $sht(a=>response::180::invite) = $sht(a=>response::180::invite) + 1;
                }   
                else if(status == "183") {                      
                        if($sht(a=>response::183::invite) == $null) $sht(a=>response::183::invite)= 0;
                        $sht(a=>response::183::invite) = $sht(a=>response::183::invite) + 1;
                }
#!endif                
	}
	else if($rm == "BYE") {

		if(status == "407") {	
			if($sht(a=>response::407::bye) == $null) $sht(a=>response::407::bye) = 0;
			$sht(a=>response::407::bye) = $sht(a=>response::407::bye) + 1; 
		}
		else if(status == "401") {			
			if($sht(a=>response::401::bye) == $null) $sht(a=>response::401::bye) = 0;
			$sht(a=>response::401::bye) = $sht(a=>response::401::bye) + 1; 
		}
		else if(status == "200") {			
			if($sht(a=>response::200::bye) == $null) $sht(a=>response::200::bye) = 0;
			$sht(a=>response::200::bye) = $sht(a=>response::200::bye) + 1; 
		}
	}
	
	#Store
	route(STORE);
	drop;
}

route[KILL_VICIOUS] {
	xlog("Kill-Vicious ! si : $si ru : $ru ua : $ua\n");
	return;
}

route[STATS_BY_IP] {
        if($sht(c=>$rs::$sel(via[1].host)) == $null) $sht(c=>$rs::$sel(via[1].host)) = 0;
        $sht(c=>$rs::$sel(via[1].host)) = $sht(c=>$rs::$sel(via[1].host)) + 1;
}

route[PARSE_STATS_IP] {
        sht_iterator_start("i1", "c");
        while(sht_iterator_next("i1")) {
                $var(sipcode) = $(shtitkey(i1){s.select,0,:});
                $var(ip) = $(shtitkey(i1){s.select,2,:});
                
                if($shtitval(i1) > $avp($var(sipcode))) {
                        sql_query("cb", "INSERT INTO alarm_data (create_date, type, total, source_ip, description) VALUES(NOW(), 'Too Many $var(sipcode)', $shtitval(i1), '$var(ip)', 'Too Many $var(sipcode)')");
                }
        }
        sht_iterator_end("i1");
        sht_rm_name_re("c=>.*");
}

route[TIMER_STATS] {

    #xlog("timer routine: time is $TS\n");

    route(CHECK_ALARM);
    #Check statistics 	 
    route(CHECK_STATS);

}


route[SEND_ALARM] {
   	exec_msg('echo "Value: $var(thvalue), Type: $var(atype), Desc: $var(aname)" | mail -s "Homer Alarm $var(atype) - $var(thvalue)" $var(aemail) ') ;
}

route[CHECK_ALARM] 
{

    #POPULATE ALARM THRESHOLDS
    #Homer 5 sql schema    
    sql_query("cb", "SELECT type,value,name,notify,email FROM alarm_config WHERE NOW() between startdate AND stopdate AND active = 1", "ra");
    if($dbr(ra=>rows)>0)
    {
	$var(i) = 0;
 	while($var(i)<$dbr(ra=>rows))
        {
                $var(atype) = $dbr(ra=>[$var(i),0]);
                $var(avalue) = $dbr(ra=>[$var(i),1]);
                $var(aname) = $dbr(ra=>[$var(i),2]); 
                $var(anotify) = $(dbr(ra=>[$var(i),3]){s.int});
                $var(aemail) = $dbr(ra=>[$var(i),4]); 
                $avp($var(atype)) = $var(avalue);

                if($sht(a=>alarm::$var(atype)) != $null) {
                        $var(thvalue) = $sht(a=>alarm::$var(atype));
                }

                #If Alarm - go here
                if($var(thvalue) > $var(avalue)) {
                                                  
                        sql_query("cb", "INSERT INTO alarm_data (create_date, type, total, description) VALUES(NOW(), '$var(aname)', $var(thvalue), '$var(aname) - $var(atype)');");
                        #Notify
                        if($var(anotify) == 1) {
                                route(SEND_ALARM);
                        }                         
                }

                #Alarm for Scanner;
                if($var(atype) == "scanner") {
                        sql_query("cb", "DELETE FROM alarm_data_mem WHERE type='scanner' AND total < $var(avalue)");
                        if($var(anotify) == 1) 
                        {
                                sql_query("cb", "SELECT * FROM alarm_data_mem WHERE type='scanner' AND total  >= $var(avalue) LIMIT 2", "rd");        
                                if($dbr(rd=>rows) > 0) {
                                        route(SEND_ALARM);
                                }

                                sql_result_free("rd");
                        }
                }
         
                $sht(a=>alarm::$var(atype)) = 0;
                $var(i) = $var(i) + 1;
        }

    }


    sql_result_free("ra");


    #if(sql_xquery("cb", "select type,value,name,notify,email from alarm_config WHERE startdate > NOW() AND stopdate < NOW() AND active = 1", "trigger") == 1) {
    #    $var(i) = 0;
    #    while($xavp(trigger[$var(i)]) != $null) {
    #      $avp($xavp(trigger[$var(i)]=>type)) = $xavp(trigger[$var(i)]=>value); 
    #       $avp($xavp(trigger[$var(i)]=>type)+"_notify") = $xavp(trigger[$var(i)]=>notify); 
    #       $avp($xavp(trigger[$var(i)]=>type)+"_email") = $xavp(trigger[$var(i)]=>email); 
    #       $var(i) = $var(i) + 1;
    #    }   
    #}

    route(PARSE_STATS_IP);

    #delete old alarms
    sql_query("cb", "DELETE FROM alarm_data WHERE create_date < DATE_SUB(NOW(), INTERVAL 5 DAY)");
}


route[CHECK_STATS] {

    #SQL STATS
    $var(tm) = ($time(min) mod 10);
    if($var(tm) != 0 && $var(tm) != $sel(cfg_get.stats.min)) return;

    $var(t1) = $TS;
    $var(t2) = $var(t1) - ($sel(cfg_get.stats.min)*60);

    $var(t_date) = "FROM_UNIXTIME(" + $var(t1) + ", '%Y-%m-%d %H:%i:00')";
    $var(f_date) = "FROM_UNIXTIME(" + $var(t2) + ", '%Y-%m-%d %H:%i:00')";

    #ALARM SCANNERS
    sql_query("cb", "INSERT INTO alarm_data (create_date, type, total, source_ip, description) SELECT create_date, type, total, source_ip, description FROM alarm_data_mem;");
    sql_query("cb", "TRUNCATE TABLE alarm_data_mem");

    #STATS Useragent
    sql_query("cb", "INSERT INTO stats_useragent (from_date, to_date, useragent, method, total) SELECT $var(f_date) as from_date, $var(t_date) as to_date, useragent, method, total FROM stats_useragent_mem;");
    sql_query("cb", "TRUNCATE TABLE stats_useragent_mem");

    #STATS IP
    sql_query("cb", "INSERT INTO stats_ip (from_date, to_date, method, source_ip, total) SELECT $var(f_date) as from_date, $var(t_date) as to_date, method, source_ip, total FROM stats_ip_mem;");
    sql_query("cb", "TRUNCATE TABLE stats_ip_mem");

#!ifdef WITH_HOMER_GEO
    sql_query("cb", "INSERT INTO stats_geo (from_date, to_date, method, country, lat, lon, total) SELECT $var(f_date) as from_date, $var(t_date) as to_date, method, country, lat, lon, total FROM stats_geo_mem;");
    sql_query("cb", "TRUNCATE TABLE stats_geo_mem");
#!endif
    
    #INSERT SQL STATS
    #Packet HEP stats
    if($sht(a=>packet::count) != $null && $sht(a=>packet::count) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'packet_count', $sht(a=>packet::count)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>packet::count)");
        $sht(a=>packet::count) = 0;
    }
    if($sht(a=>packet::size) != $null && $sht(a=>packet::size) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'packet_size', $sht(a=>packet::size)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>packet::size)");
        $sht(a=>packet::size) = 0;
    }

    #SDF
    if($sht(a=>stats::sdf) != $null && $sht(a=>stats::sdf) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'sdf', $sht(a=>stats::sdf)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>stats::sdf)");
        $sht(a=>stats::sdf) = 0;
    }

    #ISA
    if($sht(a=>stats::isa) != $null && $sht(a=>stats::isa) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'isa', $sht(a=>stats::isa)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>stats::isa)");
        $sht(a=>stats::isa) = 0;
    }

    #SD
    if($sht(a=>stats::sd) != $null && $sht(a=>stats::sd) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'isa', $sht(a=>stats::sd)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>stats::sd)");
        $sht(a=>stats::sd) = 0;
    }

    #SSR
    if($sht(a=>stats::ssr) != $null && $sht(a=>stats::ssr) > 0) {
        sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'ssr', $sht(a=>stats::ssr)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>stats::ssr)");
        $sht(a=>stats::ssr) = 0;
    }

    #ASR
    $var(asr) = 0;
    #if($sht(a=>response::200::invite) > 0) {
    if($sht(a=>method::invite) > 0) {
        if($sht(a=>response::407::invite) == $null) $sht(a=>response::407::invite) = 0;
        if($sht(a=>response::200::invite) == $null) $sht(a=>response::200::invite) = 0;
        $var(d) = $sht(a=>method::invite) - $sht(a=>response::407::invite);
        if($var(d) > 0) {
                $var(asr) =  $sht(a=>response::200::invite) * 100 / $var(d);
                if($var(asr) > 100)  $var(asr) = 100;
        }
    }

    #Stats DATA
    sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'asr', $var(asr)) ON DUPLICATE KEY UPDATE total=(total+$var(asr))/2");


    #NER
    $var(ner) = 0;
    #if($sht(a=>response::200::invite) > 0 || $sht(a=>stats::bad::invite) > 0) {
    if($sht(a=>method::invite) > 0) {

        if($sht(a=>response::200::invite) == $null) $sht(a=>response::200::invite) = 0;
        if($sht(a=>response::bad::invite) == $null) $sht(a=>response::bad::invite) = 0;
        if($sht(a=>response::407::invite) == $null) $sht(a=>response::407::invite) = 0;

        $var(d) = $sht(a=>method::invite) - $sht(a=>response::407::invite);

        if($var(d) > 0) {
                $var(ner) = ($sht(a=>response::200::invite) + $sht(a=>stats::bad::invite)) * 100 / $var(d);
                if($var(ner) > 100)  $var(ner) = 100;
        }
    }

    sql_query("cb", "INSERT INTO stats_data (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), 'ner', $var(ner)) ON DUPLICATE KEY UPDATE total=(total+$var(ner))/2");

    #INVITE
    if($sht(a=>method::reinvite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, totag, total) VALUES($var(f_date), $var(t_date),'INVITE', 1, $sht(a=>method::reinvite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::reinvite)");
        $sht(a=>method::reinvite) = 0;
    }

    #INVITE
    if($sht(a=>method::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'INVITE', $sht(a=>method::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::invite)");
        $sht(a=>method::invite) = 0;
    }

    #INVITE AUTH
    if($sht(a=>method::invite::auth) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, auth, total) VALUES($var(f_date), $var(t_date), 'INVITE', 1, $sht(a=>method::invite::auth)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::invite::auth)");
        $sht(a=>method::invite::auth) = 0;
    }

    #REGISTER
    if($sht(a=>method::register) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'REGISTER', $sht(a=>method::register)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::register)");
        $sht(a=>method::register) = 0;
    }

    #REGISTER AUTH
    if($sht(a=>method::register::auth) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, auth, total) VALUES($var(f_date), $var(t_date), 'REGISTER', 1, $sht(a=>method::register::auth)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::register::auth)");
        $sht(a=>method::register::auth) = 0;
    }

    #BYE
    if($sht(a=>method::bye) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'BYE', $sht(a=>method::bye)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::bye)");
        $sht(a=>method::bye) = 0;
    }

    #CANCEL
    if($sht(a=>method::cancel) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'CANCEL', $sht(a=>method::cancel)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::cancel)");
        $sht(a=>method::cancel) = 0;
    }

    #OPTIONS
    if($sht(a=>method::options) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'OPTIONS', $sht(a=>method::options)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::options)");
        $sht(a=>method::options) = 0;
    }

#!ifdef WITH_STATISTIC_METHOD_EXTRA
    #UNKNOWN
    if($sht(a=>method::unknown) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'UNKNOWN', $sht(a=>method::unknown)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::unknown)");
        $sht(a=>method::unknown) = 0;
    }
    
    #ACK
    if($sht(a=>method::ack) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'ACK', $sht(a=>method::ack)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::ack)");
        $sht(a=>method::ack) = 0;
    }
#!endif

    #REFER
    if($sht(a=>method::refer) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'REFER', $sht(a=>method::refer)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::refer)");
        $sht(a=>method::refer) = 0;
    }

    #UPDATE
    if($sht(a=>method::update) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'UPDATE', $sht(a=>method::update)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::update)");
        $sht(a=>method::update) = 0;
    }

    #RESPONSE

    #300
    if($sht(a=>response::300) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), '300', $sht(a=>response::300)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::300)");
        $sht(a=>response::300) = 0;
    }

    #407 INVITE
    if($sht(a=>response::407::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '407', 'INVITE', $sht(a=>response::407::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::407::invite)");
        $sht(a=>response::407::invite) = 0;
    }

    #401 INVITE
    if($sht(a=>response::401::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '401', 'INVITE', $sht(a=>response::401::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::401::invite)");
        $sht(a=>response::401::invite) = 0;
    }

#!ifdef WITH_STATISTIC_INVITE_1XX
    #100 INVITE
    if($sht(a=>response::100::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '100', 'INVITE', $sht(a=>response::100::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::100::invite)");
        $sht(a=>response::100::invite) = 0;
    }

    #180 INVITE
    if($sht(a=>response::180::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '180', 'INVITE', $sht(a=>response::180::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::180::invite)");
        $sht(a=>response::180::invite) = 0;
    }

    #183 INVITE
    if($sht(a=>response::183::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '183', 'INVITE', $sht(a=>response::183::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::183::invite)");
        $sht(a=>response::183::invite) = 0; 
    }

#!endif

    #200 INVITE
    if($sht(a=>response::200::invite) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '200', 'INVITE', $sht(a=>response::200::invite)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::200::invite)");
        $sht(a=>response::200::invite) = 0;
    }

    #407 BYE
    if($sht(a=>response::407::bye) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '407', 'BYE', $sht(a=>response::407::bye)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::407::bye)");
        $sht(a=>response::407::bye) = 0;
    }

    #401 BYE
    if($sht(a=>response::401::bye) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '401', 'BYE', $sht(a=>response::401::bye)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::401::bye)");
        $sht(a=>response::401::bye) = 0;
    }

    #200 BYE
    if($sht(a=>response::200::bye) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, cseq, total) VALUES($var(f_date), $var(t_date), '200', 'BYE', $sht(a=>response::200::bye)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>response::200::bye)");
        $sht(a=>response::200::bye) = 0;
    }

    #ALL TRANSACTIONS MESSAGES
    if($sht(a=>method::all) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'ALL', $sht(a=>method::all)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::all)");
        $sht(a=>method::all) = 0;
    }
    
     #ALL MESSAGES ON INTERFACE
    if($sht(a=>method::total) > 0) {
        sql_query("cb", "INSERT INTO stats_method (from_date, to_date, method, total) VALUES($var(f_date), $var(t_date), 'TOTAL', $sht(a=>method::total)) ON DUPLICATE KEY UPDATE total=total+$sht(a=>method::total)");
        $sht(a=>method::total) = 0;
    }

#!ifdef WITH_HOMER_CUSTOM_STATS
    #Generic stats
    sht_iterator_start("i1", "d");
    while(sht_iterator_next("i1")) {
                $var(key) = $(shtitkey(i1){s.select,2,:});
                sql_query("cb", "INSERT INTO stats_generic (from_date, to_date, type, total) VALUES($var(f_date), $var(t_date), '$var(key)', $shtitval(i1)) ON DUPLICATE KEY UPDATE total=(total+$shtitval(i1))/2");
    }
    sht_iterator_end("i1");
    sht_rm_name_re("d=>.*");
#!endif

}


route[STORE] {

        if($rm == "REGISTER") {
                $var(table) = "sip_capture_registration";       
        }
        else if($rm =~ "(INVITE|UPDATE|BYE|ACK|PRACK|REFER|CANCEL)$")
        {
                $var(table) = "sip_capture_call";
        } 
        else if($rm =~ "(NOTIFY)$" && is_present_hf("Event") && $hdr(Event)=~"refer;")
        {
                $var(table) = "sip_capture_call";
        }
        else if($rm =~ "(INFO)$")
        {
                $var(table) = "sip_capture_call";
        }
        else if($rm =~ "(OPTIONS)$" )
        {
            $var(table) = "sip_capture_rest";
        }
        else {   
            $var(table) = "sip_capture_rest";
        }

	
#!ifdef KAMAILIO_4_3
	#For old models. Not accurate insert time. System vs capture time.
	#$var(utc) = $timef(%Y%m%d);	
	#as workaround for kamailio < 4.4
	sql_query("cb", "SELECT UTC_DATE()+0;", "ra");
    	if($dbr(ra=>rows)>0) {
              $var(utc) = $dbr(ra=>[0,0]);
        }
        sql_result_free("ra");
#!else
	#for Kamailio >=4.4 please uncomment this parameters:
	# New utimef can fix the timediff issue. But unfortunately only kamailio 4.4
	$var(utc) = $utimef(%Y%m%d);
	# or sipcapture style
	#$var(utc) = "%Y%m%d";
#!endif                        

	$var(a) = $var(table) + "_" + $var(utc);
	sip_capture("$var(a)");
}

event_route[sipcapture:request] {
	xlog("received sipcapture request from $si:$sp\r\n");
}

#!ifdef WITH_HOMER_CUSTOM_STATS
event_route[xhttp:request] {
        set_reply_close();
        set_reply_no_connect();
        xlog("L_WARN", "HTTP request received on $Rp, $hu\n");
        if($hu =~ "/api/v1/stats/push") {
                #Json is our body
                $var(json) = $rb;
                jansson_get("type", $var(json), "$var(n)");
                xlog("L_WARN","Type is $var(n)");
                if($var(n) == "rtp_stat") {
                         $var(i) = 0;
                         $(avp(x)[0]) = 'interval';
                         $(avp(x)[1]) = 'streams';
                         $(avp(x)[2]) = 'packets';
                         $(avp(x)[3]) = 'lost';
                         $(avp(x)[4]) = 'late';
                         $(avp(x)[5]) = 'lost_perc';
                         $(avp(x)[6]) = 'late_perc';
                         $(avp(x)[7]) = 'out_of_seq';
                         $(avp(x)[8]) = 'delay_min';
                         $(avp(x)[9]) = 'delay_max';
                         while(is_avp_set("$(avp(x)[$var(i)])")) {
                                  xlog("L_INFO", "Array value [$var(i)]: $(avp(x)[$var(i)])\n");
                                  jansson_get("$(avp(x)[$var(i)])", $var(json), "$var(d)");
                                  $var(n) = $(var(d){s.int});
                                  if($sht(d=>generic::$(avp(x)[$var(i)])) == $null) $sht(d=>generic::$(avp(x)[$var(i)])) = $var(n);
                                  else $sht(d=>generic::$(avp(x)[$var(i)])) = ($sht(d=>generic::$(avp(x)[$var(i)])) + $var(n))/2;                                  
                                  $var(i) = $var(i) + 1;
                         }
          }
          xhttp_reply("200", "Ok", "done", "");
          exit;
        }

        xhttp_reply("403", "Forbidden", "", "");
        exit;
}
#!endif