Trying to parse out specific values from a log then load to mysql

[pwnedbyphp]

Hi all,

 

I've been stuck on this script for a few days and I can't figure this out. Basically the script opens a file, goes through each line and outputs a numerical value with preg_replace. Then if it matches, it calls a function to insert the value into the database. The problem is I'm having trouble figuring out how to code the function so that it takes multiple variables when the case calls it. The function itself just needs to recognize each type of variable and insert it into the right table.

 

Basically, how would I make the function check what variable called it and insert it that variable into the appropriate table?

 

Thanks in advance!

 

<?php

// open the log file and get each line and assign to a variable
$fp = fopen("test.txt", "r");
while (!feof($fp)) {
// process current line
$current_line = fgets($fp);

switch (true) {

case preg_match("/local time/", $current_line ) :
        $date = preg_replace('/^local time  is: (\S+ \S+ \d+ \d+:\d+:\d+) \S+ \d+$/','\1',$current_line);
parser($date);

        break;

case preg_match("/packets lost/", $current_line ) :
        $plost = preg_replace('/^\[REPORT\] Total packets lost\s+: \d+ \((\d+)%\)$/','\1',$current_line);
parser($plost);

        break;
}
}
function parser()
{
        $c = mysql_connect("localhost", "root", "passwd") or die(mysql_error());
        mysql_select_db("database") or die(mysql_error());
        mysql_query("INSERT INTO database (DATE) VALUES('$date')") or die(mysql_error());
}
fclose($fp);
?>

[five]

assuming your database is named 'database' and has a table named 'database' which contains 'DATE' and 'PLOST' columns..

 

<?php

$dbFieldNames['date']  = 'DATE';
$dbFieldNames['plost'] = 'PLOST';

$patternsMatch['date']    = "/local time/";
$patternsMatch['plost']   = "/packets lost/";

$patternsReplace['date']  = '/^local time  is: (\S+ \S+ \d+ \d+:\d+:\d+) \S+ \d+$/';
$patternsReplace['plost'] = '/^\[REPORT\] Total packets lost\s+: \d+ \((\d+)%\)$/';


$c = mysql_connect("localhost", "root", "passwd") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());


$data = file_get_contents("test.txt");

foreach ($patternsMatch as $type => $patMatch)
{
if (preg_match($patMatch, $current_line))
	$matchStore[$type] = preg_replace($patternsReplace[$type],'\1',$data);
}

if (parser($matchStore)) echo "success!";
else echo "fail..".mysql_error();


function parser(array $match_Store)
{
global $c, $dbFieldNames;
$values = $fields = "";

foreach ($match_Store as $type => $matchCount)
{
	$fields .= $dbFieldNames[$type] . ",";
	$values .= "'$matchCount',";
}
$fields = substr($fields, 0, -1);
$values = substr($values, 0, -1);

$sql = "INSERT INTO database ($fields) VALUES ($values)";
    return mysql_query($sql, $c);
}

?>

 

INSERT syntax is INSERT INTO tableName (FIELD_NAME_1, FIELD_NAME_2) VALUES (VALUE_1, VALUE_2)

[pwnedbyphp]

Cool, I'll give it a try today. Thanks!

[pwnedbyphp]

For the if statement, shouldn't it refer to $data instead of $current_line?

 

$data = file_get_contents("test.txt");

 

foreach ($patternsMatch as $type => $patMatch)

