PW formatlog importer and analyzer(v0.1)

You must know the SQL script very well, otherwise this program is useless for you...

Guide:
Requirement: MSSQL Server 2000/2005/2008
1. Create a new database called "pwlogs" on your SQL server.

2. Run these SQL scripts to build the tables:

Code:

---

USE PWLOGS
CREATE TABLE [dbo].[loginlog](
    [time] [datetime] NULL,
    [uid] [int] NULL,
    [ip] [varchar](15) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[rareitem](
    [itemid] [int] NOT NULL,
    [itemname] [varchar](64) NULL,
 CONSTRAINT [PK_rareitem] PRIMARY KEY CLUSTERED
(
    [itemid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tradelog](
    [uid] [int] NULL,
    [rid] [int] NULL,
    [itemid] [int] NULL,
    [count] [int] NULL,
    [time] [datetime] NULL,
    [tid] [int] NULL,
    [type] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tradelog2](
    [tid] [int] NULL,
    [traderA] [int] NULL,
    [traderB] [int] NULL,
    [uidA] [int] NULL,
    [uidB] [int] NULL,
    [type] [int] NULL,
    [time] [datetime] NULL,
    [ipA] [varchar](15) NULL,
    [ipB] [varchar](15) NULL,
    [lvl] [int] NULL
) ON [PRIMARY]
GO

---

3. Open the "cfg.ini" and config the sql connection settings.

4. Run the program, click "Import" button to load the world2.formatlog, it will take some minutes to complete the import(cost time is depend on the size of the log file), then click the "Analyse" button to analyse the transaction type, it also will take some time.

5. After the import and analyse done, you can open the SQL query analyzer to query any transaction details you want with your custom script.

6. The definition of some important columns:

Code:

---

uid: Account ID, corresponds to "ID" of table "users" in the PW account database.
rid: Role ID or character ID
itemid: Item ID, you can get the item name with querying this ID in pwdatabase.com or your elements.data
count: The amount of the item
tid: Transaction ID, each transaction will be assigned a different tid by server, and the tid will be recount from 1 once the log service restart
type(tradelog): 1 or 2, 1 = add, 2 = remove, in a transaction, the trader might add an item or remove an item
type(tradelog2): 1 or 2 or 3, 1 = Trader A give items to Trader B without exchange, 2 = Trader B give items to Trader A without exchange, 3 = Exchange stuff between Trader A and Trader B
TraderA: Trader A's rid
TraderB: Trader B's rid
UidA: Trader A's uid
UidB: Trader B's uid
ipA: TraderA's ip address when doing this transaction
ipB: TraderB's ip address when doing this transaction

---

7. The table "rareitem" is used to add the frequently-used item informations, like:

Code:

---

insert into pwlogs.dbo.rareitem values(15320,'DS')
insert into pwlogs.dbo.rareitem values(15049,'RB')

---

Then it will be easier to get a better report when you query, example:
I wanna see character ID 95713's one-way transaction record between 2010.3.31 and 2010.4.2, I can do this query:

Code:

---

select c1.itemname,c2.* from
(select  t1.time,t2.traderA,t2.traderB,t1.itemid,t1.[count],t2.ipA,t2.ipB
 from tradelog as t1,tradelog2 as t2
where t1.tid=t2.tid and t2.type=1 and t2.traderA=95713
union all
select t1.time,t2.traderB,t2.traderA,t1.itemid,t1.[count],t2.ipB,t2.ipA
 from tradelog as t1,tradelog2 as t2
where t1.tid=t2.tid and t2.type=2 and t2.traderB=95713  ) as c2
left join rareitem as c1
on c1.itemid=c2.itemid
where c2.time>'2010-03-30 23:59:59 PM' and c2.time<'2010-04-03 01:00:00 AM'
order by c2.time asc

---

Then the report will be very clear.(check the attachment screenshot)

Could anyone tell me how to decrypt the messages in world2.chat? I wanna make a importer for that file.

Quote:

---

Originally Posted by quakerv

Could anyone tell me how to decrypt the messages in world2.chat? I wanna make a importer for that file.

---

It's base64 encoding...

Quote:

---

Originally Posted by ronny1982

It's base64 encoding...

---

:ott1: nice, it's working.

Quote:

---

Originally Posted by quakerv

You must know the SQL script very well, otherwise this program is useless for you...

Guide:
1. Create a new database called "pwlogs" on your SQL server.

2. Run these SQL scripts to build the tables:

Spoiler:

USE PWLOGS
CREATE TABLE [dbo].[loginlog](
[time] [datetime] NULL,
[uid] [int] NULL,
[ip] [varchar](15) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[rareitem](
[itemid] [int] NOT NULL,
[itemname] [varchar](64) NULL,
CONSTRAINT [PK_rareitem] PRIMARY KEY CLUSTERED
(
[itemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tradelog](
[uid] [int] NULL,
[rid] [int] NULL,
[itemid] [int] NULL,
[count] [int] NULL,
[time] [datetime] NULL,
[tid] [int] NULL,
[type] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tradelog2](
[tid] [int] NULL,
[traderA] [int] NULL,
[traderB] [int] NULL,
[uidA] [int] NULL,
[uidB] [int] NULL,
[type] [int] NULL,
[time] [datetime] NULL,
[ipA] [varchar](15) NULL,
[ipB] [varchar](15) NULL,
[lvl] [int] NULL
) ON [PRIMARY]
GO

3. Open the "cfg.ini" and config the sql connection settings.

4. Run the program, click "Import" button to load the world2.formatlog, it will take some minutes to complete the import(cost time is depend on the size of the log file), then click the "Analyse" button to analyse the transaction type, it also will take some time.

5. After the import and analyse done, you can open the SQL query analyzer to query any transaction details you want with your custom script.

6. The definition of some important columns:

Spoiler:

uid: Account ID, corresponds to "ID" of table "users" in the PW account database.
rid: Role ID or character ID
itemid: Item ID, you can get the item name with querying this ID in pwdatabase.com or your elements.data
count: The amount of the item
tid: Transaction ID, each transaction will be assigned a different tid by server, and the tid will be recount from 1 once the log service restart
type(tradelog): 1 or 2, 1 = add, 2 = remove, in a transaction, the trader might add an item or remove an item
type(tradelog2): 1 or 2 or 3, 1 = Trader A give items to Trader B without exchange, 2 = Trader B give items to Trader A without exchange, 3 = Exchange stuff between Trader A and Trader B
TraderA: Trader A's rid
TraderB: Trader B's rid
UidA: Trader A's uid
UidB: Trader B's uid
ipA: TraderA's ip address when doing this transaction
ipB: TraderB's ip address when doing this transaction

7. The table "rareitem" is used to add the frequently-used item informations, like:

Spoiler:

insert into pwlogs.dbo.rareitem values(15320,'DS')
insert into pwlogs.dbo.rareitem values(15049,'RB')

Then it will be easier to get a better report when you query, example:
I wanna see character ID 95713's one-way transaction record between 2010.3.31 and 2010.4.2, I can do this query:

Spoiler:

select c1.itemname,c2.* from
(select t1.time,t2.traderA,t2.traderB,t1.itemid,t1.[count],t2.ipA,t2.ipB
from tradelog as t1,tradelog2 as t2
where t1.tid=t2.tid and t2.type=1 and t2.traderA=95713
union all
select t1.time,t2.traderB,t2.traderA,t1.itemid,t1.[count],t2.ipB,t2.ipA
from tradelog as t1,tradelog2 as t2
where t1.tid=t2.tid and t2.type=2 and t2.traderB=95713 ) as c2
left join rareitem as c1
on c1.itemid=c2.itemid
where c2.time>'2010-03-30 23:59:59 PM' and c2.time<'2010-04-03 01:00:00 AM'
order by c2.time asc

Then the report will be very clear.(check the attachment screenshot)

---

can you simple upload your dump sql, coz i got error using that command

Spoiler:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE [dbo].[loginlog]( [time] [datetime] NULL, [uid] [int] NUL' at line 2

mssql or mysql?

It can't work with MySql, this program is for MS SQL Server(2000/2005/2008).

Epic fail.... MySQL is better.... :/:

/cry

mysql version please?
i dont wanna have to download it from server then import into my mssql then export as .sql then convert that to mysql format then upload to server then import into mysql.

which is the only way atm -.-

MySQL pleaseeeeeeeeeeeeeeee :*:

It's very quick to download and install a SQL server 2005/2008 express, and it's much more powerful than Mysql when make some complex queries.

yeah MSSQL probably is the best and of course it's from microsucks.
but it will distrubing your windust, and make slow windust, coz it to much service runs in there.

most of us in here using MySQL, coz it run directly under linux and not distrubing windust.:blushing:

MS SQL server sucks! MySQL is better !

And better than all --> Postgre SQL ! =_=

omg so noob to say that ms sql server are the best...

Is this thread going off-topic... ?

If yes, here is my suggestion:

Why not using Oracle DB on HP-UX for storing the log-info xD, i think this is the best setup to handle a single database with 5 tables and 100.000 transactions... :)::laugh::lol:

@_@ oracle...:jaw:... wow ...... will make my head :boom::flame:

I have a good MySQL chat reader its just making it memory buff friendly atm.

Quote:

---

Originally Posted by Romulan

MS SQL server sucks! MySQL is better !

And better than all --> Postgre SQL ! =_=

omg so noob to say that ms sql server are the best...

---

hot-headed boy, could u read other ppl's post carefully before u spout, I never said MSSQL is the best.
MSSQL is better than MySql when you need to do lots of multidimensional and deep data analysis works.