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)
05-04-10
quakerv
Re: PW formatlog importer and analyzer(v0.1)
Could anyone tell me how to decrypt the messages in world2.chat? I wanna make a importer for that file.
05-04-10
ronny1982
Re: PW formatlog importer and analyzer(v0.1)
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...
05-04-10
quakerv
Re: PW formatlog importer and analyzer(v0.1)
Quote:
Originally Posted by ronny1982
It's base64 encoding...
:ott1: nice, it's working.
05-04-10
hrace009
Re: PW formatlog importer and analyzer(v0.1)
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.
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
05-04-10
rbb138
Re: PW formatlog importer and analyzer(v0.1)
mssql or mysql?
05-04-10
quakerv
Re: PW formatlog importer and analyzer(v0.1)
It can't work with MySql, this program is for MS SQL Server(2000/2005/2008).
05-04-10
Romulan
Re: PW formatlog importer and analyzer(v0.1)
Epic fail.... MySQL is better.... :/:
05-04-10
rbb138
Re: PW formatlog importer and analyzer(v0.1)
/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 -.-
05-04-10
hrace009
Re: PW formatlog importer and analyzer(v0.1)
MySQL pleaseeeeeeeeeeeeeeee :*:
06-04-10
quakerv
Re: PW formatlog importer and analyzer(v0.1)
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.
06-04-10
hrace009
Re: PW formatlog importer and analyzer(v0.1)
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:
06-04-10
Romulan
Re: PW formatlog importer and analyzer(v0.1)
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...
06-04-10
ronny1982
Re: PW formatlog importer and analyzer(v0.1)
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:
06-04-10
hrace009
Re: PW formatlog importer and analyzer(v0.1)
@_@ oracle...:jaw:... wow ...... will make my head :boom::flame:
08-04-10
nofxpunkerbrian
Re: PW formatlog importer and analyzer(v0.1)
I have a good MySQL chat reader its just making it memory buff friendly atm.
08-04-10
quakerv
Re: PW formatlog importer and analyzer(v0.1)
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.