{

if (preg_match($patMatch, $current_line))

[five]

yup sorry for that one. works?

[pwnedbyphp]

It didn't work exactly the way you wrote it but I was able to take parts from it and adapt it into my own. The main difference was the file_get_contents instead of the while loop .

 

$fp = fopen("test.txt", "r");

while (!feof($fp)) {

 

I just took the important parts like the array and the variable definitions and it worked!

 

Thanks to your easy to read code, I have a better understanding of associative arrays now. Thank you so much!

 

 

 

[five]

well i thought there may be more patterns to match in your actual code than just two and this way you can just add up to the array. could you post the working code? just curious

[pwnedbyphp]

There were more patterns but just adding up the array did not work. Even if I only matched two patterns, I kept getting a mysql error regarding the syntax. I think it was basically putting the whole entire text file as the field value.

 

Here's the working code.

<?php

$dbFieldNames['date']  = 'DATE';
$dbFieldNames['plost'] = 'PacketsLost';
$dbFieldNames['order'] = 'OutofOrder';
$dbFieldNames['jitter'] = 'Jitter';
$dbFieldNames['latency'] = 'Latency';

$patternsMatch['date']    = "/local time/";
$patternsMatch['plost']   = "/packets lost/";
$patternsMatch['order']   = "/out-of-order/";
$patternsMatch['jitter']   = "/jitter/";
$patternsMatch['latency']   = "/latency/";

$patternsReplace['date']  = '/^local time is: (\S+ \S+ \d+ \d+:\d+:\d+) \S+ \d+$/';
$patternsReplace['plost'] = '/^\[REPORT\] Total packets lost\s+: \d+ \((\d+)%\)$/';
$patternsReplace['order'] = '/^\[REPORT\] Total out-of-order packets\s+: (\d+)$/';
$patternsReplace['jitter'] = '/^\[REPORT\] Average jitter\s+: (\d+) ms$/';
$patternsReplace['latency'] = '/^\[REPORT\] Average latency\s+: (\d+) ms$/';

$c = mysql_connect("localhost", "root", "passwd") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());


// open the log file and get each line and assign to a variable
$fp = fopen("test.txt", "r");
while (!feof($fp)) {
// process current line
$current_line = fgets($fp);


foreach ($patternsMatch as $type => $patMatch)
{
if (preg_match($patMatch, $current_line)) 
$matchStore[$type] = preg_replace($patternsReplace[$type],'\1',$current_line);

}
}

if (parser($matchStore)) echo "success!";

else echo "fail..".mysql_error();


function parser(array $matchStore)
{

global $c, $dbFieldNames;
$values = $fields = "";

foreach ($matchStore as $type => $matchCount)

{

$fields .= $dbFieldNames[$type] . ",";
$values .= "'$matchCount',";

}

$fields = substr($fields, 0, -1);
$values = substr($values, 0, -1);

$sql = "INSERT INTO tablename ($fields) VALUES ($values)";
    return mysql_query($sql, $c);
echo $sql;

}

fclose($fp);
?>

[pwnedbyphp]

Spoke too soon. I took another look at the data in the database and it is not unique for all fields. Also the number of times the function is called is way too many times. It should basically look at each line of the file and if it matches something it needs to insert just that value into the DB otherwise move on. Also once it finds the match set from the array it needs to move on in the file and find the next result set. Each result set should be 1 row on the db.

 

One match set should be the following:

$patternsMatch['date']    = "/local time/";

$patternsMatch['plost']  = "/packets lost/";

$patternsMatch['order']  = "/out-of-order/";

$patternsMatch['jitter']  = "/jitter/";

$patternsMatch['latency']  = "/latency/";

 

The database looks like:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| DATE        | varchar(32) | NO   | PRI |         |       | 
| PacketsLost | varchar(10) | YES  |     | NULL    |       | 
| OutofOrder  | varchar(10) | YES  |     | NULL    |       | 
| Jitter      | varchar(10) | YES  |     | NULL    |       | 
| Latency     | varchar(10) | YES  |     | NULL    |       | 
+-------------+-------------+------+-----+---------+-------+

 

The log file looks like:

Wed Apr 7 00:00:52 PDT 2010
------------------------------------------------
[REPORT] Number of endpoints        : 2
[REPORT] Number of streams          : 2
[REPORT] Number of calls            : 1
[REPORT] Total packets sent         : 1524
[REPORT] Total packets received     : 1522
[REPORT] Total packets lost         : 2 (0%)
[REPORT] Total out-of-order packets : 0
[REPORT] Total packets 'discarded'  : 0
[REPORT] Average jitter             : 0 ms
[REPORT] Average latency            : 40 ms
[REPORT] TURN timeouts              : 0
[REPORT] TURN refresh failures      : 0
[REPORT] TURN error responses       : 0
[REPORT] TURN instance busy         : 0
[REPORT] Unexpected STUN/TURN code  : 0
[REPORT] Peer timeouts              : 0
[REPORT] Wrong peer                 : 0
[REPORT] Packet send lag            : 26
[REPORT] Main loop starved          : 0
[REPORT] Average sleep time         : 9 ms

 

The above would be one entry and a result is appended this file at a specified interval so a 2nd entry would be practically the same appended at the end of the first entry.

 

Any help with this is appreciated!