Since i no longer have time to continue development or perfect it. I've decided to just release what i've got sofar :)
What i was building?
A complete port to MSSQL to enable player management, shared banlists, community banlists, player profiles (webbased) with stats etc..
Sadly i ran out of free time to continue work so i've decided to release what ive got so far :)
So.. what is this?
This is a MSSQL logger for MW3 including some nifty example usages of it in meanings of ingame and out-of-game stats! :)
Requirements:
- uptodate addon
- MSSQL 2005 or higher with TCP/IP networking enabled
- AsciiName plugin installed and set to kick/tempban (no non-ascii names allowed)
DB-Structure
source
sv_config lines:
LOCALHOST,sa,YourSApassword need to be changed to correspond your mssql installation
ServerID must be raised and added to the servers table if you run multiple instances
The MSSQL Database backup (Empty with example servers added to the servers table) is added below.
DB-Dump:
MW3-BackupEmpty.zip (Size: 653.87 KB / Downloads: 209)
restore it as databasename: MW3 or you'll run into problems.
Ingame commands?
!stats - your personal stats
!thismap - your personal stats on the current map
!rank - Your rank
!top3 - The current top3 of the server
out of game?
you can use PHP + MSSQL to create stats :)
WTF why MSSQL make it MYSQL!!!???
This was my choice because i mainly use MSSQL for my other businesses :)
I will not be bringing out a MySQL version. but you're free to edit the code.
Where's the compiled version?
There isnt any :) and I will not add one. :)
Support?
I really dont have time anymore to hang around on the forums here. I'll do my best to keep an eye on this thread. But can't make any promisses
ss?
dont mind the other crap in screen lol
![[Image: 29FCFC207E9BF7206F96489B28CFE260B4FC9185]](http://cloud.steampowered.com/ugc/559821183773241677/29FCFC207E9BF7206F96489B28CFE260B4FC9185/)
![[Image: 860086438F23D82BB39DC6E12BCCEE8C94F53B6D]](http://cloud-2.steampowered.com/ugc/559821183774942922/860086438F23D82BB39DC6E12BCCEE8C94F53B6D/)
What i was building?
A complete port to MSSQL to enable player management, shared banlists, community banlists, player profiles (webbased) with stats etc..
Sadly i ran out of free time to continue work so i've decided to release what ive got so far :)
So.. what is this?
This is a MSSQL logger for MW3 including some nifty example usages of it in meanings of ingame and out-of-game stats! :)
Requirements:
- uptodate addon
- MSSQL 2005 or higher with TCP/IP networking enabled
- AsciiName plugin installed and set to kick/tempban (no non-ascii names allowed)
DB-Structure
source
Code:
/*
* Welcome to the MW3 MS SQL Logger
* Please be so kind to keep the credits and comments
* Do NOT re-distribute my code or modified versions of it
* You are free to use/modify it, just dont claim it as being your own software
*
* This plugin REQUIRES your server to only accept players with standard ASCII names
* For best results use the AsciiName kicker included in the release thread
*
* You also need to run MSSQL Server 2005 Express or later
* And you need to have the backup MW3 database i included restored to your server
*
* available commands ingame: !stats !rank !thismap !top3
*
* There will be some errors being logged if a player with a 'bad name' connects and gets kicked.
* But that isnt too serious :-)
*
* This plugin has been coded by JoSchaap on itsmods.com (Also known as JoSchaap elsewhere)
*
* Build/Release date: 22-10-2012
*
* */
using Addon;
using System;
using System.Data;
using System.Data.SqlClient;
// do not modify this area, it consists the functions used to connect to the database
public class MySqlConnection
{
public SqlConnection Connection;
public SqlDataReader DataReader;
public SqlCommand Command;
public string SQLConnString;
public void CloseConn()
{
if (Connection != null)
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
}
Connection.Dispose();
}
}
public SqlConnection CreateConn()
{
if (Connection == null) { Connection = new SqlConnection(); };
if (Connection.ConnectionString == string.Empty || Connection.ConnectionString == null)
{
try
{
Connection.ConnectionString = "Min Pool Size=5;Max Pool Size=20;Connect Timeout=4;" + this.SQLConnString + ";";
Connection.Open();
}
catch (Exception)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
Connection.ConnectionString = "Pooling=false;Connect Timeout=45;" + this.SQLConnString + ";";
Connection.Open();
}
return Connection;
}
if (Connection.State != ConnectionState.Open)
{
try
{
Connection.ConnectionString = "Min Pool Size=5;Max Pool Size=20;Connect Timeout=4;" + this.SQLConnString + ";";
Connection.Open();
}
catch (Exception)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
Connection.ConnectionString = "Pooling=false;Connect Timeout=45;" + this.SQLConnString + ";";
Connection.Open();
}
}
return Connection;
}
public MySqlConnection()
{
}
}
public class SQLMW3Stats : CPlugin
// the plugin itself (This is where the magic happens)
{
public int ServerID;
public int Counter = 0;
public string Serv_name = null;
public string LowMsg = "";
MySqlConnection MySql = new MySqlConnection();
public void OnAddonFrame()
{
Counter = Counter++;
if (Counter == 6660)
{
// update ranks 15 second (60 frames) after mapchange to avoid server hang on mapchange
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "EXEC MW3..UpdateRanks " + this.ServerID + ";";
MySql.Command.ExecuteNonQuery();
MySql.CloseConn();
}
}
public override void OnMapChange()
{
Counter = 6600; // dirty trick to make ranks update 15 seconds AFTER mapchange instead of instant
if (Serv_name != null)
{
MySql.CreateConn();
// update stored servername
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "UPDATE MW3.dbo.Servers SET LastMapChange = getdate() WHERE ServerID = "+this.ServerID+"";
MySql.Command.ExecuteNonQuery();
string NewServerName = GetDvar("sv_hostname");
// trololol remove colorcodes
NewServerName = NewServerName.Replace("^1", "");
NewServerName = NewServerName.Replace("^2", "");
NewServerName = NewServerName.Replace("^3", "");
NewServerName = NewServerName.Replace("^4", "");
NewServerName = NewServerName.Replace("^5", "");
NewServerName = NewServerName.Replace("^6", "");
NewServerName = NewServerName.Replace("^7", "");
NewServerName = NewServerName.Replace("^8", "");
NewServerName = NewServerName.Replace("^9", "");
NewServerName = NewServerName.Replace("^0", "");
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "UPDATE MW3.dbo.Servers SET ServerName = LEFT(LTRIM(RTRIM('" + NewServerName.ToUpper() + "')),22) WHERE ServerID = " + this.ServerID + ";";
MySql.Command.ExecuteNonQuery();
MySql.CloseConn();
}
}
public override void OnPreMapChange()
{
StatsPlayerScore();
}
public override void OnPlayerConnect(ServerClient Client)
{
StatsPlayerConnect(Client);
}
public override ChatType OnSay(string Message, ServerClient Client)
{
LowMsg = Message.ToLower();
if (LowMsg == "!stats")
{
// (private.tell) Personal stats, KDR and ranking on current map by pulling info from the database (MSSQL)
StatsMe(Client);
}
if (LowMsg == "!thismap")
{
// (private.tell) Personal stats, KDR and ranking on current map by pulling info from the database (MSSQL)
StatsMeMap(Client);
}
if (LowMsg == "!rank")
{
// (private.tell) Personal stats, KDR and ranking on current map by pulling info from the database (MSSQL)
GetPlayerRank(Client);
}
if (LowMsg == "!top3")
{
// (public.tell) Serverwide top3 players (based on kills) by pulling info from the database (MSSQL)
GetServerTop3(Client);
}
return ChatType.ChatContinue;
}
public void StatsPlayerConnect(object q)
{
if (GetClients() != null)
{
ServerClient ConnectClient = (ServerClient)q;
int SpelerIDConnected = 0;
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
// check if the XUID is allready known
MySql.Command.CommandText = "SELECT PlayerID FROM MW3.dbo.XUID WHERE XUID = LTRIM(RTRIM('" + ConnectClient.XUID + "'))";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() == false)
{
// if nog log the XUID now
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandType = CommandType.Text;
MySql.Command.CommandText = "INSERT IGNORE INTO MW3.dbo.XUID (XUID,FirstSeen,LastSeen) Values (LTRIM(RTRIM('" + ConnectClient.XUID + "')),getdate(),getdate())";
MySql.Command.ExecuteNonQuery();
}
MySql.DataReader.Close();
MySql.Command.Parameters.Clear();
MySql.Command.Dispose();
MySql.CloseConn();
string VerbondenSpeler;
VerbondenSpeler = "(none)";
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "SELECT PlayerID FROM MW3.dbo.XUID WHERE XUID = LTRIM(RTRIM('" + ConnectClient.XUID + "'))";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
SpelerIDConnected = MySql.DataReader.GetInt32(0);
MySql.Command = MySql.Connection.CreateCommand();
VerbondenSpeler = ConnectClient.Name;
// sanitize player name
VerbondenSpeler = VerbondenSpeler.Replace("'", "''");
VerbondenSpeler = VerbondenSpeler.Replace("[", "[[]");
VerbondenSpeler = VerbondenSpeler.Replace("%", "[%]");
VerbondenSpeler = VerbondenSpeler.Replace("_", "[_]");
VerbondenSpeler = VerbondenSpeler.Replace("*", "");
VerbondenSpeler = VerbondenSpeler.Replace(",", "");
VerbondenSpeler = VerbondenSpeler.Replace(";", "");
MySql.Command.CommandType = CommandType.Text;
// check if name has been logged yet, if not, add it
MySql.Command.CommandText = "IF NOT EXISTS(SELECT PlayerName FROM MW3.dbo.NAMES WHERE PlayerID=" + SpelerIDConnected + " and PlayerName=LTRIM(RTRIM('" + VerbondenSpeler + "'))) BEGIN; INSERT IGNORE INTO MW3.dbo.NAMES (PlayerID,PlayerName,LastSeen) VALUES (" + SpelerIDConnected + ",LTRIM(RTRIM('" + VerbondenSpeler + "')),getdate()); END; ELSE BEGIN; UPDATE MW3.dbo.NAMES SET LastSeen=getdate() WHERE PlayerID=" + SpelerIDConnected + " and PlayerName=LTRIM(RTRIM('" + VerbondenSpeler + "')); END";
MySql.Command.ExecuteNonQuery();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandType = CommandType.Text;
// check if ip has been logged yet, if not, add it
MySql.Command.CommandText = "IF NOT EXISTS(SELECT IP FROM MW3.dbo.IP WHERE PlayerID=" + SpelerIDConnected + " and IP=LTRIM(RTRIM('" + ConnectClient.IP.ToString() + "'))) BEGIN; INSERT IGNORE INTO MW3.dbo.IP (PlayerID,IP,date) VALUES (" + SpelerIDConnected + ",LTRIM(RTRIM('" + ConnectClient.IP.ToString() + "')),getdate()); END; ELSE BEGIN; UPDATE MW3.dbo.IP SET date=getdate() WHERE PlayerID=" + SpelerIDConnected + " and IP=LTRIM(RTRIM('" + ConnectClient.IP.ToString() + "')); END";
MySql.Command.ExecuteNonQuery();
VerbondenSpeler = "(none)";
}
MySql.DataReader.Close();
MySql.Command.Dispose();
MySql.CloseConn();
}
else { ServerPrint("[MW3SQL] OnPlayerConnect: GetClients was null"); }
}
public void StatsPlayerScore() // log player stats and scores
{
if (GetClients() != null) // addon failfix :')
{
string sv_mapname = GetDvar("mapname");
string sv_teamname = "NoTeam";
int SpelerID = 0;
foreach (ServerClient Client in GetClients())
{
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "SELECT PlayerID FROM MW3.dbo.XUID WHERE XUID = LTRIM(RTRIM('" + Client.XUID + "'))";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
SpelerID = MySql.DataReader.GetInt32(0);
if (Client.Team == Teams.Allies) { sv_teamname = "Allies"; }
if (Client.Team == Teams.Axis) { sv_teamname = "Axis"; }
if (Client.Team == Teams.FFA) { sv_teamname = "FFA"; }
if (Client.Team == Teams.Spectator) { sv_teamname = "Spectator"; }
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandType = CommandType.Text;
MySql.Command.CommandText = "INSERT IGNORE INTO MW3.dbo.SCORE ([PlayerID],[Score],[Kills],[Deaths],[Assists],[Date],[Team],[Map],[ServerID]) VALUES (" + SpelerID + "," + Client.Stats.Score + "," + Client.Stats.Kills + "," + Client.Stats.Deaths + "," + Client.Stats.Assists + ",getdate(),LTRIM(RTRIM('" + sv_teamname + "')),LTRIM(RTRIM('" + sv_mapname + "'))," + this.ServerID + ");";
MySql.Command.ExecuteNonQuery();
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
} else { ServerPrint("[MW3SQL] GetClients was null (no players, or the server was just started)"); }
}
public void StatsMe(object p) // get player stats for current server
{
if (GetClients() != null)
{
ServerClient StatsmeClient = (ServerClient)p;
string SMxuid = StatsmeClient.XUID;
string SMrounds = "0";
string SMkills = "0";
string SMDeaths = "0";
string SMassists = "0";
string SMKDR = "0";
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "select cast(COUNT(PlayerID) as varchar), cast(SUM(kills) as varchar), cast(SUM(deaths) as varchar), cast(SUM(Assists) as varchar), cast(ROUND(CAST(SUM(Kills) as float) / CAST(replace(SUM(Deaths),0,1) as float),1,3) as varchar), COUNT(PlayerID) from mw3..score where playerid = (select PlayerID from XUID where XUID = '" + SMxuid + "') and serverid = " + this.ServerID + "";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
SMrounds = MySql.DataReader.GetString(0);
SMkills = MySql.DataReader.GetString(1);
SMDeaths = MySql.DataReader.GetString(2);
SMassists = MySql.DataReader.GetString(3);
SMKDR = MySql.DataReader.GetString(4);
iPrintLn("[^1STATS^7]^1Rounds played(^2" + SMrounds + "^1) Kills(^2" + SMkills + "^1) Deaths(^2" + SMDeaths + "^1) Assists(^2" + SMassists + "^1) K/D-Ratio(^2" + SMKDR + "^1) ", StatsmeClient);
ServerSay("^1!STATS ^2" + StatsmeClient.Name + " ^1played ^2" + SMrounds + " Rounds,^1 made ^2" + SMkills + " Kills ^1 and has a ^2K/D-Rate ^1of: ^2" + SMKDR + "^1!", true);
}
else
{
TellClient(StatsmeClient.ClientNum, "[^1STATS-ME^7] ^1Sorry! ^2No data!^1 Play 1 map and try again!", true);
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
else { ServerPrint("[MW3SQL] StatsMe: GetClients was null"); }
}
public void StatsMeMap(object p) // get player stats for the current map and server being played
{
if (GetClients() != null)
{
ServerClient StatsmeMapClient = (ServerClient)p;
string thismap = GetDvar("mapname");
string SMMxuid = StatsmeMapClient.XUID;
string SMMrounds = "0";
string SMMkills = "0";
string SMMDeaths = "0";
string SMMassists = "0";
string SMMKDR = "0";
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "select cast(COUNT(PlayerID) as varchar), cast(SUM(kills) as varchar), cast(SUM(deaths) as varchar), cast(SUM(Assists) as varchar), cast(ROUND(CAST(SUM(Kills) as float) / CAST(replace(SUM(Deaths),0,1) as float),1,3) as varchar) from mw3..score where playerid = (select PlayerID from XUID where XUID = '" + SMMxuid + "') and serverid = " + this.ServerID + " AND Map = LTRIM(RTRIM('" + thismap + "'))";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
SMMrounds = MySql.DataReader.GetString(0);
SMMkills = MySql.DataReader.GetString(1);
SMMDeaths = MySql.DataReader.GetString(2);
SMMassists = MySql.DataReader.GetString(3);
SMMKDR = MySql.DataReader.GetString(4);
iPrintLn("[^1STATS^7]^1Rounds played on ^6" + thismap + "^1: (^2" + SMMrounds + "^1) Kills(^2" + SMMkills + "^1) Deaths(^2" + SMMDeaths + "^1) Assists(^2" + SMMassists + "^1) K/D-Ratio(^2" + SMMKDR + "^1) ", StatsmeMapClient);
ServerSay("^1!STATS ^2" + StatsmeMapClient.Name + " ^1played ^2" + SMMrounds + " Rounds on ^3" + thismap + " ^1and made ^2" + SMMkills + " Kills ^1 and has a ^2K/D-Rate ^1of: ^2 " + SMMKDR + " ^1on ^3" + thismap + "^1!", true);
}
else
{
TellClient(StatsmeMapClient.ClientNum, "[^1STATS-ME^7] ^1Sorry! ^2No data!^1 Play 1 map and try again!", true);
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
else { ServerPrint("[MW3SQL] StatsMe: GetClients was null :troll:"); }
}
public void GetPlayerRank(object p) // get the rank for current player on current server and spam it
{
if (GetClients() != null)
{
ServerClient PRankClient = (ServerClient)p;
string PRxuid = PRankClient.XUID;
string PRrank = "0";
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "select cast(rank as varchar) from mw3..rank where playerid=(select playerid from mw3..xuid where xuid='"+PRankClient.XUID+"') and serverid = "+this.ServerID+" ";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
PRrank = MySql.DataReader.GetString(0);
iPrintLn("[^1STATS^7]^1 Your rank on this server is: (^2" + PRrank + "^1) !!", PRankClient);
ServerSay("^1!STATS ^2" + PRankClient.Name + " ^1Currently is ranked number ^2" + PRrank + " ^1 on this server!!", true);
}
else
{
TellClient(PRankClient.ClientNum, "[^1STATS-ME^7] ^1Sorry! ^2No data!^1 Play 1 map and try again!", true);
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
else { ServerPrint("[MW3SQL] StatsMe: GetClients was null :troll:"); }
}
public void GetServerTop3(object p) // get the top 3 for current server and spam it
{
if (GetClients() != null)
{
ServerClient TopClient = (ServerClient)p;
string top3sting = TopClient.XUID;
string top3string = "<nodata>";
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "select top 3 cast(rank as varchar), (select top 1 REPLACE(REPLACE(REPLACE(PlayerName,'[[]','['),'[_]','_'),'''''','''') from MW3..Names where PlayerID=mw3..rank.PlayerId order by lastseen desc) from mw3..rank where serverid = " + this.ServerID + " order by rank asc ";
MySql.DataReader = MySql.Command.ExecuteReader();
while (MySql.DataReader.Read() != false)
{
top3string = "Rank: (^1"+MySql.DataReader.GetString(0)+"^2) - ^7"+MySql.DataReader.GetString(1)+"^2.";
ServerSay("^1!STATS ^2"+top3string, true);
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
else { ServerPrint("[MW3SQL] StatsMe: GetClients was null :troll:"); }
}
public override void OnServerLoad()
{
string ServerName;
// read sv_config entrys
this.ServerID = int.Parse(GetServerCFG("SQLSTATS", "ServerID", "1"));
MySql.SQLConnString = GetServerCFG("SQLSTATS", "SQLConnStr", "0");
base.ServerPrint("--<(MW3 SQLSTATS v1.0 (by: JoSchaap) LOADED)>-- [ServerID = "+this.ServerID+"]");
// get servername from DB to verify the sv_config settings
MySql.CreateConn();
MySql.Command = MySql.Connection.CreateCommand();
MySql.Command.CommandText = "SELECT ServerName FROM MW3.dbo.Servers WHERE ServerID = " + this.ServerID +";";
MySql.DataReader = MySql.Command.ExecuteReader();
if (MySql.DataReader.Read() != false)
{
ServerName = (string)MySql.DataReader.GetValue(0);
Serv_name = ServerName;
ServerPrint("[SQLSTATS] (OnServerLoad) Servername found: (" + (string)ServerName + ")");
MySql.Command = MySql.Connection.CreateCommand();
// update last server start timestamp in database
MySql.Command.CommandText = "UPDATE MW3.dbo.Servers SET LastServerStart = getdate() WHERE ServerID = "+this.ServerID+";";
MySql.Command.ExecuteNonQuery();
}
else
{
ServerPrint("[SQLSTATS] (OnServerLoad) ERROR: Servername NOT found (make sure SV_CONFIG is properly configured!)");
}
MySql.Command.Dispose();
MySql.DataReader.Close();
MySql.CloseConn();
}
}sv_config lines:
Code:
[SQLSTATS]
ServerID=1
SQLConnStr=server=LOCALHOST;database=MW3;MultipleActiveResultSets=True;User ID=sa;Password=YourSApassword;Trusted_Connection=True;ServerID must be raised and added to the servers table if you run multiple instances
The MSSQL Database backup (Empty with example servers added to the servers table) is added below.
DB-Dump:
MW3-BackupEmpty.zip (Size: 653.87 KB / Downloads: 209)
restore it as databasename: MW3 or you'll run into problems.
Ingame commands?
!stats - your personal stats
!thismap - your personal stats on the current map
!rank - Your rank
!top3 - The current top3 of the server
out of game?
you can use PHP + MSSQL to create stats :)
WTF why MSSQL make it MYSQL!!!???
This was my choice because i mainly use MSSQL for my other businesses :)
I will not be bringing out a MySQL version. but you're free to edit the code.
Where's the compiled version?
There isnt any :) and I will not add one. :)
Support?
I really dont have time anymore to hang around on the forums here. I'll do my best to keep an eye on this thread. But can't make any promisses
ss?
dont mind the other crap in screen lol
Had a life, Got a modem..
![[Image: MaEIQ.png]](http://i.imgur.com/MaEIQ.png)
![[Image: lQDUjba.jpg]](http://i.imgur.com/lQDUjba.jpg)
![[Image: back_us_button.png]](http://static.squarespace.com/static/51cca4bae4b0bf9a1f4deb75/t/52ea6a9ee4b0f767960f8c35/1390493521990/back_us_button.png)
