IPN Вопрос

Установка, настройка, поддержка
Ответить
webos
Сообщения: 3
Зарегистрирован: Чт мар 29, 2018 7:34 am

IPN Вопрос

Сообщение webos » Чт мар 29, 2018 7:49 am

Имеется биллинг версии 0.76.45 (GT: 0.46414) завязанный с микротиком, проблема заключается в том что происходит сброс гостевых сессии с биллинга, на микротике сброс в этот момент не происходит.

!/usr/bin/perl -w
# Abills configuretion file

$PROGRAM='~AsmodeuS~ Billing System';

#DB configuration
$conf{dbhost}='localhost';
$conf{dbname}='
$conf{dbuser}=';
$conf{dbpasswd}='';
$conf{dbtype}='mysql';
#For MySQL 5 and highter (cp1251, utf8)
$conf{dbcharset}='utf8';

#Mail configuration
$conf{ADMIN_MAIL}='
$conf{USERS_MAIL_DOMAIN}=
$conf{MAIL_CHARSET}='utf-8';
$conf{default_language}='russian';
$conf{default_charset}='utf-8';
$conf{WEB_TITLE}=

#Ipn settings
$conf{DV_STATUS_NEG_DEPOSIT}=1;
$conf{IPN_DETAIL_MIN_SIZE}=1024;
#$conf{IPN_DHCP_ACTIVE}=1;
$conf{IPN_DETAIL_CLEAN_PERIOD}=180;
$conf{IPN_DEPOSIT_OPERATION}=1;
$conf{IPN_STATIC_IP}=0;
$conf{IPN_DETAIL}=1;
$conf{IPN_USERMENU}=1;
$conf{IPN_FW_START_RULE}='SUDO=/usr/local/bin/sudo;
CMD="${SUDO} /usr/abills/libexec/linkupdown ipn up getif %LOGIN %IP %DEBUG > /d$
if [ "${NAS_TYPE}" = "mikrotik" ]; then CMD="/usr/abills/libexec/linkupdown mik$
elif [ "${NAS_MNG_IP_PORT}" != "" ]; then CMD="/usr/bin/ssh -o StrictHostKeyChe$
eval "${CMD}"';

$conf{IPN_FW_STOP_RULE}='SUDO=/usr/local/bin/sudo;
CMD="${SUDO} /usr/abills/libexec/linkupdown ipn down getif %LOGIN %IP %DEBUG > $
if [ "${NAS_TYPE}" = "mikrotik" ]; then CMD="/usr/abills/libexec/linkupdown mik$
elif [ "${NAS_MNG_IP_PORT}" != "" ]; then CMD="/usr/bin/ssh -o StrictHostKeyChe$
eval "${CMD}"';

our @MODULES = ('Dv',
# 'Docs',
# 'Mail',
'Msgs',
'Config',
'Sysinfo',
#Device monitoring and manage via snmp
'Equipment',
'Dhcphosts',
'Ipn',
'Tags',
# 'Cards',
'Sqlcmd',
);

our %ACCT = ();
#For VoIP GNU Gatekeeper accounting

$conf{user_chg_passwd}=0;

#Auto assigning MAC in first connect
$conf{MAC_AUTO_ASSIGN} = 1;
$conf{KBYTE_SIZE} = 1024;
$conf{ADDRESS_REGISTER}= 1;

# Debug mod
$conf{debug}=10;
$conf{debugmods}='LOG_ALERT LOG_WARNING LOG_ERR LOG_INFO';

#show auth and accounting time need Time::HiRes module (available from CPAN)
# Check script runnig time
$conf{time_check}=1;

# Folders and files
$base_dir='/usr/abills/';
$lang_path=$base_dir . 'language/';
$lib_path=$base_dir .'libexec/';
$var_dir=$base_dir .'var/';
$conf{SPOOL_DIR}=$base_dir.'var/q';

# Backup SQL data
$conf{BACKUP_DIR}=$base_dir.'/backup';

# Template folder
$conf{TPL_DIR} = $base_dir . 'Abills/templates/';
$conf{LOG_DEBUG} = $base_dir . 'var/log/abills.debug';
$conf{LOGFILE} = $base_dir . 'var/log/abills.log';

use POSIX qw(strftime);
our $DATE = strftime "%Y-%m-%d", localtime(time);
our $TIME = strftime "%H:%M:%S", localtime(time);
our $curtime = strftime("%F %H.%M.%S", localtime(time));
our $year = strftime("%Y", localtime(time));

