![[RELEASE]LogFile Querier](https://www.elitepvpers.com/forum/iconimages/dekaron-private-server/release-logfile-querier_ltr.gif){kind=small}
* 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
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>";
}
}
?>
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>";
}
}
?>
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>";
}
?>
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>";
}
?>
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].
Good luck guys, post any issues you have!
-Created by Me (Zirak/Zektor)
