How to create statistics for your server

Hi everyone,

I'd like to post here on how you can create a statistics page for your servers, as I know many servers want to have it.

Just for example, here is a simple statistics page we are running for the DCS Israel nowadays for the on going campaigns we are running -

http://89.163.173.82/DCS/Default.html

Now, this is fairly simple to do, although you do need to know what you are doing in order to set it up and maintain until I get time to try and automate everything.

I will not explain how to setup the infrastructure of the DB/WebServer/PHP

What you will need -

1. MySQL.

2. Apache Web Server.

3. Easy PHP 5.3.5.

*You can also use WAMP for a simpler way to have it all setup, but a complete install is more reliable.

**You can use the following website, it has an excellent guide -

http://www.sitepoint.com/php-amp-mysql-1-installation/

Setting up the environment and getting the website up -

Now that we have a Web Server working with PHP and a DB behind it, we have all we need to start creating our statistics site.

1. The first thing we should do, is create the DB (Check the previous .

2. Next step will be creating a table to use:

a. Log into your DB (Using Query Browser/SQL Yog etc..)

b. Create a table by running the following query on your DB -

CREATE TABLE `TABLENAME` --change to your tablename
(
 `Time` TIME,
 `Event` VARCHAR(255),
 `InitiatorID` INT,
 `InitiatorCoa` VARCHAR(255),
 `InitiatorGroupCat` VARCHAR(255),
 `InitiatorType` VARCHAR(255),
 `InitiatorPlayer` VARCHAR(255),
 `WeaponCat` VARCHAR(255),
 `WeaponName` VARCHAR(255),
 `TargetID` INT,
 `TargetCoa` VARCHAR(255),
 `TargetGroupCat` VARCHAR(255),
 `TargetType` VARCHAR(255),
 `TargetPlayer` VARCHAR(255)
)

3. Next, we will have to actually get information out of the game into a file and then into our DB. The first part is done automatically using a script I built:

a. Go into your mission, in the initialization script that start with the mission add the following - https://www.dropbox.com/s/uphnkbcrkr1dxak/eStatHandler1.02.lua?dl=0

b. In your mission, do a trigger that runs according to whenever you want to save stats and runs the following script - https://copy.com/xgdLUv0ZfFYK

c. We now need to tell DCS to enable us the option to save a file, go into the following folder - DCS World\Scripts\ and change the marked lines to look as follows in the file - MissionScripting.lua -

do
sanitizeModule('os')
[b]--sanitizeModule('io')
--sanitizeModule('lfs')[/b]
require = nil
loadlib = nil
end

d. Make sure to create the folder Save_stat in your Saved Games\DCS\Logs folder.

*Example of a CSV file - https://copy.com/ipElh4ybWLpM

4. Next step, Now once you run the mission and the trigger to save stats a CSV file will be saved into Saved Games\DCS\Logs\Save_stat

That file is to be loaded to the DB using the following query -

LOAD DATA INFILE 'C:/Users/Coop/Saved Games/DCS/Logs/Save_stat/EventStat7257.201.csv'  --change to your file name and path
INTO TABLE `TABLENAME` --change to your tablename
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES

5. After we have loaded the statistics information into the DB, we now have to create smart queries to take out the correct information as shown in my first example.

Here is the example of this page - http://89.163.173.82/DCS/INT/Stat_INT_18_05.php

PHP file example - https://copy.com/3ZooW1cFKGYB

This is something you will have to figure out for yourselves on how you want the page to be displayed and which data you want to be shown.

I know some of this is hard to understand, I tried to be specific and give info as I can in the limited time I have.

For any help, I'll be coming over to this thread every once in a while and try to answer questions.

I hope this is a start of many many statistic pages and whatever other ideas anyone has in regards to getting information into a webpage about a running server :)

:thumbup: Thanks!

Good effort xcom. However, The real question is does DCS accurately generate logs itself? Often, kills get logged as crashes. This is good to get automated stats but you'll have to go manually through Tacview and see if there are any discrepancies with what actually happened. From my own experience, DCS does not log events accurately, especially kills. Ganaalma from the 36th squadron came up with a Tacview-based solution during SATAC 2013, then when I went through the actual recording, more than half of the kills weren't there or were saved as a crash.

This guide is first so people would understand how they can create a website that works with a working server.

About logging kills, the script I've made writes down these handler information -

if e.id == world.event.S_EVENT_HIT 
or e.id == world.event.S_EVENT_EJECTION
or e.id == world.event.S_EVENT_BIRTH
or e.id == world.event.S_EVENT_CRASH
or e.id == world.event.S_EVENT_DEAD
or e.id == world.event.S_EVENT_PILOT_DEAD then

