I am working on a script to import MikroTik IP accounting data into MySQL only if there is an existing row in the database containing the IP address.
I have a table services with columns id(service id) and ipv4(service IP address) which contains the rows of IP addresses I want to store the accounting data for.
I then have another table traffic_counters with columns id, service_id, download_bytes, upload_bytes and date
Currently when fetching the accounting data from API, if I use print_r($ARRAY), I get the output like:
Array ( [0] => Array ( [.id] => *0 [src-address] => 10.2.1.2 [dst-address] => 10.1.1.20 [packets] => 4 [bytes] => 4528 ) [1] => Array ( [.id] => *1 [src-address] => 10.1.1.20 [dst-address] => 18.196.198.94 [packets] => 2 [bytes] => 80 ) [2] => Array ( [.id] => *2 [src-address] => 10.2.1.2 [dst-address] => 10.100.1.1 [packets] => 16 [bytes] => 2216 )
I am only at this stage wanting to keep track of the IP address and bytes transferred. I used to use a script that saves all the accounting data into a table and then sorts it into a new table then clears when done but this process is taking extremely way too long, sometimes the script can run for two hours before completing entering all the data temporarily into a table.
My previous query was
SELECT
ip_address,
SUM(upload_bytes) as upload_bytes,
SUM(download_bytes) as download_bytes,
SUM(upload_bytes + download_bytes) as total_bytes,
timeanddate
FROM
(
(
SELECT
ip_accounting.src_address as ip_address,
SUM(ip_accounting.bytes) AS upload_bytes,
0 as download_bytes,
ip_accounting.accounting_router_ip,
ip_accounting.accounting_router_id,
timeanddate
FROM
ip_accounting
GROUP BY
src_address
)
UNION ALL
(
SELECT
ip_accounting.dst_address as ip_address,
0 AS upload_bytes,
SUM(ip_accounting.bytes) as download_bytes,
ip_accounting.accounting_router_ip,
ip_accounting.accounting_router_id,
timeanddate
FROM
ip_accounting
GROUP BY
dst_address
)
) a
GROUP BY
ip_address,
YEAR(timeanddate),
MONTH(timeanddate),
DAY(timeanddate)
ORDER BY
ip_address
So basically takes the source IP as upload and destination IP as download
I need to change the script to fetch all the data via API like I mentioned in the beginning and run it through a foreach loop to look for matching IP addresses that are in the services.ipv4 table/column and then insert/update it into the traffic_counters table
I am having a bit of a problem on how to use the foreach command on this type of array output and not very sure on which order to code out the script.
My current script does daily accounting which must stay the same, eg. every IP accounting update will add the bytes to the existing traffic_counters table or insert a new row if the date is not current.
So far I have tried
$API->write('/ip/accounting/snapshot/take',true);
$READ = $API->read(false);
$ARRAY = $API->parseResponse($READ);
$API->write('/ip/accounting/snapshot/print',true);
$READ = $API->read(false);
$ARRAY = $API->parseResponse($READ);
foreach($ARRAY['0']['.id'] as $ACCOUNTING) {
$id = $ARRAY['0']['.id'];
$ip_src = $ARRAY['0']['src-address'];
$ip_dst = $ARRAY['0']['dst-address'];
$bytes = $ARRAY['0']['dst-address'];
}
But I am getting
Warning: Invalid argument supplied for foreach() in /opt/WispManager/html/admin/test.php on line 24
Could someone please help me figure out on how to do this process in the most efficient way because I am frying my brain with this entire process, I do not want to store any temporary data in mysql.
I am trying to copy the same way in which Splnyx does their IP accounting.
EDIT: I have played around a bit and figured out how to do the foreach loop with the array and have got everything work as I wanted. I am just not sure if my way of doing this is very efficient, may some one please check my code for me and point me in the right direction if there is a simpler/quicker solution.
My new script:
<?php
//Require admin
require_once("inc/admin.php");
require_once ("../includes/routeros_api.class.php");
//SET
$ip = "10.100.1.1";
//Connect to MikroTik API
$API = new RouterosAPI();
$API->debug = $config['api']['debug'];
if (!$API->connect($ip, $config['api']['username'], $config['api']['password'])) {
echo "Could not connect to RouterOS API";
} else {
$API->write('/ip/accounting/snapshot/take',true);
$READ = $API->read(false);
$ARRAY = $API->parseResponse($READ);
$API->write('/ip/accounting/snapshot/print',true);
$READ = $API->read(false);
$ARRAY = $API->parseResponse($READ);
foreach($ARRAY as $ACCOUNTING) {
$ip_src = $ACCOUNTING['src-address'];
$ip_dst = $ACCOUNTING['dst-address'];
$bytes = $ACCOUNTING['bytes'];
//Check if ip in use UPLOAD
$query = "SELECT id, ipv4 FROM services WHERE ipv4='$ip_src' AND deleted !='1'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_array($result);
if(mysqli_num_rows($result) > 0) {
$service_id = $row['id'];
//Update Download Traffic
$check_if_exist_query = "SELECT * FROM traffic_counters WHERE service_id='$service_id' AND date=CURRENT_DATE()";
$check_result = mysqli_query($conn, $check_if_exist_query);
$check_num_rows = mysqli_num_rows($check_result);
if($check_num_rows == 0) {
$add_query = "INSERT INTO traffic_counters (service_id, upload_bytes, date) VALUES ('$service_id', '$bytes', CURRENT_DATE());";
$add_result = mysqli_query($conn, $add_query);
} else {
$update_query = "UPDATE traffic_counters SET
upload_bytes = upload_bytes + $bytes
WHERE service_id='$service_id' AND date=CURRENT_DATE();
";
$update_result = mysqli_query($conn, $update_query);
}
}
//Check if ip in use DOWNLOAD
$query = "SELECT id, ipv4 FROM services WHERE ipv4='$ip_dst' AND deleted !='1'";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_array($result);
if(mysqli_num_rows($result) > 0) {
$service_id = $row['id'];
//Update Download Traffic
$check_if_exist_query = "SELECT * FROM traffic_counters WHERE service_id='$service_id' AND date=CURRENT_DATE()";
$check_result = mysqli_query($conn, $check_if_exist_query);
$check_num_rows = mysqli_num_rows($check_result);
if($check_num_rows == 0) {
$add_query = "INSERT INTO traffic_counters (service_id, download_bytes, date) VALUES ('$service_id', '$bytes', CURRENT_DATE());";
$add_result = mysqli_query($conn, $add_query);
} else {
$update_query = "UPDATE traffic_counters SET
download_bytes = download_bytes + $bytes
WHERE service_id='$service_id' AND date=CURRENT_DATE();
";
$update_result = mysqli_query($conn, $update_query);
}
}
}
$API->disconnect();
}
?>