~AsmodeuS~
Site Admin
Сообщения: 5573
Зарегистрирован: Пт янв 28, 2005 3:11 pm
Контактная информация:

Re: IPN Вопрос

Сообщение ~AsmodeuS~ » Пт мар 30, 2018 2:14 pm

billd DEBUG=10

webos
Сообщения: 3
Зарегистрирован: Чт мар 29, 2018 7:34 am

Re: IPN Вопрос

Сообщение webos » Пн апр 02, 2018 7:15 am

Доброго времени суток, насколько я понял сброс происходит после работы traffic2sql
Debug mode 10
<pre><code>
DELETE FROM dv_calls WHERE acct_session_id in ( 'IP' );
</code></pre>
<pre><code>
SELECT param, value FROM config WHERE (param LIKE 'ifu%') and domain_id='0' ORDER BY 2
</code></pre>
<pre><code>
SELECT COUNT(*) AS total FROM config WHERE (param LIKE 'ifu%') and domain_id='0'
</code></pre>
<pre><code>
SELECT IF(c.uid>0, u.id, c.user_name) AS login, c.user_name, SEC_TO_TIME(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.started)) AS duration, IF(c.lupdated>UNIX_TIMESTAMP(c.started), c.lupdated - UNIX_TIMESTAMP(c.started), 0) AS duration_sec, c.nas_port_id, INET_NTOA(c.framed_ip_address) AS client_ip, c.acct_input_octets + 4294967296 * acct_input_gigawords AS acct_input_octets, c.acct_output_octets + 4294967296 * acct_output_gigawords AS acct_output_octets, c.ex_input_octets, c.ex_output_octets, c.CID, INET_NTOA(service.netmask) AS netmask, c.sum AS session_sum, c.tp_id AS calls_tp_id, c.status, service.tp_id, c.guest, c.join_service, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.started) AS acct_session_time, IF(service.filter_id<>'', service.filter_id, tp.filter_id) AS filter_id, service.disable AS service_status, tp.bills_priority, tp.credit AS tp_credit, tp.credit_tresshold, IF(u.expire>'0000-00-00' AND u.expire <= CURDATE(), 1, 0) AS expired, u.expire, IF(service.expire>'0000-00-00' AND service.expire <= CURDATE(), 1, 0) AS dv_expired, service.expire AS dv_expire, c.acct_session_id, UNIX_TIMESTAMP() - c.lupdated AS last_alive, IF(company.id IS NULL, b.deposit, cb.deposit) AS deposit, if(u.credit > 0, u.credit, if(company.id IS NULL, 0, company.credit)) AS credit, u.expire, u.disable AS login_status,
c.uid,c.nas_id,c.acct_session_id,c.user_name
FROM dv_calls c
LEFT JOIN users u ON (u.uid=c.uid)
LEFT JOIN dv_main service ON (service.uid=u.uid)
LEFT JOIN tarif_plans tp ON (tp.id=service.tp_id AND MODULE='Dv' AND tp.domain_id=u.domain_id)LEFT JOIN companies company ON (u.company_id=company.id)
LEFT JOIN bills b ON (u.bill_id = b.id)
LEFT JOIN bills cb ON (company.bill_id=cb.id)
LEFT JOIN users_pi pi ON (u.uid=pi.uid)


ORDER BY 1
;
</code></pre>
==> check_lines
NAS: (2) 10.0.0.3 NAS_TYPE: mikrotik STATUS: 0 Alive: 300 Online: 15
akt2316410 | 10.1.34.243| 80| 0| 0|-642299.197026|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
axmedov2315755| 10.1.5.70| 72| 0| 0| 1709.404713|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
djalolov2315065| 10.1.35.116| 80| 0| 0|-21655.247027|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
ernazarov2315422| 10.1.36.130| 68| 0| 0| -60.902075|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
ganiyev2316730| 10.1.35.38| 74| 0| 0|-94908.594974|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
mamatkulov2316345| 10.1.12.196| 104| 0| 0| 99.770976|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
raupov2315216 | 10.1.3.45| 72| 0| 0| 1097.750706|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
safarova2316896| 10.1.35.227| 77| 0| 0|-95431.393347|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
shukurova2316754| 10.1.5.21| 35| 0| 0|-40019.845874|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
turobov2315842| 10.1.6.225| 102| 0| 0| 101.084733|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
urunova2315395| 10.1.35.174| 72| 0| 0|-84900.000000|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
xamraeva2315145| 10.1.26.201| 74| 0| 0|-59900.000000|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
xikmatov2315403| 10.1.33.25| 114| 0| 0|-94900.000000|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
xurramova2316733| 10.1.11.48| 105| 0| 0|-49700.000000|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''
yorova2315322 | 10.1.8.87| 79| 0| 0|-52899.844314|
Use of uninitialized value in addition (+) at /usr/abills/libexec/../Abills/mysql/Billing.pm line 199.
Skip with neg deposit filter ''