S_EVENT_CRASH events are logged for information only, the following events are relevant for kills -

S_EVENT_EJECTION, S_EVENT_DEAD, S_EVENT_PILOT_DEAD.

There is inaccurate information, I've also found them myself but it is not relevant to the S_EVENT_CRASH event.

I've noticed 2 issues which I've reported -

http://forums.eagle.ru/showpost.php?p=2066271&postcount=38

Until ED does not fix these issues, there is no real way (AFAIK) to have good QA on the data exported (especially because of duplicate records).

Yes, absolutely. MP logs were much more accurate in FC2, hence automated stats were implemented by many servers. I'm not sure what's causing those issues in DCS. I hope ED will fix that soon, because and I hope I'm wrong, the only way to get a proper debrief right now is to generate a Tacview file and extract relevant logs manually, a bit of a PITA I would say.

Basically my scripts export the data correct except for both issues I indicated which are very rare, which makes this way a lot easier than going into TacView and getting logs out manually for each mission, especially in large scale missions which this is aimed for.

Regarding Tacview, You would have to check if the issues I indicated are shown as well in Tacview logs or not to be sure TacView is showing the correct information aswell.

I suggest checking in large scale missions of above 500 units.

Great job xcom! Don't mind the bugs, let's hope they eventually will be fixed :) Also FC2 was full of bugs when it came to scoring kills.

Basically my scripts export the data correct except for both issues I indicated which are very rare, which makes this way a lot easier than going into TacView and getting logs out manually for each mission, especially in large scale missions which this is aimed for.

 

Regarding Tacview, You would have to check if the issues I indicated are shown as well in Tacview logs or not to be sure TacView is showing the correct information aswell.

I suggest checking in large scale missions of above 500 units.

Well, I'll give your method a go next time, see how accurate the logs will be compared to the track/Tacview and report back. Thanks for your effort.

Nice!

Ya... I need to get up to speed on MySQL and Queries and etc etc etc. Have no idea about PHP though.

Xcom,

Thanks for all the work you put into this. I have everything working but I did have to make one change and I want to make sure it is not messing anything up.

When creating my table I had to change the following field from

`TargetID` INT,

to

`TargetID` VARCHAR(255),

in order for the CSV file to load properly. INT was giving an error when running the query to load the data.

Thanks again,

Eric

Hi Eric,

That should be fine, the field TargetID only has numbers, that's why I thought INT should work fine, but VARCHAR should also work with no issue.

Also, if you guys have any additions or improvements, please share :)

Thanks xcom, cool stuff.

I was fiddling with a python script that does the same but bypasses the database thingy.

So after much adventure and a fair amount of copying your work, i came up with this:

https://github.com/crash-horror/dcs-mission-statistics-xcom

The actual script:

https://github.com/crash-horror/dcs-mission-statistics-xcom/blob/master/get-mission-stats.py

A binary(exe):

https://github.com/crash-horror/dcs-mission-statistics-xcom/releases/download/v0.16/get-mission-stats-v016.rar

It parses the CSV file that your other scripts generate and creates a html page.

I am a complete amateur so bear with the ugly code :music_whistling:

Also it seems to work with all the csv files I managed to get my hands on, except the one from last Sunday's (May 25) Caucasus Blizzard. Did you change sthing on the server?

Thanks again, cheers.

Hey Crash,

Great stuff man!

Greg showed me your script and I tried it, it did work great with one of the older CSV file I tried.

To your question, I haven't changed anything with the CSV output, maybe there's some value that your script has troubles with?

I tried running it here and it also failed with the last CSV, I got the following error -

Traceback (most recent call last):
 File "c:\Python34\lib\site-packages\cx_Freeze\initscripts\Console.py", line 27
, in <module>
 File "get-mission-stats.py", line 300, in <module>
 File "get-mission-stats.py", line 126, in event_table
UnboundLocalError: local variable 'eventROW' referenced before assignment

Check the CSV table, there are some null values due to the issues I wrote earlier.

BTW, love your youtube vids, great stuff!

Fixed!

Indentation error:

https://github.com/crash-horror/dcs-mission-statistics-xcom/commit/0d96aab0c162bc4a6e71843b7fb4b19d48e8dcc0

Binary:

https://github.com/crash-horror/dcs-mission-statistics-xcom/releases/download/v0.16/get-mission-stats-v016.rar

