code

Centovacast – Getting Listener Statistics via MySQL

We ran into a little issue with our Centovacast installation last month. It turns out, that if you have a few large radio stations using the same server, the MySQL Database tables get rather full (20 million rows), and when trying to pull the data back into the Centovacast interface was causing some issues (timeout and 500 errors etc). Which ultimately meant our customers could not retrieve the statistics they needed.

So, the only solution was to manually query the tables, and generate our own statistics to provide to our customers. I wrote a little PHP/HTML interface for this, however you can easily do this via an MySQL Client.

Here’s what the customers were requesting, and the SQL queries to get them!

SQL Time!

Replace the accountid=269 in the queries below with the account you wish to get the data for.

You can get the accountid from the acounts table:

SELECT * FROM centovacast.accounts;

# Total sessions

SELECT COUNT(*) AS sessions FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Total listener seconds

SELECT SUM(duration) as duration FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Average session length (seconds)

SELECT AVG(duration) as seconds FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Total data transfer (KB)

SELECT SUM(bandwidth) as bandwidth FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Average transfer (KB)

SELECT AVG(bandwidth) as bandwidth FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Unique Listeners

SELECT COUNT(DISTINCT ipaddress) FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# Unique Countries

SELECT COUNT(DISTINCT country) FROM visitorstats_sessions WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269;

# ASCAP music sessions

SELECT COUNT(*) FROM centovacast.playbackstats_tracks WHERE starttime > '2017-12-25' AND starttime < '2017-12-31' AND accountid=269 GROUP BY DAY(starttime), HOUR(starttime), name;

 

With most of my blog posts, I just write this stuff down for future reference. However I hope it’s helped at least someone in the same situation!

Basic Steam RCON Example (Rust)

After spending time over Christmas coding a tool to query Steam servers for information. I’ve now been taking the next steps… Sending data to a Steam server!

For this example, I’m going to be using a Rust Dedicated Server, to send a simple command, then in future posts show how I sent scheduled commands (such as adverts, messages and other routine tasks which you would expect a Rust server to run).

First things first, after learning the hard way; Don’t run your Rust server with rcon.web = 1. It’s a new protocol, and I found it almost impossible to get working. Maybe if I had more time and patience, I could have cracked it. However, using the old protocol (which I assume is steam’s rcon protocol) is much easier.

 

Here’s the startup command I used when starting the Rust server (notice “rcon.web 0”):

./RustDedicated -batchmode +server.ip 127.0.0.1 +server.port 28015 +server.tickrate 30 +server.hostname "Server Name" +server.identity "rust-server"  +server.maxplayers 250 +server.worldsize 3000 +server.saveinterval 300 +rcon.web 0 +rcon.ip 127.0.0.1 +rcon.port 28016 +rcon.password "Password" -logfile "gamelog-2017-01-22-22-47-05.log"

Code time!

Again I’m using xPaw’s SourceQuery. Here’s a simple example of how to send a command via RCON:

<?php 
require __DIR__ . '/SourceQuery/bootstrap.php'; 
use xPaw\SourceQuery\SourceQuery; 

define( 'SQ_TIMEOUT', 1 ); 
define( 'SQ_ENGINE', SourceQuery::SOURCE ); 

// Init SourceQuery 
$Query = new SourceQuery( ); 

// Connect to the server 
$Query->Connect("<IP>", "<PORT>", SQ_TIMEOUT, SQ_ENGINE);

// Auth
$Query->SetRconPassword( "<Password>" );

// Run the "status" command and return
var_dump($Query->Rcon( "status" ));

// Disconnect
$Query->Disconnect( );

Result!

Which outputs the following:

[email protected]:/var/www/zurk/scripts/rcon# php test.php
string(269) "hostname: Server Name
version : 1955 secure (secure mode enabled, connected to Steam3)
map     : Procedural Map
players : 0 (250 max) (0 queued) (0 joining)

id name ping connected addr owner violation kicks
"

In the next post, I’ll be writing about how I took this simple example, and made a scheduled messaging system. Until then!

Querying Source Servers with PHP : Part 2

So I have my JSON data which contains all the query information which was returned from the Source Query Protocol. I’m storing it as a flat JSON file – the reason being (as discussed in my last post) is simply due to speed (or the assumption it would be faster). I don’t want to be getting into caching database queries, optimising the indexes, normalisation etc… As this project is for fun, let’s just store the JSON on the file system, and pull the data out when we need it.

