[Release]Find all Chars connected to IP

02/06/2011 00:42 RebeccaBlack#1

I have another practically useless script that you guys might want =o

To begin with, run this:

Code:

USE [PS_GMTool]
GO
/****** Object:  StoredProcedure [dbo].[usp_Char_Find]    Script Date: 02/05/2011 17:50:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO




/*==================================================
@author	Tyler
@date	2011-02-05
==================================================*/
CREATE Proc [dbo].[usp_Char_Find]
@CharName		varchar(30)

AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
@UserIP varchar(30),
@UserUID int

SET @UserUID=(SELECT UserUID FROM PS_GameData.dbo.Chars WHERE CharName=@CharName AND DEL=0)
SET @UserIP=(SELECT UserIP FROM PS_UserData.dbo.Users_Master WHERE UserUID=@UserUID) 

SELECT U.[UserIP], U.[UserID], C.[CharName],C.[K1],C.[K2],
	CASE C.[Family]
		WHEN 0 THEN 'Light'
		WHEN 1 THEN 'Light'
		WHEN 2 THEN 'Fury'
		WHEN 3 THEN 'Fury'
	END AS [Faction]
FROM [PS_UserData].[dbo].[Users_Master] U
INNER JOIN [PS_GameData].[dbo].[Chars] C
ON C.[UserUID] = U.[UserUID]
WHERE UserIP=@UserIP
ORDER BY U.[UserUID]

Then use this to run it:

Code:

EXEC PS_GMTool.dbo.usp_Char_Find '' --CharName goes here

Any suggestions/Comments? =o

02/06/2011 02:32 castor4878#2

Aren't the CharName unique in your DB ??
if they are (as expected) the 1st request is useless

The 2nd request is a bad idea - UserIP is read a 2nd time in 3rd request and using a varchar as join key is ... not nice.

According uniqueness of name, the final sort is useless since you obtain a single record response.

so ...

SELECT C.[CharName],U.[UserID],U.[UserIP],
CASE C.[Family]
WHEN 0 THEN 'Light'
WHEN 1 THEN 'Light'
WHEN 2 THEN 'Fury'
WHEN 3 THEN 'Fury'
END Faction,C.[K1],C.[K2]
FROM [PS_GameData].[dbo].[Chars] C
INNER JOIN [PS_UserData].[dbo].[Users_Master] U ON U.[UserUID]=C.[UserUID]
WHERE C.CharName=@CharName

should be enough.

02/06/2011 08:15 abrasive#3

One possible flaw I see with this, is that it appears you are only checking the IP addresses of what the accounts were registered under.

It does not appear you are checking what IP address the users are currently logged in with.

The reason this is a big flaw, is that when registering an account, it is very easy to use a proxy to show up under a different IP address than you use to connect to the game with.

Theoretically one could register any number of accounts under different IP addresses using a simple web proxy. Then when you check for other accounts under the same IP address, you always find only one.

This is why it is important to know which IP address(es) the person logged into the game with, because it is MUCH harder to fake. The IP address that created the account is of little significance overall.

02/06/2011 08:52 RebeccaBlack#4

That's what I wanted to do Abrasive, but I couldn't figure out how to pull that data easily.

Usually it takes a bit for it to scan that table, I've thought about trying to get it to store that data in another table though, if possible. ;o *Not the whole actionlog, just actiontype for login)

And thank you Castor, I'll change it.

02/06/2011 15:44 castor4878#5

You didin't explain your goal Tnelis - except to say it's "practically useless".

Abrasive is right to highlight the fact that Users_Master.UserIP contains a single IP, the one used at registration (assuming the script does store it).
If your goal is to track all IP used by one toon you should scan the UserLoginLog table (assuming here again that you fill-in that table during login/logout).
Also, relying on IP can be a weak solution and it's better to identify an user with a MAC address (the unique address of his ethernet card), but that info can be harder to obtain.

02/06/2011 16:47 zargon05#6

The login IP is stored in the gamelog, you can get it from there. I would post a script but i completly destroyed my SQl with 3 partial 2k5's and 2 partial 2k8 installs. Gonna take sometime to fix it lol.

02/06/2011 17:54 castor4878#7

Quote:

Originally Posted by zargon05

The login IP is stored in the gamelog

or somewhere else ...

The sole valuable info is: in which table ps_login.exe does insert a record (if any) or which stored proc. does it invoke.
It's that entry point that must be checked, beyond that point it depends strongly on the configuration of the server and its databases.

Note that I'm not saying that server config is always unpredictable, I just remember that individual adjustments can be made.

