:local sysnumber [/system routerboard get value-name=serial-number]
:local txbyte [/interface ethernet get ether1-gateway value-name=driver-tx-byte]
:local rxbyte [/interface ethernet get ether1-gateway value-name=driver-rx-byte]
/tool fetch url=("http://URL/collector.php\?sn=$sysnumber&tx=$txbyte&rx=$rxbyte") mode=http keep-result=no
<?php
$link = mysql_connect('localhost', 'root', 'password');
if (!$link) die('Could not connect: ' . mysql_error());
mysql_select_db('tikstat');
$db_host="localhost";
$db_user="root";
$db_pass="password";
$db_database="tikstat";
global $db;
$db = new mysqli($db_host, $db_user, $db_pass, $db_database);
if ($db->connect_error) die('Could not connect');
?><?php
/*
/collector.php?sn=<SERIAL NUMBER>&tx=<INTERFACE OUR BYTES>&rx=<INTERFACE IN BYTES>
*/
include("include/config.php");
// Check input data
if (isset($_GET[sn])
and isset($_GET[tx]) and is_numeric($_GET[tx])
and isset($_GET[rx]) and is_numeric($_GET[rx])) {
$device_serial = substr($_GET[sn], 0, 12);
} else {
echo 'No code fail';
exit;
}
// Create new devices if not exist
$link = "insert into devices(id, last_tx, last_rx, FROM devices WHERE sn='".$device_serial."'";
$link = mysql_query($db);
if (!is_numeric($device[0][id])) {
$link = "INSERT INTO devices(sn, last_check, last_tx, last_rx) VALUES ('".$device_serial."', '".time()."', '".$_GET[tx]."', '".$_GET[rx]."')";
$device[0][id] = $db->lastInsertRowid();
$txBytes = $_GET[tx];
$rxBytes = $_GET[rx];
$link = mysql_query($db);
echo 'INSERT INTO device';
} else {
// update last receiving data
$db = "UPDATE devices SET last_check='".time()."', last_tx='".$_GET[tx]."', last_rx='".$_GET[rx]."' WHERE id='".$device[0][id]."'";
// check last received value
if ($device[0][last_tx] > $_GET[tx]) {
$txBytes = $_GET[tx];
} else {
$txBytes = $_GET[tx]-$device[0][last_tx];
}
if ($device[0][last_rx] > $_GET[rx]) {
$rxBytes = $_GET[rx];
} else {
$rxBytes = $_GET[rx]-$device[0][last_rx];
}
$link = mysql_query($db);
}
$db = "SELECT * FROM traffic WHERE device_id='".$device[0][id]."' AND datetime = ".mktime(date('H'),0,0)." LIMIT 1";
$link = mysql_query($db);
if (is_numeric($link[0][id])) {
$db = "UPDATE traffic SET tx='".($link[0][tx]+$txBytes)."', rx='".($link[0][rx]+$rxBytes)."' WHERE id='".$link[0][id]."'";
$link = mysql_query($db);
} else {
$db = "INSERT INTO traffic(device_id, datetime, tx, rx) VALUES (".$device[0][id].", ".mktime(date('H'),0,0).", ".$txBytes.", ".$rxBytes.")";
$link = mysql_query($db);
}
?>-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Dec 12, 2015 at 06:52 PM
-- Server version: 5.6.21
-- PHP Version: 5.5.19
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `tikstat`
--
-- --------------------------------------------------------
--
-- Table structure for table `devices`
--
CREATE TABLE IF NOT EXISTS `devices` (
`id` mediumint(9) NOT NULL,
`sn` text COLLATE utf8_unicode_ci,
`comment` text COLLATE utf8_unicode_ci,
`last_tx` int(11) DEFAULT NULL,
`last_rx` int(11) DEFAULT NULL,
`last_check` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- --------------------------------------------------------
--
-- Table structure for table `traffic`
--
CREATE TABLE IF NOT EXISTS `traffic` (
`id` mediumint(9) NOT NULL,
`device_id` int(10) unsigned NOT NULL,
`datetime` datetime NOT NULL,
`tx` int(10) unsigned NOT NULL,
`rx` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `devices`
--
ALTER TABLE `devices`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `traffic`
--
ALTER TABLE `traffic`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `traffic` (`device_id`,`datetime`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `devices`
--
ALTER TABLE `devices`
MODIFY `id` mediumint(9) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;
--
-- AUTO_INCREMENT for table `traffic`
--
ALTER TABLE `traffic`
MODIFY `id` mediumint(9) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
<?php
include 'config.php';
include 'opendb.php';
if (isset($_GET[sn])
and isset($_GET[tx]) and is_numeric($_GET[tx])
and isset($_GET[rx]) and is_numeric($_GET[rx])) {
$device_serial = substr($_GET[sn], 0, 12);
} else {
echo '<table border="2" align="center"><tr><td>';
echo '<align="center">This PHP scripts will only run from the Mikrotik Routerboard Tikstat Script.<br />';
echo '</td></tr></table>';
exit;
}
// Delete empty records start
mysql_query("delete from devices where sn = '0'");
// Delete empty records end
$res = mysql_query("select id ,last_tx, last_rx from devices where sn='".$_GET[sn]."'");
$row = mysql_fetch_row($res);
if ($row[0]==0)
$sql = "insert into devices (sn,last_tx,last_rx,last_check) values ('".$_GET[sn]."','".$_GET[tx]."','".$_GET[rx]."',CURRENT_TIMESTAMP)";
else
$sql = "update devices set "."sn='".$device_serial."'".",last_tx='".$_GET[tx]."'".",last_rx='".$_GET[rx]."'".",last_check = CURRENT_TIMESTAMP"." where sn='".$device_serial."'";
mysql_query($sql);
mysql_close();
echo ' "OK";';
?>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'tikstat';
?> <?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db($dbname);
?> :local sysnumber [/system routerboard get value-name=serial-number];
:local txbyte [/interface get [find name="ether8_ADSL"] tx-byte];
:local rxbyte [/interface get [find name="ether8_ADSL"] rx-byte];
# convert to MB
# :set txbyte (($txbyte / 1048576))
# :set rxbyte (($rxbyte / 1048576))
:set txbyte (($txbyte / 1000))
:set rxbyte (($rxbyte / 1000))
:log warning "$sysnumber ...";
:log warning "$txbyte ...";
:log warning "$rxbyte ...";
/tool fetch url=("http://192.168.0.105:81/tikstat-master/collector.php\?sn=$sysnumber&tx=$txbyte&rx=$rxbyte") mode=http keep-result=no;
:log warning "Tikstat inset to DB has run...";