Statistics Based on Server Log File

Donnovan · June 8, 2014

Rai,

I created a schedule in windows to run this SQL statement every 10 minutos.

This SQL statement read the DayZ Epoch server log file and search for lines related to:

- Sells

- Buys

- Kills

- Running Over (a kill where the kill weapon is a vehicle)

After find the lines it create 4 tables in your dayz_epoch data base (along with the original tables, like object_data, player_data, etc..., but not in then). Those new tables are, respectively:

table SELLS:

PLAYER_NAME, PLAYER_UID, PRODUCT, TRADER, VALOR, CURRENCY, UNITY, QUANTITY

table BUYS:

PLAYER_NAME, PLAYER_UID, PRODUCT, TRADER, VALOR, CURRENCY, UNITY, QUANTITY

table KILLS:

KILLER, VICTIM, WEAPON, DISTANCE

table RUNNING_OVER:

VICTIM, DRIVER, VEHICLE

PS: It also create a auxiliar table called CURRENCY.

IMPORTANT:

The SQL statement bellow does not mess with the original DayZ Epoch data tables. But since the use of it require some knowlegdge about MySql and i can't garantee the persons using this file will have it or even if my own knowledge is sufficient, i ask you to just use this intended help if you don't mind to be at your own if something strange happens.

DROP TABLE IF EXISTS SERVER_RPT;
CREATE TABLE SERVER_RPT(LOG_TXT VARCHAR(1000)) ENGINE=MyISAM;

LOAD DATA LOCAL INFILE 'E:/Steam/steamapps/common/Arma 2 Operation Arrowhead/instance_11_Chernarus/arma2oaserver.RPT'
INTO TABLE SERVER_RPT
LINES TERMINATED BY '\r\n';

DROP TABLE IF EXISTS KILLS;
CREATE TABLE KILLS(VICTIM VARCHAR(48), KILLER VARCHAR(48), WEAPON VARCHAR(48), DISTANCE MEDIUMINT) ENGINE=MyISAM;

INSERT INTO KILLS
SELECT SUBSTRING(LOG_TXT, 18, LOCATE(' was killed by ', LOG_TXT) - 18) AS VICTIM, SUBSTRING(LOG_TXT, LOCATE(' was killed by ', LOG_TXT) + 15, LOCATE(' with weapon ', LOG_TXT) - (LOCATE(' was killed by ', LOG_TXT) + 15) + 1) AS KILLER, SUBSTRING(LOG_TXT, LOCATE(' with weapon ', LOG_TXT) + 12, LOCATE(' from ', LOG_TXT) - (LOCATE(' with weapon ', LOG_TXT) + 12) + 1) AS WEAPON, SUBSTRING(LOG_TXT, LOCATE(' from ', LOG_TXT) + 6, LENGTH(LOG_TXT) - 2 - (LOCATE(' from ', LOG_TXT) + 6) + 1) AS DISTANCE
FROM SERVER_RPT
WHERE LOG_TXT LIKE '%"PKILL: %';

DELETE FROM KILLS WHERE DISTANCE = 0;

DROP TABLE IF EXISTS RUNNING_OVER;
CREATE TABLE RUNNING_OVER(VICTIM VARCHAR(48), DRIVER VARCHAR(48), VEHICLE VARCHAR(48)) ENGINE=MyISAM;

INSERT INTO RUNNING_OVER
SELECT SUBSTRING(LOG_TXT, 18, LOCATE(' was killed by ', LOG_TXT) - 18) AS VICTIM, SUBSTRING(LOG_TXT, LOCATE(' was killed by ', LOG_TXT) + 15, LOCATE(' with weapon ', LOG_TXT) - (LOCATE(' was killed by ', LOG_TXT) + 15) + 1) AS DRIVER, SUBSTRING(LOG_TXT, LOCATE(' with weapon ', LOG_TXT) + 12, LOCATE(' from ', LOG_TXT) - (LOCATE(' with weapon ', LOG_TXT) + 12) +1) AS VEHICLE
FROM SERVER_RPT
WHERE LOG_TXT LIKE '%"PKILL: %' AND LOG_TXT LIKE '% from 0m"%';

