I need help compiling a php script that will fetch IP accounting data from my MikroTik router and store it into a sql database,
On MikroTik the IP accounting is displayed on the web port in cgi eg. http://routerip:webport/accounting/ip.cgi
After every request to ip.cgi the router automatically clears all data.
On ip.cgi the data is displayed:
196.10.52.57 192.168.1.60 456 6 * *
157.240.1.32 192.168.2.16 612 6 * *
192.168.1.23 38.90.226.38 2124 15 * *
23.21.244.249 192.168.1.23 6219 13 * *
157.240.1.18 192.168.1.23 9881 27 * *
192.168.1.23 23.21.244.249 1987 20 * *
The first ip is the src address, second ip is the dst address, the next number is the bytes transferred and the last number is the packets transferred. The stars are not needed, I don't even know what they are for.
I have this code so far to connect to the database but cannot figure out how to pull the info from the ip.cgi page ans store it into the database
<?php
$SQLserver = "localhost";
$SQLusername = "root";
$SQLpassword = "sqlpassword";
$SQLdatabase = "sqldatabase";
$RouterIP = "192.168.1.1";
$WebPort = "81";
$AccountingLocation = "accounting/ip.cgi";
$routerconnect = "http://$RouterIP:$WebPort/$AccountingLocation";
$orig = file_get_contents($routerconnect);
$a = htmlentities($orig);
$conn = mysqli_connect($SQLserver, $SQLusername, $SQLpassword,
$SQLdatabase);
if (!$conn) {
die("Could not connect: " . mysqli_connect_error());
}
$sql = "INSERT INTO accounting (src_address, dst_address, bytes, packets)
VALUES ('1.1.1.1', '2.2.2.2', '3', '4')";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
I will be adding this php script to a cron job to run every 5 minutes to store accounting data for my OSPF network, all accounting is done on my core mikrotik router.
Once you've pulled the data with file_get_contents() into your $orig variable, you'll need to parse it out:
foreach ( explode( "\n", $orig ) as $line ) {
if ( trim( $line )) {
list( $src, $dst, $bytes, $packets ) = explode( ' ', trim( strip_tags( $line )));
// Now $src, $dst, $bytes, and $packets contain your data
$sql = "INSERT INTO accounting (src_address, dst_address, bytes, packets) VALUES ('$src', '$dst', '$bytes', '$packets')";
// Then the rest of your code to execute the query goes here
}
}
This will break the result into individual lines, then break the lines into pieces (assuming that the pieces are separated by spaces as it appears they are in your question). From there, you can handle the SQL part that you've already written.
Check into the documentation for list for more information about what's happening there.