Pre-processing an mp_log.txt for stats (Howto)

[dodger42]

Hey all, if your interested in starting a stats project for your squad or whatever this may get you on your way. This is based on the pre-release code I wrote for lomac-stats.com. There are a lot of innefficiencies in the code, but with a few hours you could easily clean those up.

 

Running this pre-processor will input a split raw log into a database (my_sql tested only). I have not included the logic for determining kills / deaths etc. That is up to you.

 

This is being released for the good of the lomac community. Hope it helps!

 

 

<?php

$filename = "../logs/live/pub.txt";

$firstline = TRUE;

$pos = 0;

$elapsed = 0;

$allrows = 0;

$debug = false;

 

$filectime = filectime($filename) or die('Could not read file!');

 

// open file for reading

$fh = @fopen($filename, "r") or die('Could not read file!'); // open your logfille

 

// interate over open file until we reach an end of file marker

while (!feof($fh)) {

// read a line from file up to 4096 bytes long

$buffer = fgets($fh, 4096);

$md5 = md5($buffer);

 

if($firstline == TRUE) {

// here we process the first line

$start = intval(substr($buffer,0,2))*86400 +

intval(substr($buffer,3,2))*3600 +

intval(substr($buffer,6,2))*60 +

intval(substr($buffer,9,2));

$firstline = FALSE;

$unix_timestamp = $filectime;

 

} else {

// here we process remaining lines.

$pos = intval(substr($buffer,0,2))*86400 +

intval(substr($buffer,3,2))*3600 +

intval(substr($buffer,6,2))*60 +

intval(substr($buffer,9,2));

$elapsed = $pos - $start;

$unix_timestamp = $elapsed + filectime($filename);

}

 

//split lines into variables

list($ignore, $str1, $str2, $str3, $str4, $str5, $str6, $str7, $str8) = split(" ", $buffer, 9);

$str3 = trim($str3);

 

 

$test_array = explode("\"",$str2);

 

$pilot_callsign = str_replace(array('/', '\\' ,'\'', '[',']'), array('','','',''), $test_array[1]);

 

$pilot_ac = str_replace(array(')', '('), array('',''), $test_array[2]);

 

// TEST VARIABLES - DEBUG ONLY

 

if ($debug == true)

{

 

echo "str1 = ". $str1. " ";

echo "str2 = ". $str2. " ";

echo "str3 = ". $str3. " ";

echo "str4 = ". $str4. " ";

echo "str5 = ". $str5. " ";

echo "str6 = ". $str6. " ";

echo "str7 = ". $str7. " ";

echo "str8 = ". $str8. " <br>";

echo "<br>";

echo "actual_log_line= ".$buffer."<br>";

}

 

 

include_once('../include/db_connect.php');

 

//

if ($str3 == 'kill' and $str4 == 'vehicle') {

if(substr($str6,0,1) == "\"") {

$array_gu = array($str6, $str7);

$victim_gu_dirty = implode(" ", $array_gu);

$victim_gu = ereg_replace("\"*","",$victim_gu_dirty);

$victim_gu_pristine = ereg_replace("by", "", $victim_gu);

}

 

$pilot_team = $str1;

$victim_team = $str5;

$weapon = str_replace("by ","",$str8);

$weapon_dirty = ereg_replace("\(.*\)", "", $weapon);

$weapon_pristine = str_replace("-","_",$weapon_dirty);

 

$action = $str3;

$unit_type = $str4;

 

if ($debug == true)

{

echo " " .$unix_timestamp. " " .$pilot_team. " " .$pilot_callsign. " " .$pilot_ac. " " .$action. " " .$victim_team. " " .$unit_type. " " .$victim_gu_pristine. " " .$weapon_clean. "<br><br>";

} else {

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp , pilot_team , pilot_callsign , pilot_ac , action, victim_team, unit_type, victim_gu, weapon) VALUES('$md5', '$unix_timestamp' , '$pilot_team', '$pilot_callsign' , '$pilot_ac' , '$action', '$victim_team', '$unit_type', '$victim_gu_pristine', '$weapon_pristine')";

mysql_query($sql_wr) or die (mysql_error());

}

 

 

}

 

//