DROP TABLE IF EXISTS CURRENCY;
CREATE TABLE CURRENCY (CURRENCY varchar(48), UNITY VARCHAR(16), QUANTITY INT) ENGINE=MyISAM;

INSERT INTO CURRENCY
VALUES ('ItemBriefcase100oz','Gold',100),('ItemGoldBar','Gold',1),('ItemGoldBar10oz','Gold',10),('ItemSilverBar','Silver',1),('ItemSilverBar10oz','Silver',10);

DROP TABLE IF EXISTS SELLS;
CREATE TABLE SELLS(PLAYER_NAME VARCHAR(48), PLAYER_UID VARCHAR(48), PRODUCT VARCHAR(48), TRADER VARCHAR(48), VALOR MEDIUMINT, CURRENCY VARCHAR(48), UNITY VARCHAR(16), QUANTITY MEDIUMINT) ENGINE=MyISAM;

INSERT INTO SELLS(PLAYER_UID, PRODUCT, TRADER, VALOR, CURRENCY)
SELECT SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -9), ' ', 1), 2, LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -9), ' ', 1))-2) AS PLAYER_UID, SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -6), ' ', 1) AS PRODUCT, SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -4), ' ', 1) AS TRADER, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -2), ' ', 1), 'x', '') AS VALOR, REPLACE(SUBSTRING_INDEX(LOG_TXT, ' ', -1), '"', '') AS CURRENCY
FROM (SELECT REPLACE(REPLACE(REPLACE(LOG_TXT, 'Unknown Trader City', 'Unknown'), 'Hero Trader', 'Hero'), 'Bandit Trader', 'Bandit') AS LOG_TXT FROM SERVER_RPT) AS SERVER_RPT
WHERE LOG_TXT LIKE '% "EPOCH SERVERTRADE: Player: %' AND LOG_TXT LIKE '% sold a %';

UPDATE SELLS
SET SELLS.PLAYER_NAME = (SELECT PlayerName FROM player_data WHERE player_data.PlayerUID = SELLS.PLAYER_UID), SELLS.UNITY = (SELECT CURRENCY.UNITY FROM CURRENCY WHERE CURRENCY.CURRENCY = SELLS.CURRENCY), SELLS.QUANTITY = (SELECT CURRENCY.QUANTITY * SELLS.VALOR FROM CURRENCY WHERE CURRENCY.CURRENCY = SELLS.CURRENCY);
	
DROP TABLE IF EXISTS BUYS;
CREATE TABLE BUYS(PLAYER_NAME VARCHAR(48), PLAYER_UID VARCHAR(48), PRODUCT VARCHAR(48), TRADER VARCHAR(48), VALOR MEDIUMINT, CURRENCY VARCHAR(48), UNITY VARCHAR(16), QUANTITY MEDIUMINT) ENGINE=MyISAM;

INSERT INTO BUYS(PLAYER_UID, PRODUCT, TRADER, VALOR, CURRENCY)
SELECT SUBSTRING(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -9), ' ', 1), 2, LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -9), ' ', 1)) - 2) AS PLAYER_UID, SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -6), ' ', 1) AS PRODUCT, SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -4), ' ', 1) AS TRADER, REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(LOG_TXT, ' ', -2), ' ', 1), 'x', '') AS VALOR, REPLACE(SUBSTRING_INDEX(LOG_TXT, ' ', -1), '"', '') AS CURRENCY
FROM (SELECT REPLACE(REPLACE(REPLACE(LOG_TXT, 'Unknown Trader City', 'Unknown'), 'Hero Trader', 'Hero'), 'Bandit Trader', 'Bandit') AS LOG_TXT FROM SERVER_RPT) AS SERVER_RPT
WHERE LOG_TXT LIKE '% "EPOCH SERVERTRADE: Player: %' AND LOG_TXT LIKE '% bought a %';