What should I do with this data?

Well, that’s easy! Let’s create a website, and display it! I admit, I’m no front-end developer. So I chose to use a simple PHP Framework I wrote about 8 years ago. It’s a very simple MVC Framework, which consists of 10 files.

Without going into too much detail (maybe this is a topic for another blog post), here’s the file structure:

/config - Configuration Files

/content - Views

/data - Storage (JSON in this example)

/includes - Controllers

/models - Models

Very simple, but why spend time setting up and configuring something like Laravel or CodeIgnitor, when I can copy/paste my old code, and get cracking? I wrote a controller to loop through my list of servers, pull the data from each of the JSON files, then pass it to the view to be displayed.

CSS & HTML Framework

I decided to use Good old faithful Bootstrap (version 3), and customised some of the basic elements. Again, nothing special, just a quick and easy setup, and throwing together code in minutes!

No npm, grunt, composer, gulp or any other technology! Just raw PHP, CSS and HTML.

Here’s the finished design.

As you can see, I’m displaying information such as the Name, IP, Players and giving the user options to connect (or purchase their own, wink, wink). It’s just a simple “col-md-6” <div>. Nothing fancy, but it means when I add more servers, they can just drop in, and look apart of the page. There’s a few situations, where the divs don’t line up – mainly when there are many players online. However I could resolve this by adding an ellipsis to the players text – one for another time!

What about the Graph?

Ok I added the data used in the graph after my previous post. I simply added integer variable to the foreach loop, which I collate the number of players on each server together. I’m then throwing this data into another flat file with a timestamp.

<datetime>,<number of players>
<datetime>,<number of players>

I then pull this data back in the front end via the controller, and use ChartJS to display it.

<script>
 var ctx = document.getElementById("myChart");
 var data = {
 labels: ["04:20","04:25","04:30","04:35","04:40","04:45","04:50","04:55","05:00","05:05","05:10","05:15","05:20","05:25","05:30","05:35","05:40","05:45","05:50","05:55","06:00","06:05","06:10","06:15","06:20","06:25","06:30","06:35","06:40","06:45","06:50","06:55","07:00","07:05","07:10","07:15","07:20","07:25","07:30","07:35","07:40","07:45","07:50","07:55","08:00","08:05","08:10","08:15","08:20","08:25","08:30","08:35","08:40","08:45","08:50","08:55","09:00","09:05","09:10","09:15",],
 datasets: [
 {
 label: "Players",
 fill: true,
 lineTension: 0,
 backgroundColor: "rgba(187,255,0, 0.09)",
 borderColor: "rgba(187,255,0,1)",
 borderCapStyle: 'butt',
 borderDash: [],
 borderDashOffset: 0.0,
 borderJoinStyle: 'miter',
 pointBorderColor: "rgba(187,255,0,1)",
 pointBackgroundColor: "#fff",
 pointBorderWidth: 0,
 pointHoverRadius: 5,
 pointHoverBackgroundColor: "rgba(187,255,0,1)",
 pointHoverBorderColor: "rgba(187,255,0,1)",
 pointHoverBorderWidth: 2,
 pointRadius: 0,
 pointHitRadius: 10,
 data: [2,2,2,2,2,2,2,2,2,2,4,4,4,4,4,4,4,5,5,8,4,4,4,4,4,4,4,4,5,6,6,6,6,6,6,6,6,4,4,5,5,5,5,5,4,4,5,6,5,4,5,6,7,8,8,8,9,8,8,8],
 spanGaps: false,
 }
 ]
 };
 var myChart = new Chart(ctx, {
 type: 'line',
 data: data,
 options: {
 legend: {
 display: false
 },
 responsive : true,
 scaleShowLabels : false,
 scales: {
 yAxes: [{
 display: true,
 ticks: {
 stepSize: 1,
 beginAtZero: true
 }
 }],
 xAxes: [{
 display: false
 }]
 }
 }
 });
</script> 

As the cron is running every 5 minutes, the graph is showing the number of players, every 5 minutes. Really simple, but a nice addition to the page.

I’d imagine, when I add more servers, I could add options to the graph for different time periods (hour, day, month). This will allow me to see the peak times players are online.

Around this page, I’ve started to build up other pages, and more elements I can use. I’ll leave this blog post here, and maybe continue in another.

