1
Personal the code below parses the data that is in the openvpn log and stores in the database that by other codes are shown on a table page. But that’s a lot of records in a log file! There are more than 6 thousand (and will go up with time) and this is taking a little while and ended up breaking the database with "Too Many Conections" and also to show the data in a table I use the following:
$sql = "SELECT idvpn, comName, realAddr, byS, byR, virtAddr, since, blockstatus, connectedOr FROM vpn";
$query = mysqli_query($conexao, $sql);
return $query;
Only with this code above, it takes almost 2 minutes to display the data in a table (where there is that next page with a plugin called Datatable). Because of numerous requests - I think - I couldn’t even log on to the site anymore and the data started to be entered all broken in the database... I need urgent help, I need to know what I’m doing wrong!
Here is the code of the parser that breaks each line of the log and takes the information, checks if it already exists in the database, if there is no it adds, if there is it checks if it is in the log and in the database, if yes it updates to connected, if not it updates to disconnected.
<?php
$con = mysqli_connect("*******", "root", "*******", "*******");
set_time_limit(0);
$pathlog = "log2.txt";
ovpnParser($con, $pathlog);
function checkDiff($con, &$isinlog){
// echo '<script>console.log("**** START CHECKDIFF ****")</script>';
$databArray = array(); // database array
$sql = "SELECT comName FROM vpn";
$query = mysqli_query($con, $sql);
while ($result = mysqli_fetch_array($query)){
array_push($databArray, $result['comName']);
}
foreach($databArray as $newArray){
if(!in_array($newArray, $isinlog)){
// echo '<script>console.log("database ok, log off...")</script>';
$sql = "UPDATE vpn SET connectedOr = 'false' WHERE comName = '{$newArray}'"; // tem no bd mas não tem no log. ou seja, desconectado.
$query = mysqli_query($con, $sql);
}
}
// echo '<script>console.log("**** END CHECKDIFF****")</script>';
}
function ovpnParser($con, $pathlog){
// echo '<script>console.log("**** START OVPN PARSER****")</script>';
$isinlog = array(); // log array
$inclients = false;
$handle = fopen($pathlog, "r");
$inclients = false;
while(!feof($handle)){
$line = fgets($handle, 4096);
if (substr($line, 0, 11) == "CLIENT_LIST"){
if (preg_match("/CLIENT_LIST\t{1,}UNDEF(.*)UNDEF/", $line)){
$inclients = false;
}
else{
$inclients = true;
}
}
if ($inclients){
preg_match("/CLIENT_LIST(.*)UNDEF/", $line, $conteudo);
$partes = preg_split("/\t{1,}/", trim($conteudo[1]));
array_push($isinlog, $partes[0]);
$sql = "SELECT comName FROM vpn WHERE comName = '{$partes[0]}'";
$query = mysqli_query($con, $sql);
$rows = mysqli_num_rows($query);
if ($rows == 0){
// echo '<script>console.log("new register")</script>';
$sql = "INSERT INTO vpn (comName, realAddr, virtAddr, byR, byS, since, blockstatus, connectedOr)
VALUES ('{$partes[0]}', '{$partes[1]}', '{$partes[2]}', '{$partes[3]}', '{$partes[4]}', '{$partes[5]}', 'true', 'true')";
$query = mysqli_query($con, $sql);
}
elseif ($rows > 0) {
// echo '<script>console.log("log and database ok...")</script>';
$sql = "UPDATE vpn SET byR = '{$partes[3]}', byS = '{$partes[4]}', connectedOr = 'true' WHERE comName = '{$partes[0]}'";
$query = mysqli_query($con, $sql);
}
}
$inclients = false;
}
// echo '<script>console.log("**** END OVPN PARSER ****")</script>';
checkDiff($con, $isinlog);
}