UPDATE BUYS
SET BUYS.PLAYER_NAME = (SELECT PlayerName FROM player_data WHERE player_data.PlayerUID = BUYS.PLAYER_UID), BUYS.UNITY = (SELECT CURRENCY.UNITY FROM CURRENCY WHERE CURRENCY.CURRENCY = BUYS.CURRENCY), BUYS.QUANTITY = (SELECT CURRENCY.QUANTITY * BUYS.VALOR FROM CURRENCY WHERE CURRENCY.CURRENCY = BUYS.CURRENCY);

CONFIG IT TO YOUR SYSTEM:

Change the green part of line number 3:

LOAD DATA LOCAL INFILE 'E:/Steam/steamapps/common/Arma 2 Operation Arrowhead/instance_11_Chernarus/arma2oaserver.RPT'

To meet the place of your DayZ Epoch Server log file, the arma2oaserver.RPT file.

Thankyou!

Donnovan · June 8, 2014

Table SELLS:

Table KILLS:

Table RUNNING_OVER:

Donnovan · June 8, 2014

Here my update_from_log.bat file:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" --user=YourDbUser --password=YourDbPassword --host=localhost --port=3306 "dayz_epoch" <  "D:\DayZ_Backup\CREATE LOG TABLES.sql"

1) The SQL in the first post is on the file D:\DayZ_Backup\CREATE LOG TABLE.sql.

2) C:\Program Files\MySQL\MySQL Server 5.7\ it's where MySql Server is installed.

3) "dayz_epoch" is the default name for the Data Base.

4) --host=localhost is the ip of the machine running MySql Server, localhost means it's in the same machini running the .bat file.

If you schedule this .bat in Windows Schedule Manager, you can run it periodically and update your extra statistic tables with the frequency you want.

raziel23x · June 8, 2014

what would you suggest if we want to add some php to the mix and have it be printed out on a webpage

Donnovan · June 9, 2014

I suggest you to do that. Nice idea.

We can add new server messages to create new statistics.

raziel23x · June 9, 2014

like bans and kick messages

raziel23x · June 9, 2014

even side chat

Donnovan · June 10, 2014

Some statistics about the chat would be nice.

INPORTANT:

Its neat to take a look at your log file healthy if you want to use that.

I just installed SARGE AI, and due to a change in the way Arma 2 OA engine respond to manipulation of null strings, SARGE AI is giving tons of errors messages. Those errors made my arma2oaserver.rpt reach 900 Megabytes. MySql did well even on the 900 Megabytes rpt file, but this is not a good thing to happen.

raziel23x · June 10, 2014

yea my rpt gets up to 400 MB depending on the server as we have 2 dedicated boxes each with 2 servers on it and we just got a third dedicated box where we are putting up 5 fifth server and possibly a sixth one.

2 Chernarus servers 50/45 players

1 Overpoch Chernarus Server 45 players

1 Panthera Server 45 players

and working on setting up a overpoch Napr server now either a 45 or 50 players

Defent · June 10, 2014

You can check my example if you want to print chat and kills on the website, without having them saved to the database. The database would get really proper full if you saved chat.

You can also use it to find bans, php scripts are quite customizable.

I'm pretty sure you can use it to read from the database and print it out as well.

Donnovan · June 12, 2014

Nice raziel23x. Its a lot of servers!

Thanks Defent, this will help on the site project.

IMPORTANT FIND

There is a player on my server with special characters in his name. This is broking the string manipulation in a very stange way. MySql believe that the lines with his name are bigger than it really is, causing errors.

Donnovan · June 12, 2014

I want to put date and time on the log messages so i can know when the event happned. It's not the virtual ingame date and time, but real world date and time...