It’s been a very fun experience so far, and even though I’m not using any bleeding edge/funky languages or technologies. It’s been nice to refresh my knowledge of Bootstrap, CSS, jQuery, and also learn how Source Query Protocol works!

Here’s where I’ve left the website so far:

Hopefully as time allows, I’ll begin to build it up, and add more pages and servers!

Querying Source Servers with PHP : Part 1

Over the Christmas Period, I decided to develop a website to monitor the multitude of gaming servers that I host. As a game server provider, I’ve often wondered how many players are actively playing across the network.

For this, I decided to use a simple LEMP (Linux, NGINX, MySQL, PHP) environment. Furthermore, I wanted this website to be heavily reliant on JSON data, and would try to avoid pulling data from MySQL where possible – as I’m assuming pulling from a JSON file (using caching), is faster than from MySQL.

The first task was to obtain a list of the game servers on our network. This was quite easy, due to the fact our billing system already contains this information.

I built a JSON string containing an array of the servers:

Example:

{"1":{"id": "1", "protocol":"steam", "game": "Rust","ip":"46.101.5.78", "port": "28015"},"2":{"id": "2", "protocol":"steam", "game": "Rust","ip":"178.62.55.51", "port": "28015"}}

Lovely and simple!

ID: Unique Primary Key
Protocol: Steam
Game: The game!
IP: Servers IP
Port: Servers Port

 

Now, I have my list of servers. Next it to query them for the data! To do this, we need to interrogate the Source Query Protocol. There’s many open source examples of this out there. I found one by xPaw at https://github.com/xPaw/PHP-Source-Query – It’s very well coded class, and rather than reinventing the wheel, I choose to use his class.

So, I create a cron, to query the server list on a predefined interval.

Here’s the code.

It’s a proof of concept, so don’t judge the coding standards!

<?php

require __DIR__ . '/SourceQuery/bootstrap.php';
use xPaw\SourceQuery\SourceQuery;

define('SQ_TIMEOUT', 1);
define('SQ_ENGINE', SourceQuery::SOURCE);

$servers = json_decode(file_get_contents('servers.json'));

foreach ($servers as $server) {
 switch ($server->protocol) {
 case 'steam':
 $data = fetchSteam($server->id, $server->ip, $server->port, $server->game);
 break;
 }
}

function fetchSteam($id, $ip, $port, $game)
{

 $Query = new SourceQuery();

 try {
 $Query->Connect($ip, $port, SQ_TIMEOUT, SQ_ENGINE);

 $data["info"] = $Query->GetInfo();
 $data["info"]["Game"] = $game;
 $data["info"]["IP"] = $ip;
 $data["info"]["Port"] = $port;
 $data["players"] = $Query->GetPlayers();
 $data["rules"] = $Query->GetRules();

 file_put_contents('servers/' . $id . '.json', json_encode($data));
 } catch (Exception $e) {
 echo $e->getMessage();
 } finally {
 $Query->Disconnect();
 }
}

I’m looping through the list of servers, querying each one – then storing the returned data in a separate JSON file (1.json, 2.json etc).

Why the switch statement? Well, not every game server we host is powered by Steam, eventually I plan to add more protocols…

So, what data can we pull back from the Source Query Protocol?

