My Spotify-Style Game Monitoring Project
This story begins in the summer of 2022. At that time, I was hosting several games on a home server to play with my friends: Minecraft, Starbound, Factorio... we played it all.
It was also during this period that I received my first Spotify Wrapped, that compilation of statistics that Spotify sends to each of its users every year.
I thought the idea was brilliant. There's something fun about seeing your habits summarized by numbers. And I asked myself: could I do the same for the servers I was hosting at home?
I already had plenty of ideas for statistics:
- Most played game: Starbound, with 13 hours
- Death count record: Kiki with 118 deaths
- The ghost player: Bob, with a total of 3 minutes on Minecraft
- Playtime over time: peak in August on Minecraft, then a drop in September
- ...
So, that's how I embarked on this project (this one's for you, stats fans).
The beginning of the problems
My idea was to rely on logs—those text files that servers fill with various information during execution. Admittedly, it's not , but it is the most standard: to my knowledge, all game servers have a log file. This would make it convenient in the future to add other games.
First step: I centralized all these log files in one place to start observing them. And very quickly, I noticed that these files had completely different formatting. For example, when a player connects, Minecraft writes something like this in its log file:
[15:30:45] [Server thread/INFO]: PlayerName[/192.168.1.15:54321] logged in with entity id 150 at (10.5, 64.0, -20.2)
Whereas Starbound uses another format:
[14:20:05.789] [Info] UniverseServer: Client 'CharacterName' (PLAYER_IP) connected
And Factorio does something else entirely.
I needed a system to centralize everything under a common format, with the goal of being able to create cross-game statistics.
That's where my main-log.csv file comes in.
The idea is simple: monitor the log files with a script in mode. Each script (e.g., factorio-watcher) observes every new line added to the log and passes it through patterns. Each regex triggers if the line matches an important event (login, logout, death, achievement, etc.).
It then extracts the important info, such as the player's name, and stores it in the famous main-log.csv, which is common to all games.
Every piece of data is normalized there (the date, for example) so it can be compared across games (see diagram).
It's very much improvable, but it works quite well!
A 100% DIY solution
Happy with this first solution, I moved on to the next step. I racked my brain to figure out how to exploit this data. In the end, I chose the simplest option: a Google Sheets file (= Excel).
To keep it simple, I expose the main-log.csv file on a private URL from my server,
then I import it into Google Sheets using the function IMPORTDATA("mysuperserver.com/main-log.csv?token=1234don'tpanicthistokenisfake").
This allows the Google Sheets file to update in real-time as the watchers write to the main-log.csv file.
Thanks to all the features of Google Sheets, I can easily transform this data.
For example, I can measure the time between the login and logout events for each player to track playtime.
With a simple COUNTIF, I can count the number of deaths for each player.
Better yet, I can create some pretty cool charts:
- Evolution of the death count over the year (are we getting better or not?)
- Most played day of the week (surprisingly, Monday night)
- Players who play together the most (= at the same time)
- ...
And the cool part is that Google Sheets allows access to these charts as images via public URLs, which means I can display them anywhere. As a result, in addition to the annual email I planned to send manually (remember, a bit like a Spotify Wrapped), I added a Discord bot that displays these charts on demand.
It's already the end
In the end, this little project of a few days was quite a hit! People even asked for extra info, like the player with the record for cheating (/gamemode creative 👀). As it stands, everything works quite well, even though I see plenty of points to improve.
First of all, relying on watcher scripts is practical for real-time updates, but if they crash, we could lose precious information before they restart.
Secondly, the main difficulty in centralizing all this info across games is the different player nicknames. Indeed, it's impossible to automatically know that Anima28 on Minecraft and aniima on Starbound are actually the same player. This complicates the idea of cross-game stats a bit, but I bypassed the problem by creating a manual nickname mapping table.
Ultimately, I see this project as a sort of : I don't expect to improve it anytime soon, but it was very fun to make.
Don't hesitate to leave a comment if you have suggestions or questions!