diag_log format["EPOCH SERVERTRADE: Player: %1 (%2) sold a %3 in/at %4 for %5",(name _player), (getPlayerUID _player), _classname, _traderCity, _price];

...but to do that i need a function that return the date and time of the server machine, and i can't find it. Anyone know such function?

Thankyou!

itsatrap · June 20, 2014

hmm, why drop the database when update ?

Donnovan · June 22, 2014

itsatrap,

Because its not a aditive process, its completely redone at each run. The historic you will have is the historic of the actual log file (arma2oaserver.rpt file).

Donnovan · June 24, 2014

Guys!

This above is geting old.

Right now we are experimenting with Arma2NET and MySQL for Arma2NET so we can trow statistic directly from the game to the MySQL database (thanks Herbert for the find).

One of the last detected problem happens because we mess with multiplayer public event handlers and this is kicking the player from the game with MPEventhandler Restriction #0 message!

I'm not sure how to avoid this kick since i never really had understand the BE filters.

Soon, i will post here asking for help.

Update: Problem with BE Filters Fixed! Allowed my custom multiplayer public event handler in MPEventhandlers.txt, publicvariables.txt, publicvariablesval.txt and remoteexec.txt!

Donnovan · June 26, 2014

It's working!

I'm just thinking in the way to release it...

1) Stand alone: Pretty independent.

-BATTLEYE FILTERS: Change in BE filters to allow new public event handler.

-NEW FILES PLACEMENT: New directory DONN inside server pbo file.

-FILES CHANGED: New line at the end of server file init\server_functions.sqf

or...

2) Invasive: More right to the point.

-BATTLEYE FILTERS: none.

-NEW FILES PLACEMENT: none.

-FILES CHANGED: Replace 3 files (server_playerDie.sqf, server_traderObj.sqf, fnc_plyrHit.sqf) on the server pbo file.

Also there is another steps connon to the two instalation ways:

- Create the @Arma2NET directory inside your Arma2 OA directory (this folder is downloaded from the Arma2NET MySql project)*.

- Add the @Arma2NET mod to your server launcher bat.

- Install the MySql NET Conector 6.7.5**.

- Run a SQL file in your MySql database (like you have run epoch.sql for the Epoch Server) to create the dayz_epoch_stats database where the stats will be placed.

* Arma2NET and Arma2NET MySql are different things. The first is the pure Arma2NET with some default function like server time function to make the server computer time acessible by the game .sqf scripts or command line function to make the game execute a comand line, like a bat file, from the .sqf scripts. And Arma2NET MySql is all this plus the abillity to execute any SQL statement from the game .sqf scripts. The two projects provide a mod folder for you with the name @Arma2NET.

** you may need to unistall newer version if you have it, 6.8.3 for example, because the last compiled Arma2NET MySql files was compiled for 6.7.x version of NET Conector.

This all seens complicated, but is rather simple.

Donnovan · June 26, 2014

CUSTOM STATISTIC YOU ALSO CAN HAVE:

Player "NEARNESS": Write to the MySql database each time a player is less than 50 meters away from another. When the players are on the same vehicle, the distance will be show as 0 meters.

The "usefulness" of this is already in search (lol), but i already got some very nice info from that:

- You know players are friends, because they are always near each other.

- You know players that have spoted other players, because they was near for a small amount of time.

- You have all that stored in the database, so you can do a timeline of "nearness".

But, this above is just the first Unseen Statistcs test.

The most obvious statistics will be allways there:

- PvP kills (server_playerDie.sqf).

- All Buys on Traders (server_traderObj.sqf).

- All Sells on Traders (server_traderObj.sqf).

- All PvP weapon hits (fnc_plyrHit.sqf).

Everything with player name and id.

NEXT STEP:

The web site to show it.

Donnovan · July 26, 2014

Sorry for the big delay. Here is the first incoming of the online custom statistics system:

http://177.54.147.233/index.asp