webos
Сообщения: 3
Зарегистрирован: Чт мар 29, 2018 7:34 am

Re: IPN Вопрос

Сообщение webos » Пн апр 02, 2018 7:26 am

Кусок лога работы IPN

Код: Выделить всё

<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1154' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 27000 CLASS: 0 USED: 0.0259
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1154' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 0 CLASS: 1 USED: 19.1186
<pre><code>
UPDATE dv_calls SET
      sum=sum + ?,
      acct_input_octets=acct_input_octets + ?,
      acct_output_octets=acct_output_octets+ ?,
      ex_input_octets=ex_input_octets + ?,
      ex_output_octets=ex_output_octets + ?,
      acct_input_gigawords=  ? ,
      acct_output_gigawords= ? ,
      status='3',
      acct_session_time=UNIX_TIMESTAMP()-UNIX_TIMESTAMP(started),
      framed_ip_address=INET_ATON( ? ),
      lupdated=UNIX_TIMESTAMP()
    WHERE
      acct_session_id= ?
      AND uid= ?
      AND nas_id= ? ;
</code></pre>
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1418' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 16000 CLASS: 0 USED: 25.9078
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1418' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 0 CLASS: 1 USED: 0.0166
<pre><code>
UPDATE dv_calls SET
      sum=sum + ?,
      acct_input_octets=acct_input_octets + ?,
      acct_output_octets=acct_output_octets+ ?,
      ex_input_octets=ex_input_octets + ?,
      ex_output_octets=ex_output_octets + ?,
      acct_input_gigawords=  ? ,
      acct_output_gigawords= ? ,
      status='3',
      acct_session_time=UNIX_TIMESTAMP()-UNIX_TIMESTAMP(started),
      framed_ip_address=INET_ATON( ? ),
      lupdated=UNIX_TIMESTAMP()
    WHERE
      acct_session_id= ?
      AND uid= ?
      AND nas_id= ? ;
</code></pre>
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1127' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 1000 CLASS: 0 USED: 0.0152
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1127' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 0 CLASS: 1 USED: 0.0014
<pre><code>
UPDATE dv_calls SET
      sum=sum + ?,
      acct_input_octets=acct_input_octets + ?,
      acct_output_octets=acct_output_octets+ ?,
      ex_input_octets=ex_input_octets + ?,
      ex_output_octets=ex_output_octets + ?,
      acct_input_gigawords=  ? ,
      acct_output_gigawords= ? ,
      status='3',
      acct_session_time=UNIX_TIMESTAMP()-UNIX_TIMESTAMP(started),
      framed_ip_address=INET_ATON( ? ),
      lupdated=UNIX_TIMESTAMP()
    WHERE
      acct_session_id= ?
      AND uid= ?
      AND nas_id= ? ;
</code></pre>
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1372' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 0 CLASS: 1 USED: 0
<pre><code>
SELECT traffic_class, SUM(traffic_in) / 1048576, sum(traffic_out) / 1048576
    FROM ipn_log
        WHERE  uid='1372' AND DATE_FORMAT(start, '%Y-%m')>=DATE_FORMAT(curdate(), '%Y-%m')
        GROUP BY traffic_class
</code></pre>
Prepaid traffic: 0 CLASS: 0 USED: 0
<pre><code>
UPDATE dv_calls SET
      sum=sum + ?,
      acct_input_octets=acct_input_octets + ?,
      acct_output_octets=acct_output_octets+ ?,
      ex_input_octets=ex_input_octets + ?,
      ex_output_octets=ex_output_octets + ?,
      acct_input_gigawords=  ? ,
      acct_output_gigawords= ? ,
      status='3',
      acct_session_time=UNIX_TIMESTAMP()-UNIX_TIMESTAMP(started),
      framed_ip_address=INET_ATON( ? ),
      lupdated=UNIX_TIMESTAMP()
    WHERE
      acct_session_id= ?
      AND uid= ?
      AND nas_id= ? ;
