[msleeper]

Very Basic Player Tracker

This plugin is a simple player tracker that logs player information into a MySQL database. It uses the "default" database from your databases.cfg file for storage. It logs the player name, SteamID, player's IP address, server game type (using GetGameFolderName()) and server IP address, and the GeoIP country code for the player's location. It logs this information X seconds after being connected, which is controlled with a cvar. This is useful if you only want to keep track of players who are connected for a minimum amount of time, the default is 90 seconds. If a duplicate player is found, it updates their information

I am not providing any web interface at this time, though I may in the future. This is really just a base for anyone who may want to develop it further, or use it for a more specific purpose. For those of you in need of a web interface, I highly recommend using the Sourcemod Webadmin which has built in support for this plugin!

Feel free to use this code for any other plugins, however please give credit back to me.

Installation
Installation is incredibly easy, install the plugin in your /addons/sourcemod/plugins/ directory and refresh your plugin list, or change map.

Configuration
A config file will automatically be generated in /cfg/sourcemod/ when the plugin is first ran.

sm_tracker_addtime - Add/update players in the database after this many seconds
sm_tracker_geoiptype - Add player's GeoIP country to the database. 0 = Disabled, 1 = 2 letter Country Code, 2 = 3 letter Country Code, 3 = full Country Name.

Use this SQL to create your table.

PHP Code:

CREATE TABLE `player_tracker` (
  `id` int(11) NOT NULL auto_increment,
  `steamid` varchar(255) NOT NULL,
  `playername` varchar(255) NOT NULL,
  `playerip` varchar(255) NOT NULL,
  `servertype` varchar(255) NOT NULL,
  `serverip` varchar(255) NOT NULL,
  `serverport` varchar(255) NOT NULL,
  `geoipcountry` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `steamid` (`steamid`),
  KEY `playername` (`playername`),
  KEY `playerip` (`playerip`),
  KEY `servertype` (`servertype`),
  KEY `serverip` (`serverip`),
  KEY `status` (`status`)
); 


[rotab]

Exactly what i was looking for. Thanks.

[msleeper]

That's why I posted it, I'm sure people out there would like to track player visits. Glad I could help.

[Rescue9]

I've been rewriting a bit of this. It is broken, but a work in progress.

The changed code is as such (from line 86 of above tracker.sp):

    new String:SteamID[32];
    GetClientAuthString(client, SteamID, sizeof(SteamID));

    new String:PlayerName[32];
    GetClientName(client, PlayerName, sizeof(PlayerName));
    
    new String:PlayerIP[32];
    GetClientIP(client, PlayerIP, sizeof(PlayerIP));

    new String:GameType[32];
    GetGameFolderName(GameType, sizeof(GameType));

    new String:ServerIP[32];
    cvar_ServerIP = FindConVar("ip");
    GetConVarString(cvar_ServerIP, ServerIP, sizeof(ServerIP));

    new String:query[512];
    Format(query, sizeof(query), "INSERT INTO player_tracker (steamid, playername, playerip, servertype, serverip, status) VALUES ('%s', '%s', '%s', '%s', '%s', 'new') ON DUPLICATE KEY UPDATE playerip='%s', status='seen', playername=IF( playername like '%%s%', playername, CONCAT_WS(', ', playername, '%s'));", SteamID, PlayerName, PlayerIP, GameType, ServerIP, PlayerIP, PlayerName, PlayerName);
    SendQuery(query);
}

This basically updates playername with PlayerName only if PlayerName isn't already in playername. My problem is this section:

playername like '%%s%',

Basically, mysql needs the two % to match the name. So, when it hits the sql server, if the players name were Bob, I'd need it to say:

playername like '%Bob%',

Any suggestions.

[Rescue9]

Think it might be better to use REGEXP statement instead of LIKE for the sql. THis way I can get away from having multiple % characters inside the code. I'll post findings later when I can test this out.

BTW... thanks for the nice plugin msleeper!

[Rescue9]