The kill happens on the server, the kill show up in this page.

raziel23x · July 26, 2014

Sorry for the big delay. Here is the first incoming of the online custom statistics system:http://177.54.147.233/kills.aspThe kill happens on the server, the kill show up in this page.

The link no works

Donnovan · July 26, 2014

The link no works

Thanks for pointing this raziel23x!

I changed the file name, and forgot to update here.

Here is the one ok: http://177.54.147.235/index.asp

itsatrap · July 26, 2014

Hi

Did you change the Pkill rpt log to get the uid?

I drop the idea of getting it from SQL since there where a lot of places there was it could get the wrong id

Donnovan · July 27, 2014

Hi itsatrap,

I made a small insertion of code in the server file server_playerDied.sqf.

This is my custom server_playerDied.sqf, the custom code starts in //DONN MYSQL BEGIN and ends in //DONN MYSQL END:

private ["_characterID","_minutes","_newObject","_playerID","_infected","_victim","_victimName","_killer","_killerName","_weapon","_distance","_message","_loc_message","_key","_death_record"];
//[unit, weapon, muzzle, mode, ammo, magazine, projectile]
_characterID = 	_this select 0;
_minutes =		_this select 1;
_newObject = 	_this select 2;
_playerID = 	_this select 3;
_infected =		_this select 4;
if (((count _this) >= 6) && {(typeName (_this select 5)) == "STRING"} && {(_this select 5) != ""}) then {
	_victimName =	_this select 5;
} else {
	_victimName =  if (alive _newObject) then {name _newObject;} else {"";};
};
_victim = _newObject;
_newObject setVariable ["bodyName", _victimName, true];

_killer = _victim getVariable["AttackedBy", "nil"];
_killerName = _victim getVariable["AttackedByName", "nil"];

// when a zombie kills a player _killer, _killerName && _weapon will be "nil"
// we can use this to determine a zombie kill && send a customized message for that. right now no killmsg means it was a zombie.
if ((typeName _killer) != "STRING") then
{
	_weapon = _victim getVariable["AttackedByWeapon", "nil"];
	_distance = _victim getVariable["AttackedFromDistance", "nil"];

	if ((owner _victim) == (owner _killer)) then 
	{
		_message = format["%1 killed himself",_victimName];
		_loc_message = format["PKILL: %1 killed himself", _victimName];
	}
	else
	{
		_message = format["%1 was killed by %2 with weapon %3 from %4m",_victimName, _killerName, _weapon, _distance];
		_loc_message = format["PKILL: %1 was killed by %2 with weapon %3 from %4m", _victimName, _killerName, _weapon, _distance];

		//DONN MYSQL BEGIN
		_a2n_sql_run = format [
			"Arma2NETMySQLCommand ['dayz_epoch_stats', 'INSERT INTO KILLS VALUES ('%1',NULL,'%8','%2','%3','%9','%4','%5','%6',%7)']",
			don_date_time,
			_victimName,
			_playerID,
			_killerName,
			getPlayerUID _killer,
			_weapon,
			_distance,
			"PLAYER",
			"PLAYER"
		];
		diag_log format["STATS A2N KILL: %1", _a2n_sql_run];
		"Arma2Net.Unmanaged" callExtension _a2n_sql_run;
		//DONN MYSQL END
	};

	diag_log _loc_message;

	if(DZE_DeathMsgGlobal) then {
		[nil, nil, rspawn, [_killer, _message], { (_this select 0) globalChat (_this select 1) }] call RE;
	};
	/* needs customRemoteMessage
	if(DZE_DeathMsgGlobal) then {
		customRemoteMessage = ['globalChat', _message, _killer];
		publicVariable "customRemoteMessage";
	};
	*/
	if(DZE_DeathMsgSide) then {
		[nil, nil, rspawn, [_killer, _message], { (_this select 0) sideChat (_this select 1) }] call RE;
	};
	if(DZE_DeathMsgTitleText) then {
		[nil,nil,"per",rTITLETEXT,_message,"PLAIN DOWN"] call RE;
	};

	// build array to store death messages to allow viewing at message board in trader citys.
	_death_record = [
		_victimName,
		_killerName,
		_weapon,
		_distance,
		ServerCurrentTime
	];
	PlayerDeaths set [count PlayerDeaths,_death_record];

	// Cleanup
	_victim setVariable["AttackedBy", "nil", true];
	_victim setVariable["AttackedByName", "nil", true];
	_victim setVariable["AttackedByWeapon", "nil", true];
	_victim setVariable["AttackedFromDistance", "nil", true];
};