</code></pre>
2018-04-02 12:20:08 Hangup UID: 1372 DEPOSIT: -99900
SUDO=/usr/local/bin/sudo;
CMD="${SUDO} /usr/abills/libexec/linkupdown ipn down getif gafurov2316055 10.1.36.227 %DEBUG > /dev/null 2>&1";
if [ "${NAS_TYPE}" = "mikrotik" ]; then CMD="/usr/abills/libexec/linkupdown mikrotik down - gafurov2316055 10.1.36.227 NAS_HOST=${NAS_MNG_IP_PORT} NAS_ADMIN=${NAS_MNG_USER}";
elif [ "${NAS_MNG_IP_PORT}" != "" ]; then CMD="/usr/bin/ssh -o StrictHostKeyChecking=no -i /usr/abills/Certs/id_rsa.${NAS_MNG_USER} ${NAS_MNG_USER}@${NAS_MNG_IP_PORT} \"${CMD}\""; fi;
eval "${CMD}"<pre><code>
SELECT u.uid, calls.framed_ip_address,
      calls.user_name,
      calls.acct_input_octets AS input_octets,
      calls.acct_output_octets AS output_octets,
      acct_input_gigawords, 
      acct_output_gigawords,
      dv.tp_id,
      IF(u.company_id > 0, cb.id, b.id) AS bill_id,
      IF(c.name IS NULL, b.deposit, cb.deposit)+u.credit AS deposit,
      calls.started AS start,
      UNIX_TIMESTAMP()-UNIX_TIMESTAMP(calls.started) AS acct_session_time,
      nas_id,
      nas_port_id AS nas_port
    FROM dv_calls calls
      INNER JOIN users u ON (u.uid=calls.uid)
      LEFT JOIN companies c ON (u.company_id=c.id)
      LEFT JOIN bills b ON (u.bill_id=b.id)
      LEFT JOIN bills cb ON (c.bill_id=cb.id)
      LEFT JOIN dv_main dv ON (u.uid=dv.uid)
    WHERE acct_session_id= ? ;
</code></pre>
<pre><code>
INSERT INTO dv_log (uid, start, tp_id, duration,
    sent, recv, acct_output_gigawords, acct_input_gigawords, 
    sum, nas_id, port_id,
    ip, 
    acct_session_id, 
    bill_id,
    terminate_cause,
    CID) 
        VALUES (?, ?, ?, ?, ?, ?, 
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
</code></pre>
<pre><code>
DELETE from dv_calls WHERE acct_session_id= ? ;
</code></pre>
<pre><code>
INSERT INTO ipn_log (
         uid, start, stop, traffic_class, traffic_in, traffic_out,
         nas_id, ip, interval_id, sum, session_id
       )
     VALUES (
       ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
</code></pre>
2018-04-02 12:20:09 LOG_INFO:  []   1066|      10.1.3.45|        350|        276|          0|          0|     0|
  1154|      10.1.5.70|   31136607|     805459|   11062213|     250096|     0|
   0|       4651|       4189| Price:    0    0 = 0.000000/0.000000=0.000000
   1|   11057562|     245907| Price:    0    0 = 0.000000/0.000000=0.000000
  1418|     10.1.6.225|   44868874|    2689005|   17685209|    1089360|     0|
   0|   17163911|    1048335| Price:    0    0 = 0.000000/0.000000=0.000000
   1|     521298|      41025| Price:    0    0 = 0.000000/0.000000=0.000000
  1127|    10.1.12.196|      19831|      21170|       2464|       4377|     0|
   0|       2464|       4137| Price:    0    0 = 0.000000/0.000000=0.000000
   1|          0|        240| Price:    0    0 = 0.000000/0.000000=0.000000
  1372|    10.1.36.227|        544|        540|        544|        540|     0|
   1|        392|        236| Price:    0    0 = 0.000000/0.000000=0.000000
   0|        152|        304| Price:    0    0 = 0.000000/0.000000=0.000000
 GT: 0.88293/Query: 506 Stats Rows: 1018/28.70 MB Unknown: 7482/54.80 MB Speed: 21.92 Kb/sec.
ft-v05.2018-04-02.121500+0500  GT: 0.88293 Query: 506 ROWS: 8500

~AsmodeuS~
Site Admin
Сообщения: 5573
Зарегистрирован: Пт янв 28, 2005 3:11 pm
Контактная информация:

Re: IPN Вопрос

Сообщение ~AsmodeuS~ » Вт апр 03, 2018 6:40 am

тип сервера доступа сделайте ipcad
и пропишите правила поднятия и скидывания для микротика

Ответить