[RELEASE]LogFile Querier

01/10/2011 01:42 Zektor#1
Update (26/05/2012):
* Recoded the executable (The LogFile Querier executable) for efficiency (+ a new layout).
* No longer requires .NET Framework 4.0 (Needs at least .NET Framework 2.0)
* Checks the previous hour logs (only the .dats not .sav)
* Checks hourly instead of every five minutes.
_______________________

I couldn't think of a better name, so yeah all this does is the last hour's item_xxxxx logfile in your server and inserts it into your database. I've also provided some PHP pages to make it easier to see the logs.

[Warning the logs can reach hundreds of thousands if you have a server with over 30-40 people]

Requirements:
You NEED .NET Framework 4.0 in order to run the actual program.

First off, We'll ned to set up the database with the required tables, make a new database called gamelog

Once that's done, we need to create a table where the logs will be written too, just run this query:
Code:
USE [gamelog]
GO
/****** Object:  Table [dbo].[gamelog_main]    Script Date: 01/09/2011 19:17:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[gamelog_main](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[date] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[map] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[x] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[y] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[character_from] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemid] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[itemname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[serial] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[dill] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[character_to] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Once that's done, we'll need to set up the php pages - creating a new folder for these files are recommended!

Copy and paste these into a new php file:

clist.php
Code:
<?php
echo "<body bgcolor='#000000'><font color = white>";
function anti_injection($sql) {
   $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$sql);
   $sql = trim($sql);
   $sql = strip_tags($sql);
   $sql = addslashes($sql);
   return $sql;
}
function ip_address_to_number($IPaddress)
{
    if ($IPaddress == "") {
        return 0;
    } else {
        $ips = split ("\.", "$IPaddress");
        return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
    }
}
function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip=$_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip=$_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}
$mssql = array(
		'host' => "localhost",
		'user' => "sa",
		'pass' => "YOURPASSWORDHERE"
	);
$cont = false;
echo getRealIpAddr()."<br>";
$ipcrypt = ip_address_to_number(getRealIpAddr());
$con = mssql_connect($mssql['host'],$mssql['user'],$mssql['pass']);
		$resultgip = mssql_query("SELECT * FROM character.dbo.user_character where user_ip_addr = CAST (".$ipcrypt." AS bigint)",$con);
		while($listgip = mssql_fetch_array($resultgip))
		{
			if($listgip['character_name']=="[GM]Zirak")
			{
				$cont = true;
				echo "<font color = white>Welcome, ".$listgip['character_name']."</font><br><br>";
				break;
			}
			else
			{
				$cont = false;
			}
		}
if($cont== false)
{
	echo "Sorry you cannot use this function as you are not a GM!";
}
if($cont==true)
{

		$result1 = mssql_query("SELECT * FROM character.dbo.user_character ORDER BY character_name ASC",$con);
		echo "Change limit= in URL to what ever size you want (preset at 50)<br><br>";
		while($list = mssql_fetch_array($result1))
		{
			echo "<a href=getCharLogs.php?limit=50&uid=".$list['character_no']." style='color: #00FF00'>".$list['character_name']."</a><br>";
		}
}
?>
getCharLogs.php
Code:
<?php
echo "<body bgcolor='#000000'><font color = white>";
echo "<a href='javascript:history.back()'><- Go Back</a><br>";
$uid = anti_injection($_GET['uid']);
$limit = anti_injection($_GET['limit']);
function anti_injection($sql) {
   $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$sql);
   $sql = trim($sql);
   $sql = strip_tags($sql);
   $sql = addslashes($sql);
   return $sql;
}
if($limit=='')
{
	$limit = 1000;
}
function ip_address_to_number($IPaddress)
{
    if ($IPaddress == "") {
        return 0;
    } else {
        $ips = split ("\.", "$IPaddress");
        return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
    }
}
function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip=$_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip=$_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}
$mssql = array(
		'host' => "localhost",
		'user' => "sa",
		'pass' => "YOURPASSWORDHERE"
	);
$cont = false;
echo getRealIpAddr()."<br>";
$ipcrypt = ip_address_to_number(getRealIpAddr());
$con = mssql_connect($mssql['host'],$mssql['user'],$mssql['pass']);
		$resultgip = mssql_query("SELECT * FROM character.dbo.user_character where user_ip_addr = CAST (".$ipcrypt." AS bigint)",$con);
		while($listgip = mssql_fetch_array($resultgip))
		{
			if($listgip['character_name']=="[GM]Zirak")
			{
				$cont = true;
				echo "<font color = white>Welcome, ".$listgip['character_name']."</font><br><br>";
				break;
			}
			else
			{
				$cont = false;
			}
		}
if($cont== false)
{
	echo "Sorry you cannot use this function as you are not a GM!";
}
else
{
		$con = mssql_connect($mssql['host'],$mssql['user'],$mssql['pass']);
		$counter = 0;
		$result1 = mssql_query("SELECT * FROM character.dbo.user_character WHERE character_no = '".$uid."'",$con);
		while($list = mssql_fetch_array($result1))
		{
			$counter = $counter + 1;
			$charactername = mssql_query("SELECT TOP ".$limit." * FROM gamelog.dbo.gamelog_main WHERE character_from = '".$list['character_name']."' OR character_to = '".$list['character_name']."' ORDER BY id DESC",$con);
				while($loglist = mssql_fetch_array($charactername))
				{
					echo "<font color = white>Date: ".$loglist['date']." (UTC-6:00)</font><br>";
					echo "<font color = white>Type: ".$loglist['type']."</font><br>";
					echo "<font color = white>Map: ".$loglist['map']."</font><br>";
					echo "<font color = white>X Position: ".$loglist['x']."</font><br>";
					echo "<font color = white>Y Position: ".$loglist['y']."</font><br>";
					echo "<font color = white>Character Name[From]: ".$loglist['character_from']."</font><br>";
					echo "<font color = white>Character Name[To]: ".$loglist['character_to']."</font><br>";
					echo "<font color = white>Item ID: ".$loglist['itemid']."</font><br>";
					echo "<font color = white>Serial#: ".$loglist['serial']."</font><br>";
					echo "<font color = white>Dill: ".$loglist['dill']."</font><br><br>";
				}
		}
		if($counter == 0)
		{
			echo "Character does not exist!<br>";
		}
}
?>
logchour.php
Code:
<?php
function ip_address_to_number($IPaddress)
{
    if ($IPaddress == "") {
        return 0;
    } else {
        $ips = split ("\.", "$IPaddress");
        return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
    }
}
function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip=$_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip=$_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}
$mssql = array(
		'host' => "localhost",
		'user' => "sa",
		'pass' => "YOURPASSWORDHERE"
	);
$cont = false;
echo getRealIpAddr()."<br>";
$ipcrypt = ip_address_to_number(getRealIpAddr());
$con = mssql_connect($mssql['host'],$mssql['user'],$mssql['pass']);
		$resultgip = mssql_query("SELECT * FROM character.dbo.user_character where user_ip_addr = CAST (".$ipcrypt." AS bigint)",$con);
		while($listgip = mssql_fetch_array($resultgip))
		{
			if($listgip['character_name']=="[GM]Zirak")
			{
				$cont = true;
				echo "<font color = white>Welcome, ".$listgip['character_name']."</font><br><br>";
				break;
			}
			else
			{
				$cont = false;
			}
		}
if($cont== false)
{
	echo "Sorry you cannot use this function as you are not a GM!";
}
if($cont==true)
{
function anti_injection($sql) 
{
   $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$sql);
   $sql = trim($sql);
   $sql = strip_tags($sql);
   $sql = addslashes($sql);
   return $sql;
}
$limit = anti_injection($_GET['limit']);
if($limit=='')
{
	$limit = 1000000;
}
echo "<font color=white>";
echo "<body bgcolor='#000000'>";
$getchour = date('H') - 1;
	$result1 = mssql_query("SELECT TOP ".$limit." * FROM gamelog.dbo.gamelog_main where character_from not like '' AND date like '%-%-% ".$getchour."%' ORDER BY id DESC",$con);
	while($list = mssql_fetch_array($result1))
	{
		echo "<font color = white>Date: ".$list['date']." (UTC-6:00)</font><br>";
		echo "<font color = white>Type: ".$list['type']."</font><br>";
		echo "<font color = white>Map: ".$list['map']."</font><br>";
		echo "<font color = white>X Position: ".$list['x']."</font><br>";
		echo "<font color = white>Y Position: ".$list['y']."</font><br>";
		echo "<font color = white>Character Name[From]: ".$list['character_from']."</font><br>";
		echo "<font color = white>Character Name[To]: ".$list['character_to']."</font><br>";
		echo "<font color = white>Item ID: ".$list['itemid']."</font><br>";
		echo "<font color = white>Serial#: ".$list['serial']."</font><br>";
		echo "<font color = white>Dill: ".$list['dill']."</font><br><br>";
		
	}

echo "<center>";
echo "</center>";
echo "</font>";
}
?>
logfile.php
Code:
<?php
function ip_address_to_number($IPaddress)
{
    if ($IPaddress == "") {
        return 0;
    } else {
        $ips = split ("\.", "$IPaddress");
        return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256);
    }
}
function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))   //check ip from share internet
    {
      $ip=$_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))   //to check ip is pass from proxy
    {
      $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
      $ip=$_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}
$mssql = array(
		'host' => "localhost",
		'user' => "sa",
		'pass' => "YOURPASSWORDHERE"
	);
$cont = false;
echo getRealIpAddr()."<br>";
$ipcrypt = ip_address_to_number(getRealIpAddr());
$con = mssql_connect($mssql['host'],$mssql['user'],$mssql['pass']);
		$resultgip = mssql_query("SELECT * FROM character.dbo.user_character where user_ip_addr = CAST (".$ipcrypt." AS bigint)",$con);
		while($listgip = mssql_fetch_array($resultgip))
		{
			if($listgip['character_name']=="[GM]Zirak")
			{
				$cont = true;
				echo "<font color = white>Welcome, ".$listgip['character_name']."</font><br><br>";
				break;
			}
			else
			{
				$cont = false;
			}
		}
if($cont== false)
{
	echo "Sorry you cannot use this function as you are not a GM!";
}
if($cont==true)
{
function anti_injection($sql) 
{
   $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"),"",$sql);
   $sql = trim($sql);
   $sql = strip_tags($sql);
   $sql = addslashes($sql);
   return $sql;
}
$limit = anti_injection($_GET['limit']);
if($limit=='')
{
	$limit = 1000;
}
echo "<font color=white>";
echo "<body bgcolor='#000000'>";
	$result1 = mssql_query("SELECT TOP ".$limit." * FROM gamelog.dbo.gamelog_main where character_from not like '' ORDER BY id DESC",$con);
	while($list = mssql_fetch_array($result1))
	{
		echo "<font color = white>Date: ".$list['date']." (UTC-6:00)</font><br>";
		echo "<font color = white>Type: ".$list['type']."</font><br>";
		echo "<font color = white>Map: ".$list['map']."</font><br>";
		echo "<font color = white>X Position: ".$list['x']."</font><br>";
		echo "<font color = white>Y Position: ".$list['y']."</font><br>";
		echo "<font color = white>Character Name[From]: ".$list['character_from']."</font><br>";
		echo "<font color = white>Character Name[To]: ".$list['character_to']."</font><br>";
		echo "<font color = white>Item ID: ".$list['itemid']."</font><br>";
		echo "<font color = white>Serial#: ".$list['serial']."</font><br>";
		echo "<font color = white>Dill: ".$list['dill']."</font><br><br>";
		
	}

echo "<center>";
echo "</center>";
echo "</font>";
}
?>
Adjust the YOURPASSWORDHERE to your password for your database, also
change the '[GM]Zirak' to your character name that will need to access those pages.

This check doesn't use an .htaccess because if you wanted to allow certain GM's to view it, you would need to adapt to their latest IP if it changes, that way you won't have to constantly update your .htaccess with the latest IP and just simply tell them to re log into their character if their IP changes.

Also, in order for this to work, we'll need to use a program that I coded in order to run this. This isn't absolutely bug free (e.g. if you re-open after it's done making logs in the same hour it wont do anything in the next hour - trying to fix this) other than that I haven't found any other bugs. It runs fine every hourly if you don't decide to close and re-open it in the same hour.

This program is harmless as all it does is connect to your MSSQL and insert rows using ODBC connection (Visual Studio 2010 doesn't support MSSQL 2000 queries directly so I decided to use ODBC for more support). It reads the log files, and checks both .dat and .sav extensions to ensure it grabs all the information it needs, and then inserts it into the table.

The whole point of this is to make things easier to see and you can easily execute queries and stuff especially for webpages (like the ones I have coded)

Download the LogFile Querier.zip from attachment

Once downloaded extract the two files into LogFile folder of your server

Once extracted, open db.info and adjust the settings according to your database.

Once done, simply run the program (only run it once so it doesn't screw up anything) And if you want logs to be created just leave the program running. At every hour it'll insert the table. [Checks every 5 minutes].

[Only registered and activated users can see links. Click Here To Register...]

[Only registered and activated users can see links. Click Here To Register...]

[Only registered and activated users can see links. Click Here To Register...]

Good luck guys, post any issues you have!

-Created by Me (Zirak/Zektor)
01/10/2011 02:00 Decima#2
damn, congratz, i prolly wont use it, but it rocks man +100

i hope to see more stuff from u man ;)
01/10/2011 02:09 Zektor#3
Thanks man I spent a few hours on the exe tryna get queries working, but managed to do it.

The database connection string was hardcoded before, so when I made it to use a File I didn't get to test it. But I wasn't willing to wait an hour to test it anyways, so I released it. If anyone gets any problems I'll try to fix it.
01/10/2011 10:21 6shadow6#4
Zektor it's rely nice work you have my THX
01/10/2011 12:16 tissot2#5
i can not understand that what for ?
01/10/2011 13:39 Cryptic.#6
You need an IQ over 100 to be able to understand this.
01/10/2011 14:08 pieter#7
Simple and yet effective, never bothered on making this. so thanks! :)

I'll run it on the backup DB server (replicated) since i asume this takes a bunch of load parsing on a live environment with 35-90 players online any time of the day
01/10/2011 15:45 Zektor#8
Quote:
Originally Posted by pieter View Post
Simple and yet effective, never bothered on making this. so thanks! :)

I'll run it on the backup DB server (replicated) since i asume this takes a bunch of load parsing on a live environment with 35-90 players online any time of the day
You'll need to edit the webpages a bit if you intend on using that.

Because if you're going to host the DB seperate from the game's MSSQL
Then you will need to establish two connections, because the webpages read the character database to obtain your latest IP (based on the last time you logged in game), I have this check rather than a formal IP check because of people having dynamic IP's that change constantly.

Edit:
Found a few bugs, gonna start fixing em, v1.1 coming soon.
Edit 2:
Bugs found, in V1.0 at 12:00AM the logfile program will stop working because it will try to find a file with a negative sign in it, even though its not supposed to. This is because I screwed up the checks and believed 00 is an invalid file name (even though it represents 12:00AM) so I made it pass on that check, but because of that, it turned the 00 and subtracted by one (to get the last hours logs) and returned -1, because of this it created an error reading the file. So I'm fixing this simple problem, and I'm also stopping the program from reading .sav files, as those are the files currently in use by the server and .dat are the ones that are stopped being used.

Also, I've added another check, if it fails to find the file five times (five minutes each wait) Then it will skip it and go to another one.
01/10/2011 16:48 pieter#9
the 2 servers are live replicating ;)

so no need for a 2nd connection :)

i use the backup-replication server to test new configs :)
01/10/2011 16:49 Zektor#10
Oh okay, also I've updated the download. Download the new one, should fix most bugs
(Including an indexing error one of my functions gave)
01/10/2011 22:55 ~LIGHTS~#11
Gj, +1.
01/11/2011 00:08 Zektor#12
Anyone actually tried this out yet? I wanna know your comments on how it works on your computer, and if there's any issues with it.
05/26/2012 06:54 Zektor#13
Updated LogFile Querier (The executable).

Should run smoothly and efficiently.
05/26/2012 07:41 BlackLixt#14
nice work Z

working 100%