// Might not be the best way...
/*
if (isnil "dayz_disco") then {
	dayz_disco = [];
};
*/

// dayz_disco = dayz_disco - [_playerID];
_newObject setVariable["processedDeath",diag_tickTime];

if (typeName _minutes == "STRING") then
{
	_minutes = parseNumber _minutes;
};

diag_log ("PDEATH: Player Died " + _playerID);

if (_characterID != "0") then
{
	_key = format["CHILD:202:%1:%2:%3:",_characterID,_minutes,_infected];
	#ifdef DZE_SERVER_DEBUG_HIVE
	diag_log ("HIVE: WRITE: "+ str(_key));
	#endif
	_key call server_hiveWrite;
}
else
{
	deleteVehicle _newObject;
};

Here just the custom part:

//DONN MYSQL BEGIN
_a2n_sql_run = format [
	"Arma2NETMySQLCommand ['dayz_epoch_stats', 'INSERT INTO KILLS VALUES ('%1',NULL,'%8','%2','%3','%9','%4','%5','%6',%7)']",
	don_date_time,
	_victimName,
	_playerID,
	_killerName,
	getPlayerUID _killer,
	_weapon,
	_distance,
	"PLAYER",
	"PLAYER"
];
diag_log format["STATS A2N KILL: %1", _a2n_sql_run];
"Arma2Net.Unmanaged" callExtension _a2n_sql_run;
//DONN MYSQL END

Hope the code is not too big those show here that way.

Donnovan · July 29, 2014

Link updated to: http://177.54.147.235/index.asp

Donnovan · July 30, 2014

This one, bellow, does not use the custom statistics, it uses the build-in tables object_data and character_data:

http://177.54.147.235/objects_arround.asp?X=5789&Y=8648&Rad=45

Altough, its a quite nice tool for admins.

It show Construction Objects, Vehicles, Destroyed Vehicles and Alive Players arround one point of the map.

It show Alive Players even if they arent on the server, the next update will show only online players, and for that i will need to go custom.

I have in my server a custom player action that show X and Y position.

Example of use 1:

Player: Admin, i can't put my plot pole.

Admin: Give me your position.

Player: 5789 and 8648.

Admin: One sec please.

Admin: There is already a plot pole near you. Can't you see it?

...

Example of use 2:

Player: I lost connection and now i can't see the vehicle i was in!

Admin: Tell me your position please.

Player: I'm sit down.

Admin: No...

Player: Ah, sorry! It's 5634 and 12033.

Admin: What vehicle?

Player: Its a BRDM.

[10 seconds]

Admin: There is a BRDM 250 meters from your position.

Player: What direction?

Admin: Sorry, use your spider sense.

Player: LOL...

[3 seconds]

Player: Really, what direction?

Player: Admin?

Example of use 3:

Player: Can you tell if there is someone alive in Stary. I want to camp with my sniper there.

Admin: No.

Player: :( no one in Stary?

Admin: No, can't tell you that.

Player: It's the F2 key, then you turn icons on.

Player: Admin?

Admin: We have Gebriel Safe Zones.

Player: OH NOOOOOOOES!!!!

...

Shep · August 10, 2014

OMG... i need this please! :(

Statistics Based on Server Log File

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Please sign in to comment