Well, here’s the JSON decoded (pretty format thanks to http://jsonviewer.stack.hu/)

$Query->GetInfo()

$Query->GetPlayers()

$Query->GetRules()

As you can see, we get quite a fair amount of information back from the Source Query Protocol. The only thing I’m struggling to get is the Players SteamID. However, I have no requirement for it, so it’s not a pressing issue – If I wanted to display or track players in more depth across the network, I would need the SteamID.

I’ll leave this blog post here for now, and write Part 2 with the details of what I did next (The interface & graphing).

Shorthand If/Else (ternary) Statements – C#, C++, Python & PHP

I’ve been going through the multiple languages I know recently, and refreshing my knowledge. After working with Python for a few months, I found going back to C# very easy.

I’ve noticed many similarities except for a few; In my opinion the ternary operator slightly differs from each language. So I’d thought I’d write these findings down for future use.

C# & C++

variable = (input > 0) ? 'true' : 'false';

Python

variable = 'true' if input > 0 else 'false'

PHP

$variable = ($input > 0) ? 'true' : 'false';

Knapsack Problem (0-1 solution) – Dynamic Algorithm

I’ve recently dug up old code from my University days, which I thought I’d share for the benefit/misfortune of others.

There’s a common problem in programming called the knapsack problem. Here was my solution based on the dynamic algorithm.

# Math is used to floor/round the floats to an interger and back!
import math
# Total allowed weight
totalWeight = 2392;

# Define the items name, Weight, Profit
items = (("Weapon and Ammunition",    4.13, 1.4),
         ("Water", 2.13, 2.74),
         ("Pith Helmet", 3.03, 1.55),
         ("Sun Cream", 2.26, 0.82),
         ("Tent", 3.69, 2.38),
         ("Flare Gun", 3.45, 2.93),
         ("Olive Oil", 1.09, 1.77),
         ("Firewood", 2.89, 0.53),
         ("Kendal Mint Cake", 1.08, 2.77),
         ("Snake Repellant Spray", 3.23, 4.29),
         ("Bread", 2.29, 2.85),
         ("Pot Noodles", 0.55, 0.34),
         ("Software Engineering Textbook", 2.82, -0.45),
         ("Tinned Food", 2.31, 2.17),
         ("Pork Pie", 1.63, 1.62))

# Get the total of bagged items.
def totalvalue(comb):
    totwt = totval = 0
    for item, wt, val in comb:
        totwt  += wt
        totval += val
    return (totval, -totwt) if totwt <= totalWeight else (0, 0)

# The knapsack 0-1 Dynamic Programming Algorithm.
def knapsack(items, limit):
    table = [[0 for w in range(limit + 1)] for j in xrange(len(items) + 1)]

    for j in xrange(1, len(items) + 1):
        item, wt, val = items[j-1]
        wt = int(wt * 100)
        val = int(val * 100)
        for w in xrange(1, limit + 1):
            if wt > w:
                table[j][w] = table[j-1][w]
            else:
                table[j][w] = max(table[j-1][w],
                                  table[j-1][w-wt] + val)

    result = []
    w = limit
    for j in range(len(items), 0, -1):
        was_added = table[j][w] != table[j-1][w]

        if was_added:
            item, wt, val = items[j-1]
            wt = int(wt * 100)
            val = int(val * 100)
            result.append(items[j-1])
            w -= wt

    return result

# Bag the items
bagged = knapsack(items, totalWeight)

# Print the results
print("Bagged the following items\n  " +
      '\n  '.join(sorted(item for item,_,_ in bagged)))
val, wt = totalvalue(bagged)

print("Total Value: %f - Total Weight: %f" % (float(val), float(-wt)))

 

Knapsack Problem (0-1 solution) – Greedy Algorithm

I’ve recently dug up old code from my University days, which I thought I’d share for the benefit/misfortune of others.

There’s a common problem in programming called the knapsack problem. Here was my solution based on the greedy algorithm.

# Name, Weight and Value of Items
items = [("Weapon and Ammunition",    4.13, 1.4),
         ("Water", 2.13, 2.74),
         ("Pith Helmet", 3.03, 1.55),
         ("Sun Cream", 2.26, 0.82),
         ("Tent", 3.69, 2.38),
         ("Flare Gun", 3.45, 2.93),
         ("Olive Oil", 1.09, 1.77),
         ("Firewood", 2.89, 0.53),
         ("Kendal Mint Cake", 1.08, 2.77),
         ("Snake Repellant Spray", 3.23, 4.29),
         ("Bread", 2.29, 2.85),
         ("Pot Noodles", 0.55, 0.34),
         ("Software Engineering Textbook", 2.82, -0.45),
         ("Tinned Food", 2.31, 2.17),
         ("Pork Pie", 1.63, 1.62)]

# Maximum Weight Allowed.
MAXIMUM = 15.0

# Sort items based on effeciency (value/weight).
sorted_items = sorted(((value/amount, amount, name)
                       for name, amount, value in items),
                      reverse = True)

# Define the total weight and value.
wt = val = 0
# Define an array for the items which are bagged.
bagged = []

# Loop through the item array and bag items until full
for unit_value, amount, name in sorted_items:
    weight = min(MAXIMUM - wt, amount)
    wt     += weight
    addval  = weight * unit_value
    val    += addval
    bagged += [(name, weight, addval)]
    if wt >= MAXIMUM:
        break

# Print results!
print("    ITEM   WEIGHT  VALUE")
print("\n".join("%10s %6.2f %6.2f" % item for item in bagged))
print("\nTotal Bagged Weight: %5.2f\nTotal Bagged Value: %5.2f" % (wt, val))