Local Stats on MP Server (MySQL & AWK)

[raz0rsedge 10]

Hi there,

i'm new to this Forum, but not new to ArmA. I'm supporting ArmA since OFP and do my best spreading the word about this awesome non-arcade-military-game.

I have a warfare server for CO/OA which is runnin fine, from the generated serverlog.txt I extract some stats (egrep discards useless data for me) like this:

name="mich@l *cz*";

customScore=24;

killsTotal=53;

killed=2;

name="INF_tr0y";

customScore=0;

killsTotal=1;

killed=0;

name="Mr Chivas";

customScore=0;

killsTotal=0;

killed=0;

name="[HB] Philipp";

customScore=0;

killsTotal=0;

killed=0;

name="[HB]Dan";

customScore=0;

killsTotal=0;

killed=0;

It's a linux machine and I'd like to generate some small stats tables from this textfile, therefore I've written a php-script that takes these 4 values and writes them into a MySQL-Database. Now the only thing I need is awk to generate some useable SQl syntax from this parsed serverlog file.

I'm not familiar with awk, and here's the error. It generates from above values not the needed SQL syntax:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `phpkit2`.`Players` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL AUTO_INCREMENT,
 `customScore` int(11) NOT NULL,
 `killsTotal` int(11) NOT NULL,
 `killed` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
INSERT INTO `Players` (`name`, `customScore`, `killsTotal`, `killed`) VALUES
(1,             name="Ats Puu", ^M, ),
(2,             customScore=1, ^M, ),
(3,             killsTotal=6, ^M, ),
(4,             killed=3, ^M, ),
(5,             name="Dennis2000", ^M, ),

What I expected is something like this:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `phpkit2`.`Players`(
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) character set utf8 collate utf8_bin NOT NULL default '',
 `customScore` int(11) NOT NULL,
 `killsTotal` int(11) NOT NULL,
 `killed` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
 )ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `Players` (`name`, `customScore`, `killsTotal`, `killed`) VALUES
 ('Ats Puu', '1', '6', '3'),
 ('Peter', '2', '8', '4');

maybe someone of you guys can help getting this lil thing done, so we can make a bundle for server-owners to generate their own lil stats...

Here's my faulty awk source which should be executed by a cron-job:

BEGIN {

FS=";"

printf "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n"

printf "CREATE TABLE IF NOT EXISTS `phpkit2`.`Players` (\n"

printf " `id` int(11) NOT NULL AUTO_INCREMENT,\n"

printf " `name` varchar(100) NOT NULL AUTO_INCREMENT,\n"

printf " `customScore` int(11) NOT NULL,\n"

printf " `killsTotal` int(11) NOT NULL,\n"

printf " `killed` int(11) NOT NULL,\n"

printf " PRIMARY KEY (`id`),\n"

printf " KEY `name` (`name`)\n"

printf ") ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;\n"

printf "INSERT INTO `Players` (`name`, `customScore`, `killsTotal`, `killed`) VALUES\n"

}

{

{if (NR == 1) printf ""

else printf ",\n"}

printf "(" NR ", "

printf $1", "

printf $2", "

printf $3")"

}

END{

printf ";\n"

}

Maybe some helpful guy can teach me how to rewrite the awk script to generate a useable SQL STATEMENT (awk -f awkscript serverlog.txt > inc.stats.php.

I tried many howto's and websites to get behind the internal work of AWK, but I only understand that it's parsing the lower part of the awkscript once per line in serverlog.txt

help may be apreciated,

raz0rsedge

http://silent-forces-clan.de/sfc/include.php?path=playerstats.php

[nuxil 2]

umm.

i dont think you should do this by awk.

you need to make a parser for class Session

there can be multiple class Session in the log. so you need to handle them correct.

also you should not grab data from a ongoing mission, because. the class Session is not done.

if you have a ongoing misssion the class Session may look like

class Session
{
   mission="c&h120 Valhalla Vyshnoye v.0.98 beta";
   island="Chernarus";
   gameType="Hold";
   duration=3725.2742;

   class Player1
   {
      ...
      ...
   };

in other words. its missing the last bracket. to close the session.

if you have the knowhow for a programming language i suggest you take the time to write a small app to do this.

ther are many crossplatfrom languages. such as Python,Ruby etc etc. but i prefer Python

im working on a serverlog parser. which stores the data into a sqlite database.

with some minor changes it can do mysql aswell.

im far from done with it. but its starting to to form well.

here is a picture what i done so far.

Edited February 16, 2011 by nuxil

[raz0rsedge 10]

Your work looks very nice... but for the moment I'll only do a serverwide overview.

Therefore the serverlog is parsed to an easier format by egrep & co.

I'll update this thread if the awk thingy is solved...

Thanks anyway...

[1para{god-father} 105]

Hi Nuxil,

This looks really good have you made any further progress on this ?

Cheers

[nuxil 2]

Ofcource :)

http://jointheriot.org/arma2stats/server1/missionhistory.php?ArmA2_Stats=2011-03-16.db

this is how its currently looks like

[1para{god-father} 105]

WOW looks really good is it up and running ? it is just what i am looking for to keep track of my missions, will you be releasing this to the community ?

[nuxil 2]

yes its up and running. but only beta version

i'll need to test it a bit more before i can say for sure when the public gets it.

so far i think i fixed the most seiouse bugs. but there are a few more tests i need to do before i can promise anything.

the program is also running on a popular server. hosted by dao. kochleffel is so kind doing some beta testing for me.

so far it looks good. but i dont want to release some 1/2 finished application that requiers me to make like updates each day.

also i need to change the php&css files to a more common themplate. i dont want every one who want to use this app to have a clone of our site.

i'll let you know how dev is progressing :)

[1para{god-father} 105]

Hi Nuxil,

Any further news , as really would like to put this into my Mission to Capture stats !

Cheers