:pilotfly:

Fixed!

Indentation error:

https://github.com/crash-horror/dcs-mission-statistics-xcom/commit/0d96aab0c162bc4a6e71843b7fb4b19d48e8dcc0

 

Binary:

https://github.com/crash-horror/dcs-mission-statistics-xcom/releases/download/v0.16/get-mission-stats-v016.rar

 

:pilotfly:

This is EXCELLENT!

Is it possible to make it exclude players based on their tags and to format it like

callsign | flighttime | A/A kills | A/G Kills

That way I could have it show only squadron members and instead of going into specifics as to what exactly people did and how they did it, just have the result (player-x killed a ground unit)

Then I could merge all the CSV files (I think) generated in a week and manually update the table weekly until I find a way to automate it.

EDIT:

Outputted CSV files have event births but don't seem to recognize weapons, kills, or more than one of any type of unit.

EDIT again:

Now I'm thinking it's easier to convert a slmod stats file into table and call it a day. That wouldn't require anything on behalf of the mission maker and the files are updated in real time, that could be updated to a php in real time too.. right? I'm so stupid when it comes to this stuff.

Prepare for a LOT of whining as long as the game itself doesn't accurately give out the kills/deaths.

I get awarded deaths out of nowhere, dying 0 times but still the server gives me SIX losses, sometimes , but more frequently the game suddenly decides to add a loss to every kill i make.

Sometimes i get awarded kills without having left my airfield. This goes for teamkills as well !

Yesterday i was awarded a teamkill, altough the guy that died was an enemy !

These are just the REALLY annoying things, i'm not even talking about the whole "crash" instead of "kill" thing.

AND youre gonna see a lot more bailing before dying , which in turn will generate a lot of hate.

Prepare for a LOT of whining as long as the game itself doesn't accurately give out the kills/deaths.

 

I get awarded deaths out of nowhere, dying 0 times but still the server gives me SIX losses, sometimes , but more frequently the game suddenly decides to add a loss to every kill i make.

 

Sometimes i get awarded kills without having left my airfield. This goes for teamkills as well !

Yesterday i was awarded a teamkill, altough the guy that died was an enemy !

 

These are just the REALLY annoying things, i'm not even talking about the whole "crash" instead of "kill" thing.

AND youre gonna see a lot more bailing before dying , which in turn will generate a lot of hate.

I got no idea where you're bringing your examples from, but we run a 40 player event on a regular basis and the statistics using my script are more or less accurate except for the issues mentioned earlier.

Can you show some examples?

I don't really care about exactly how accurate the stats are. One of the best things about this game is that it IS NOT stats driven.

I have the csv file being generated on my personal computer, albeit not correctly, but when I try it on the server it has a error at mission start: "error in error handling"

I still think it would be easier to convert the slmod data.. Just not easier for me lol

Prepare for a LOT of whining as long as the game itself doesn't accurately give out the kills/deaths.

 

I get awarded deaths out of nowhere, dying 0 times but still the server gives me SIX losses, sometimes , but more frequently the game suddenly decides to add a loss to every kill i make.

 

Sometimes i get awarded kills without having left my airfield. This goes for teamkills as well !

Yesterday i was awarded a teamkill, altough the guy that died was an enemy !

 

These are just the REALLY annoying things, i'm not even talking about the whole "crash" instead of "kill" thing.

AND youre gonna see a lot more bailing before dying , which in turn will generate a lot of hate.

That is just an in game issue, the log files though don't lie and creating stats from these is all fact. Third party stats systems have worked flawlessly in previous versions, thanks to the hardwork and dedication of those that created them, I don't recall one issue or complaint, the only vibe around these parts is one of thanks.

...

 

3...

a. Go into your mission, in the initialization script that start with the mission add the following - https://copy.com/0M27qJ8Fj7Co

b. In your mission, do a trigger that runs according to whenever you want to save stats and runs the following script - https://copy.com/xgdLUv0ZfFYK

...)

This part i`m not understand well:

a. .LUA file or CODE?

b. trigger WHENever: What is best and simple solution to run script with that trigger?

Apreciate any help.

Regards

This part i`m not understand well:

 

a. .LUA file or CODE?

b. trigger WHENever: What is best and simple solution to run script with that trigger?

 

Apreciate any help.

 

Regards

A. Lua file that you start with the Initialization script option in the mission editor.

B. For example, run it with switched condition every 5 minutes.

Worked. Thanks Xcom. :)

Is ED make some changes on EVENTS with last update?

Haven't noticed, why do you ask?