http://www.iredmail.org/docs/sogo-centos-6-mysql.html

http://www.mysqltutorial.org/mysql-stored-function/

CREATE DEFINER=`hieunt`@`localhost` FUNCTION `get_range_counter`(`p_idRule` INT, `p_rType` VARCHAR(50))
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE ret INT;

select sum(b.rE-b.rB+1) as DIP INTO ret
from tbl_rule_detail b
where b.rType=p_rType and b.idRule= p_idRule;

if p_rType='SP' or p_rType='DP' then
if ret>=65536 then set ret=0; end if;
else
if ret>=2147483647 then set ret=0; end if;
end if;

return (ret);

END

//—————–

select a.id,b.code_ticket ,a.SourceVlan,a.SourceIP,a.SourcePort,a.DesVLan,a.DesIP,a.DesPort,a.Protocol,
get_range_counter(a.id,'SIP') as SIP,
get_range_counter(a.id,'SP') as SP,
get_range_counter(a.id,'DIP') as DIP,
get_range_counter(a.id,'DP') as DP
from tbl_rule_request a, tbl_tickets b
where TO_DAYS(a.LastSeen)>TO_DAYS(NOW())-2 and a.idTicket=b.id
having (SIP>20)or (DIP>20) or SP>10 or DP>10
order by (DP+SP+DIP+SIP) DESC