if ($str3 == 'kill' and $str4 == 'ship') {

if(substr($str6,0,1) == "\"") {

$array_gu = array($str6, $str7);

$victim_gu_dirty = implode(" ", $array_gu);

$victim_gu = ereg_replace("\"*","",$victim_gu_dirty);

$victim_gu_pristine = ereg_replace("by", "", $victim_gu);

}

 

$pilot_team = $str1;

$victim_team = $str5;

$weapon = str_replace("by ","",$str8);

$weapon_clean = ereg_replace("\(.*\)", "", $weapon);

$weapon_pristine = str_replace("-","_",$weapon_clean);

$action = $str3;

$unit_type = $str4;

 

 

if ($debug == true)

{

echo $unix_timestamp. " " .$pilot_team. " " .$pilot_callsign. " " .$pilot_ac. " " .$action. " " .$victim_team. " " .$unit_type. " " .$victim_gu_pristine. " ".$weapon_clean. "<br>";

} else {

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp , pilot_team , pilot_callsign , pilot_ac , action, victim_team, unit_type, victim_gu, weapon) VALUES('$md5','$unix_timestamp' , '$pilot_team', '$pilot_callsign' , '$pilot_ac' , '$action', '$victim_team', '$unit_type', '$victim_gu_pristine', '$weapon_pristine')";

mysql_query($sql_wr) or die (mysql_error());

}

 

 

}

 

//

if ($str3 == 'kill' and $str4 != 'vehicle' and $str4 != 'ship') {

// Clean up victim names // !!!! change this to an ereg replace to avoid screwy player name characters

$slice_v = str_replace("\"(", " ", $str5);

$slice2_v = str_replace ("\"", "", $slice_v);

$slice3_v = str_replace (")", "", $slice2_v);

list($victim_callsign, $victim_ac) = split(" ", $slice3_v, 2);

 

$test_array2 = explode("\"",$str5);

 

$victim_ac2 = $test_array2[2];

$victim_ac3 = str_replace (")","", $victim_ac2);

$victim_ac = str_replace ("(","", $victim_ac3);

 

$victim_callsign = str_replace(array('/', '\\' ,'\'', '[',']'), array('','','',''), $test_array2[1]);

 

$pilot_team = $str1;

$victim_team = $str4;

$weapon_dirty = trim($str7);

$weapon = str_replace("-","_",$weapon_dirty);

$action = $str3;

 

 

if ($debug == true)

{

echo $unix_timestamp. " " .$pilot_team. " " .$pilot_callsign. " " .$pilot_ac. " " .$action. " " .$victim_team. " " .$victim_callsign. " " .$victim_ac. " ".$weapon. "<br>";

} else {

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp , pilot_team , pilot_callsign , pilot_ac , action, victim_team, victim_callsign, victim_ac, weapon) VALUES('$md5', ' $unix_timestamp' , '$pilot_team', '$pilot_callsign' , '$pilot_ac' , '$action', '$victim_team', '$victim_callsign', '$victim_ac', '$weapon')";

mysql_query($sql_wr) or die (mysql_error());

}

}

 

 

//

if ($str3 == 'land') {

$pilot_team = $str1;

$action = $str3;

$airfield_status = trim($str6);

$airfield = trim($str5);

 

if ($debug == true)

{

echo $unix_timestamp." ".$pilot_team." ".$pilot_callsign." ".$pilot_ac." ".$action." ".$airfield." ".$airfield_status."<br>";

 

} else {

 

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp, pilot_team, pilot_callsign, pilot_ac, action, airfield_status, airfield) VALUES('$md5', '$unix_timestamp', '$pilot_team', '$pilot_callsign', '$pilot_ac', '$action', '$airfield', '$airfield_status')";

// echo $sql_wr."<br>";

mysql_query($sql_wr) or die (mysql_error());

}

 

}

 

 

//

if ($str3 == 'entered' or $str3 == 'exited' or $str3 == 'crash' or $str3 == 'eject') {

$pilot_team = $str1;

$action = $str3;

 

if ($pilot_team == NULL) {

//echo 'spectator';

} else {

//echo $unix_timestamp. " ". $pilot_team. " ". $pilot_callsign. " ". $pilot_ac ." ". $action. " "."<br>";

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp , pilot_team , pilot_callsign , pilot_ac , action) VALUES('$md5', '$unix_timestamp' , '$pilot_team', '$pilot_callsign', '$pilot_ac' , '$action')";

mysql_query($sql_wr) or die (mysql_error());

}

 

}

 

$allrows++;

 

}

fclose($fh);

echo $allrows. " rows processed. This may not be the amount of rows added to DB";

 

 

?>

 

[dodger42]

Some explanation of the code (where in code explanations do not exist)

 

To determine a timestamp for an event, you take the mp_log.txt file creation time and then the first log entry time. Subsequent entries are an offset of that time, and the value is assigned to $unix_timestamp. Note the firstline and subsequent lines are handled seperately. For accuracy, you must ensure correct system time on the game server and no tampering of the mp_log.txt filectime.

 