02/06/2011 18:24 RebeccaBlack#8

I couldn't find another login IP other than gamelog. I wouldn't mind it pulling from gamelog, it just takes forever to query it.

I've been trying to get the login procedure to store it in the PS_UserData.dbo.UserLoginLog but not working so far. =/

02/06/2011 19:55 castor4878#9

well ... since "individual adjustments can be made" ... you have to check what happens.

let start with ps_login.exe, mine contains the following calls to stored procs:

Code:

ups_try_gamelogin7_r '%s','%s',I%64d,'%s'
ups_Try_GameLogin_Taiwan '%s','%s',I%64d,'%s'

the first proc (ups_try_gamelogin7_r) does *not* exist in my PS_UserData DB.
so I guess ps_login.exe call the second (directly or after failure of the 1st call).

ups_Try_GameLogin_Taiwan does exist and indeed has 4 mandatory parameters:

Code:

	@UserID      varchar(18),
	@InPassword  varchar(32),
	@SessionID   bigint,
	@UserIP      varchar(15)

upon correct login (ie correct verification of name account & password by OMG_GameWEB.dbo.sp_OmgLoginSuccessCheck), that proc execs:

Code:

	usp_Insert_LoginLog_E @SessionID, @UserUID, @UserIP, 0, @LoginTime, 1

here (may) come the mess, in (one of) my DB, that proc. inserts a record into PS_UserLog_yyyymm.dbo.UserLog with 'yyyy' the current year, 'mm' the month.
of course there is no 'create database' within that script and such DB likely does not exist.

so you may want to change that proc (usp_Insert_LoginLog_E) to insert a record into PS_GameLog.dbo.UserLog (instead of a date based DB) but ... second issue UserLog (at least mine) does not contains an UserIP field; so you will have to add it and modify the insert statement of usp_Insert_LoginLog_E accordingly.

after all these checks, modif. and updates, if your purpose is to "Find all Chars connected to IP" you will have a proc (or a immediate script) doing:

assuming:

Code:

declare @UserIP varchar(20)

do:

Code:

select um.UserID,ch.CharName,ul.UserIP
from PS_GameLog.dbo.UserLog ul
	inner join PS_UserData.dbo.Users_Master um ON um.UserUID=ul.UserUID
	inner join PS_GameData.dbo.Chars ch on ch.UserUID=ul.UserUID
where ul.UserIP=@UserIP

02/06/2011 20:53 ProfNerwosol#10

There's no need to check for player's IP. Just check what the UserUID is and then do a JOIN between Chars and ActionLog tables. This will show you which IP's the players logged in with.

Every login attempt is registered in ActionLog table, with UserUID, CharName, UserIP, etc.

Code:

DECLARE @UserUID int;

SELECT @UserUID=UserUID FROM PS_GameData.dbo.Chars WHERE CharName LIKE '%%';

SELECT C.UserUID, AL.UserID, C.CharID, C.CharName, AL.Text1 AS UserIP
FROM PS_GameLog.dbo.ActionLog AS AL INNER JOIN PS_GameData.dbo.Chars AS C
ON AL.UserUID=C.UserUID
WHERE AL.ActionType=107 AND AL.UserUID=@UserUID
ORDER BY C.UserUID, AL.Text1

02/07/2011 00:25 castor4878#11

Quote:

There's no need to check for player's IP

there are plenty of cases in daily life where it is perfectly true :)
now, what about cases where we are looking for information on a specific IP ?...

to quote myself:

Quote:

You didn't explain your goal Tnelis

if the goal is to know which accounts were used with one specific IP - because GM is suspecting abusive accounts (to get free DP for instance); or which toons were played with one specific IP (for instance coz someone complains that one of his toon was used by someone else) then you are looking for "all Chars connected to IP", you do need an IP and you will definitively want a search on that criterion.

OOH, if the goal is to know which IPs (IPs not IP) were used by an account, and the original request shows this type of research (in contradiction with the title of the thread) then you do not need an IP as a search criterion.

Let's say the 2 needs were processed.

02/07/2011 23:03 ProfNerwosol#12

PM me about this. I am not feeling well today and tomorrow I have work. On Wednesday, unless someone is faster, I can work on those two problems and post working queries.

02/08/2011 00:00 RebeccaBlack#13

"all Chars connected to IP"

This basically explains my reason for the creation of this.

I already made a table, and altered the login to insert if the userid didn't exist and update if it does

02/10/2011 14:28 zargon05#14

I still don't understand what you guys are trying to do but if you want all unique IP's connected with your server say on a particular day then simple use the UNIQUE function on the Text1 coloumn and the required date.