After dealing with this issue for days now, I've determined that REGEXP doesn't play well with unicode characters. Because of this the IF statement keeps writing the same name to playername, even though it's already listed, if it has unicode characters in it.

So... as it stands now, I've changed the code a bit more like the original code. It will only log the last playername the SteamID was seen under, but I'm working on using separate tables to take care of the Name issue. Just have to decide how I'm going to design the other tables, and what other information I'm going to put in there as well. Probably going to implement a Last Seen date at least, but who knows.

Also, if you'll notice above, I changed the SteamID string a bit. I'm not sure why, but on my server it was only inserting every 3rd or 4th person that joined into the DB. Since changing it to GetClientAuthString I'm now catching everyone. Don't know if this was a bug, or just something unnoticed previously.

Here is the new code that is actually working if plugged into the tracker.sp. Not much change from the original, but I thought it needed to be documented. Again... much thanks goes to msleeper. His plugin jumpstarted my thought process.

    new String:SteamID[32];
    GetClientAuthString(client, SteamID, sizeof(SteamID));

    new String:PlayerName[32];
    GetClientName(client, PlayerName, sizeof(PlayerName));
    
    new String:PlayerIP[32];
    GetClientIP(client, PlayerIP, sizeof(PlayerIP));

    new String:GameType[32];
    GetGameFolderName(GameType, sizeof(GameType));

    new String:ServerIP[32];
    cvar_ServerIP = FindConVar("ip");
    GetConVarString(cvar_ServerIP, ServerIP, sizeof(ServerIP));

    new String:query[512];
    Format(query, sizeof(query), "INSERT INTO player_tracker (steamid, playername, playerip, servertype, serverip, status) VALUES ('%s', '%s', '%s', '%s', '%s', 'new') ON DUPLICATE KEY UPDATE playerip='%s', status='seen', playername='%s';", SteamID, PlayerName, PlayerIP, GameType, ServerIP, PlayerIP, PlayerName);
    SendQuery(query);
}

[msleeper]

Originally Posted by Rescue9 Also, if you'll notice above, I changed the SteamID string a bit. I'm not sure why, but on my server it was only inserting every 3rd or 4th person that joined into the DB. Since changing it to GetClientAuthString I'm now catching everyone. Don't know if this was a bug, or just something unnoticed previously.

I will go ahead and update my plugin with this change. I'll admit that using the method I did was a little lazy and possibly sloppy.

As for the rest of your edits, they look good. I wish that SM/Source Pawn was quite a bit more SQL friendly, but I guess it wasn't really designed with being a database interface in mind.

[Rescue9]

Hmmmm.... just found another bug. Because Pawn uses single quotes but not double quotes for variables, player names with apostrophes in them are getting borked. I'm working on a few additional values right now and will eventually get to dealing with apostrophized player names. However, if you have the time, can you take a look at this as well please.

[msleeper]

I personally don't track player name, but I have ran into this bug before. I fixed this in L4D stats by simply stripping out quote marks. Here is the code you would need to use, feel free to add this to yours:

    new String:Name[MAX_LINE_WIDTH];
    GetClientName(client, Name, sizeof(Name));
    ReplaceString(Name, sizeof(Name), "\\", "");
    ReplaceString(Name, sizeof(Name), "\"", "");
    ReplaceString(Name, sizeof(Name), "'", "");
    ReplaceString(Name, sizeof(Name), ";", "");
    ReplaceString(Name, sizeof(Name), "´", "");
    ReplaceString(Name, sizeof(Name), "`", "");

You also might want to strip out the PHP open and close tags if you are using some sort of script to look at the player list.

[Rescue9]

Nice. Thanks for the fix. As it stands now, i've made the changes to the plugin, but have given up development for a while.

Our clan is not using a dedicated server, and after a week's worth of testing, I figured out that the writing of information to an external database source was creating a great deal of lag for the players. Every time someone would join, there would be about a 1-2 second period of lag spikes. This was simply unacceptable for gameplay.

As it stands now, I'm working on getting a dedicated host with mysql on the same box. it's just a matter of time.


Thanks again for the great work msleeper!