if($firstline == TRUE) {

// here we process the first line

$start = intval(substr($buffer,0,2))*86400 +

intval(substr($buffer,3,2))*3600 +

intval(substr($buffer,6,2))*60 +

intval(substr($buffer,9,2));

$firstline = FALSE;

$unix_timestamp = $filectime;

 

} else {

// here we process remaining lines.

$pos = intval(substr($buffer,0,2))*86400 +

intval(substr($buffer,3,2))*3600 +

intval(substr($buffer,6,2))*60 +

intval(substr($buffer,9,2));

$elapsed = $pos - $start;

$unix_timestamp = $elapsed + filectime($filename);

}

The variable $buffer is a single line from the mp_log.txt file (as set near the start of this php file). We process each line and split out the strings for interpretation. Note if you set debug == true in the start of the code, there will be no writes to the database, and lots of debug written to the screen (including each string value).

 

 

//split lines into variables

list($ignore, $str1, $str2, $str3, $str4, $str5, $str6, $str7, $str8) = split(" ", $buffer, 9);

$str3 = trim($str3);

 

 

$test_array = explode("\"",$str2);

 

$pilot_callsign = str_replace(array('/', '\\' ,'\'', '[',']'), array('','','',''), $test_array[1]);

 

$pilot_ac = str_replace(array(')', '('), array('',''), $test_array[2]);

 

// TEST VARIABLES - DEBUG ONLY

 

if ($debug == true)

{

 

echo "str1 = ". $str1. " ";

echo "str2 = ". $str2. " ";

echo "str3 = ". $str3. " ";

echo "str4 = ". $str4. " ";

echo "str5 = ". $str5. " ";

echo "str6 = ". $str6. " ";

echo "str7 = ". $str7. " ";

echo "str8 = ". $str8. " <br>";

echo "<br>";

echo "actual_log_line= ".$buffer."<br>";

}

 

We then connect to the db (you must write your own code for that, but its like 5 lines only).

 

 

include_once('../include/db_connect.php');

 

The first type of kill we are going to determine is air to ground kills. It's really straight forward. If $str3 text = kill then we know its a kill of sometype, but if $str4 also = vehicle, we know there was a air to ground kill. We dont know if it was a TK or not, but that is determined subsequently. I chose to keep processing to a bare minimum and get the log into the DB as raw as possible. That way if you make a mistake in your post-processor code, you simply fix it and then run the code against the raw log database. Note the ugliness in splitting the pilot and victim names. These can easily be fixed, but leave that up to you! =)

 

//

if ($str3 == 'kill' and $str4 == 'vehicle') {

if(substr($str6,0,1) == "\"") {

$array_gu = array($str6, $str7);

$victim_gu_dirty = implode(" ", $array_gu);

$victim_gu = ereg_replace("\"*","",$victim_gu_dirty);

$victim_gu_pristine = ereg_replace("by", "", $victim_gu);

}

 

$pilot_team = $str1;

$victim_team = $str5;

$weapon = str_replace("by ","",$str8);

$weapon_dirty = ereg_replace("\(.*\)", "", $weapon);

$weapon_pristine = str_replace("-","_",$weapon_dirty);

 

$action = $str3;

$unit_type = $str4;

 

if ($debug == true)

{

echo " " .$unix_timestamp. " " .$pilot_team. " " .$pilot_callsign. " " .$pilot_ac. " " .$action. " " .$victim_team. " " .$unit_type. " " .$victim_gu_pristine. " " .$weapon_clean. "<br><br>";

} else {

$sql_wr = "INSERT IGNORE INTO stats (md5, timestamp , pilot_team , pilot_callsign , pilot_ac , action, victim_team, unit_type, victim_gu, weapon) VALUES('$md5', '$unix_timestamp' , '$pilot_team', '$pilot_callsign' , '$pilot_ac' , '$action', '$victim_team', '$unit_type', '$victim_gu_pristine', '$weapon_pristine')";

mysql_query($sql_wr) or die (mysql_error());

}

I think the rest is self explanatory as it is simply variations of the last code section described above.

 

 

Have fun.

 

Dodger

[Ezor]

As i already said in my PM, this is a excellent work !

 

Can you post the database create script ?

[dodger42]

No problem. I will send you the SQL file.

 

Regards,

Dodger

[Renato71]

Hi,

 

I'm trying to set up online stats for my squads comming dedicated server, and as you can see I put search feature to a good use :)

 

I managed to create a database and I used your script to insert mp_log data. Did some tweaking with file paths and managed to implement user upload into DB (will explain later, have to check it once more).

 

But I'm having some problems with the script as the longer victim names are not split correctly. Namely, strings 6, 7 and 8:

"S-300PS 5P85D ln" by AGM-65D Maverick

... are split into:

S-300PS 5P85D

and

ln" by AGM_65D Maverick

 

After some experimenting I come to conclusion that this part is the problematic one:

if ($str3 == 'kill' and $str4 == 'vehicle') {
if(substr($str6,0,1) == "\"") {
$array_gu = array($str6, $str7);
$victim_gu_dirty = implode(" ", $array_gu);
$victim_gu = ereg_replace("\"*","",$victim_gu_dirty);
$victim_gu_pristine = ereg_replace("by", "", $victim_gu);
} 

$pilot_team = $str1;
$victim_team = $str5;
$weapon = str_replace("by ","",$str8);
$weapon_dirty = ereg_replace("\(.*\)", "", $weapon);
$weapon_pristine = str_replace("-","_",$weapon_dirty);

 

First one creates victim name, and the other one a weapon name. I guess that implode function should be corrected, but how?

Or, for victim name, everything after and including by should be removed, and for weapon name everything before and including by ?

 

Example of current result using your unchanged script is here:

http://lockonhr.com/statsdb.php

 

Few additional questions...

 

1. I would like to include time in "real format", ie, like it appears in mp_log, not in currect format like "1222610337"

 

2. Tacview records events like "take off" and "landed near" - Could these two be exported into mp_log? (I guess this cis a subject for another thread)

 

3. How to set up an automatic insertion system of mp_log into my DB? Currently I do that manually for each file. Also, I would appreciate any help with displying the results (sorting etc)

 

4. Mugatu helped me with backup system for mp_log. Any chance for option to insert filenames of those mp_log copies into DB? Explanation of that is here: LUA question: How to create a copy of mp_log?

 

Thanks in advance,

[dodger42]

Hey Ranato, some ideas below. Bare in mind, I haven't touched lockon or lockon stats for years. I did re-upload the http://www.lomac-stats.com website for you to see.

 

1.

if($victim == 'S-300PS 5P85D ln') {

$victim_dirty = $victim;

}

else {

$victim_dirty = ereg_replace("\(.*\)", "", $victim);

}

$victim_pristine = str_replace("-","_",$victim_dirty);

 

Those are unix timestamps. Here is a sample conversion:

$unixtime = '1222611216'

$time = gmdate("Y-m-d H:i:s", $unixtime);

echo $time;

 

2. From memory, only landing is recorded. This makes it impossible to calculate flight time unfortunately.

 

3. For lomac-stats.com I used a cron like scheduler. In windows you can use a scheduler too. You just run the processor.

 

c:\php\php.exe c:\lomac\pre_processor.php

 

The database suppresses duplicates so you can just keep on piping in the logs. I ended up creating a lockon server launcher which submitting the mp_log.txt after each mission was finished. Unfortunately I don't have that launcher anymore.

 

4. You need to use the php scandir() function. You get an array returned which you can use as a while loop. The while loop would be calling the pre_processor.php file.

 

<?php

$dir = "c:\logs";

$dh = opendir($dir);

while (false !== ($filename = readdir($dh))) {

$files[] = $filename;

}

 

sort($files);

 

print_r($files);

 

?>

[Mugatu]

We use luasql, google it. It's pretty easy to set up.

 

Hi,

...

 

4. Mugatu helped me with backup system for mp_log. Any chance for option to insert filenames of those mp_log copies into DB? Explanation of that is here: LUA question: How to create a copy of mp_log?

 

Thanks in advance,

[Renato71]

Thanks for replies, guys :D

 

I'll try them later on today (to-evening, heh) and post results...

[Renato71]

Ouh... Advices not working. This code - wel to someone it may lok like a "language", but to me it looks like nothing. Just a puzzle that needs to be compiled somehow. Not able to see in such depth as you guys.

 

As for luasql - well, I don't know much about lua and sql, and combining those two together... Guys, I'm an electrician, not a programmer.

 

Sorry, I can't make this work...

[dodger42]

Sorry you didn't get it to work Renato. I'm not sure what you were trying to achieve with LUA.

[Renato71]

Sorry you didn't get it to work Renato. I'm not sure what you were trying to achieve with LUA.

 

Well, Mugatu suggested that (luasql), and not being very proficient with neither lua or sql, I kinda assumed lua is needed :D

As I googled it for a while, my head almost exloded while reading all that stuff...

 

I just gave up on doing it on my own, as this is way above my head, and I do not have enough spare time to learn everything from scratch. I'll try to contact some local guys - maybe someone of them will be interested to do this as a self-promoting project, but volunteer work in Croatia is grossly underestimated. Basically it is considered to be "a job for loosers and tree-hugging hippy capheads".

 

Thanks for your help guys. I'll keep you posted :)