Improved Logging System

[rafoka]

Hello everybody,
 

Just a Better (intuitive) Log System

Problem

In my life of server administration always happened a few episodes where I had to check the logs and this always was a so boring task. Despite the caution, I had problems with players beign hacked (stolen their items) and even corruption of GameMasters. So I decided to spend some good time (I do not have much experience with the source of rAthena) to make a better system logs, so I can solve all kinds of problems (hacking, GM corruption, Bug Abuse, etc.) quickly and efficient.

Solution

In this system I improved some things and I left these things the way I initially had idealized the logs (in a more intuitive way), I'll try reporting the changes for each transaction:

 

1. A General View

 

-- Trade/Vending/Buying Store/Mail

Very simple, the new values of the row (related_char_id, related_account_id) is the another player of the trade/vending/buying store.

 

-- Pick

In every player Pick (type = 'P') the (related_char_id, related_account_id) can be another player or a mob (both columns = mob_id).

It varies between who primiraly dropped the item to the floor: A player or a mob (when the mob die).

 

-- Auctions

In every auction transaction will be a info code to help you to discover what's going on! Later I will explain each code.

 

-- Loot

Mobs can loot items, when this happens will be a row with type='L'. Related_ids of this row is the item origins (a player or another mob, is who primarily dropped the item).

 

-- Drop

When a mob or player drops an item, it's still not related to anybody because we can't know who WILL pick the item. So in this case related_ids = 0.

 

-- Scripts/NPCs

If a NPC give/delete a item, the row column related_npc_name = npc name.

 

-- Stolen/Mvp prizes

It are in essence equal to mob drop, and works just like this one.

 

2. Explaining better some features

 

These are the features that give me the harder work to make. It's hundreds of lines changeds in the source, but I think it's worth. The Drop/Pick System is a little complex, and I explain above how it works, but if you don't want to know everything you can just jump this section!

 

-- Trade/Vending/Buying Store

Very simple, the new values of the row (related_char_id, related_account_id) is the another player of the trade/vending/buying store.

 

 

-- Drops/Picks Improved
To understand this improvement well, you need to know that the emulator handles in different way the actions of: Mob drops a item (type='M') and Mob drops a looted item (type='L', amount negative).
Also there's an operation where Mobs Loot a item (type='L', amount positive). But let focus on the informations that will be provided on the log rows, and how the system works:

- Mob drop an item (type='M')
Main ID is the mob_id. Item origins are setted=mob_id.
Related_IDs = NULL. The monster dies and drop the item to the floor.

- Player drop an item (type='P', amount negative)
Main ID is the player. Item origins are setted=player_id.
Related_IDs = NULL. The player just dropped the item to the floor.

- Mob Loot an Item (type='L', amount positive)
Main ID is the mob_id. Item origins aren't changed.
Show item origins in related_IDs (can be a mob_id or a player_ids).

- Mob Drop an Looted Item (type='L', amount negative)
Main ID is the mob_id. Item origins aren't changed.
Show item origins in related_IDs (can be a mob_id or a player_ids).

- Player Pick an item (type='P', amount positive)
Main ID is the player.
Show item origins in related_IDs (the player who dropped) or (the mob_id that dropped the item).
NOTE: Here is when you know the source of a player pick. So as i said: A player can't pick another player item without making a trace in related columns.
More properly speaking, a player can't pick a item without you know the REAL SOURCE that primarily put it on the floor.

- Mail
In this system, mail transactions are full logged with related player that send and receive items/zeny on attachments.

- Extra info on Auctions (and other systems that use mail_sendmail).
New parameter send_is_NPC: Field of NPC Name will be logged.
Alter on sender_id: If send_is_NPC is true, can use it to log a Related Player to this transaction.
New parameter extra_log_info: If send_is_NPC is true, this string will be added on the end of npc_name_related on the logs.
Extra info on NPC NAME of Auctions:
 "#0": new auction created,
 "#1": auction ended by time,
 "#2": auction ended by seller,
 "#3": auction ended by buy now,
 "#-1": auction canceled by seller,
 "#-2": auction no buyers,
 "#-3": auction {returned} bids,
 "#-4": auction failed by techinical error.
 
- Other Systems that uses mail_sendmail()
If you have a mod in source that uses mail_sendmail() you can add the new parameters (send_is_NPC, extra_log_info) to put information
that you can view later on LOGs. And also, you can turn send_is_NPC=ON and use sender_id param to LOG that transaction binding a player to that
transaction as a sender. Imagine that: A NPC system (that uses mail_sendmail()) that the GMs uses to give rewards to players in events, so you can
call mail_sendmail() with send_is_NPC=true, sender_id=acc id of GM, send_name = "Event Rewards", extra_log_info = "". And all will be fine.
In mail picklogs the GM CID and AID will be logged as related.

 

3. Tables

PickLog Table

 

4 New columns:
account_id: just the account_id of the player who triggered this picklog.
related_char_id and related_account_id: These are the fields that will represent a person related to that transaction picklog BUT not the main person. (The other person in the trade, sale, mail, etc.). If is a pick from original mob drop (mob die and drop-NOT LOOT), it will be both = mob_id.
related_npc_name is used when a npc is related to the transaction (name of the npc).

 

Note: In transactions where there's no other player/npc/mob related, these related_values will be just 0 or a empty string.

IDs 5-6: Trade

IDs 8-7: Vending

IDs 9-10: PlayerA drop and then playerB pick.

IDs 15-18: PlayerA drop, mob(1153) loot, PlayerB pick.

IDs 19-23: mob(1153) drop, mob(1177) loot, player pick.
IDs 58-59: NPC buy/sell
IDs 67-70: PlayerA make mr.smile quest
IDs 71-72: Mob drop, player pick.

IDs 79-81: Weapon Refine Skill

 

IDs 109-110: mob 1159(phreeoni) 'drop' mvp prize item, playerA pick.
IDs 112-113: PlayerA send mail, PLayerB pick attachment.
IDs 116-117: Auction Started, Auction finalized (no buyers in time).
IDs 122-121: BUying Store.

ID 123: PlayerA atcommand "#item playerB poring_card"

 

 

ZenyLog Table

4 New columns:

account_id: just like picklog. (main player who triggered zenylog)
related_char_id and related_account_id: just like picklog. (another person/mob related)
related_npc_name just like picklog. (npc related)

 

The transactions Trade, Vending, #Zeny, Mail, Auction, Buying Store create a related_ids to the another player in the transaction.
Buy/Sell and "set Zeny, x" are NPC-Related so related_npc_name will be = the related npc name. (oh rly!?)

 

Example:

ID 1: Kafra "set Zeny, Zeny - 820" -> Cart Rental

IDs 2-3: Trade

IDs 4-5: Vending

IDs 6-7: Zeny Knife granted zeny
IDs 9-10: Buy/Sell on NPC

IDs 12-13: Loss zeny on skills

IDs 17: PlayerA sent a mail to playerB with zeny attached

IDs 19: PlayerA started a auction a pay the fee.

IDs 20-21: Buying Store

AtcommandLog Table
There are very few modifications in this table. Just the related_ids that are used when someone uses a #command that affects another player. So, related_ids = who typed #command.

ID 191: Rafa2 gives card to Merchant player. -> Related info!

ID 216: Rafa2 picks a card to himself

ID 217: Rafa2 gives zeny to Merchant player -> Related info!

ID 220-221: Rafa2 uses #command but to himself, no related info.

 

#Commands also works well with picklog and zenylog, as you can see on the screens:

 

Pick:

 

Zeny:

 

Note: If playerx types #zeny playerx 1000 the related_ids should be = 0, because in this case it's just not related to someone else.

 

ChatLog
2 new fields: dst_charid, dst_accountid. As the name says, it's the player who received the whisper message (to others types of message it's = 0).

SQL Indexes
I also added some very useful indexes IMO on sql logs to make the select queries faster (I know that it make the insert slower), but when you have a 4 GB picklog DB it's better to have some CPU Cycles lost everytime than can't make queries without mysql going to 99% CPU utilization. If someone knows better about indexes optimization I can edit this to get a better performance to all of us

 

If you want to know more about each transaction or you you're not convinced that this system is safe (saves 100% of logs and leaves no item can not be tracked) then download the system and make your tests.

because it is not very easy to teach everything that was done in all cases, so: Put down and try it! I challenge you to find a vulnerability.

 

NOTE: unique_id feature also greatly enhances your ability to track items but does not work for non-stackable items such as cards, consumables, then this system still has its uses

 

4. Download

I would recommend to do a manual diff apply, it's not a big deal for who know.

 

For download, there's a one that is completely based on rAthena 17170. Improved Logging System v1.2 rAthena.diff

And another to eAmod-rAthena r49. Improved Logging System v1.2 _eAmod.diff

 

SQL mail modified table: improved_log_mail.sql

Logs SQL Modifieds tables: improved_logging_system.sql

 

Maybe it's best you download a new revision of 17170 rAthena locked in, apply the patch and then update the SVN. Because are many changes in source, edit it is boring (I did twice and I'm very tired).

 

5. Changed Functions (how to handle with the new parameters!)

 

I do not intend to update this code often to be laborious. I changed the code in much the second parameter of the functions, I did it because if there is something wrong it will generate compilation errors more easily. Compilation errors are your friends! So you can see where the error is and fix, which is much better than having a bug and do not know where to fix it.

 

But I will not leave you in deep shit and above are the modified functions and how to handle with them:
 

typedef struct // Improved Logging System [rafoka]
{
    /* field  // equivalent on SQL row */
    int type; //
    int int1; // `related_char_id`
    int int2; // `related_account_id`
    char str1[40]; // `related_npc_name`
} Related_Log_Info;
 
typedef enum e_log_improved_system // Improved Logging System [rafoka]
{
    LOG_RELATED_NOTHING                 = 0,
    LOG_RELATED_PLAYER                  = 1,
    LOG_RELATED_NPC                     = 2,
    LOG_RELATED_MOB                     = 3,
    LOG_RELATED_OTHER                   = 4,
    LOG_RELATED_PLAYER_BUT_SD_OFFLINE   = 5,
    LOG_RELATED_LOOT                    = 6,
    LOG_RELATED_CUSTOM_MAIL             = 7,
    LOG_DO_NOT_LOG                      = -1,
}
e_log_improved_system;
 

Explanation: This struct is used to keep the related_values (who is related to that transaction) and it's passed by function to function until get on the log_pick/log_zeny/log_atcommand functions, that saves in SQL all the row and uses Related_Log_Info. IF your transaction don't have a related object (player, npc or mob) you can just put NULL on a function that receives Related_Log_Info* as argument (it's safe to!).

 

void log_pick_pc(struct map_session_data* sd, Related_Log_Info* info, e_log_pick_type type, int amount, struct item* itm);
void log_pick_mob(struct mob_data* md, Related_Log_Info* info, e_log_pick_type type, int amount, struct item* itm);
void log_zeny(struct map_session_data* sd, Related_Log_Info* info, e_log_pick_type type, struct map_session_data* src_sd, int amount);

void log_chat(e_log_chat_type type, int type_id, int src_charid, int src_accid, const char* map, int x, int y, struct map_session_data* tsd, const char* dst_charname, const char* message);
void log_atcommand(struct map_session_data* sd, Related_Log_Info* info, const char* message);

Explanation: The related_log_infos is obviously: It is to receive the info to fill the (related_ids and related_npc_name) on the SQL rows.

 

/* Fields (send_is_NPC and extra_log_info) added on Improved Logging System,
 * send_is_NPC: Mails Attachments will be logged with npc_name_related = send_name. Mails can't be returned.
 * sender_id: If send_is_NPC is true, this optional CID will be used only for picklog.
 * extra_log_info: If send_is_NPC is true, this string will be added on the end of npc_name_related on the logs.
 * It's recommended to put a Small Text that means something for logs. E.g: In auction mails ("#0": new auction created, "#1": ended by time,
 * "#2": ended by seller, "#3" : ended by buy now, "#-1": canceled by seller, "#-2": no buyers, "#-3" returned bids.)
 * NOTE: extra_log_info NOT recommended >10 length strings. */
void mail_sendmail(int send_id, const char* send_name, int dest_id, const char* dest_name, const char* title, const char* body, int zeny, struct item *item, bool send_is_NPC, const char extra_log_info[11])

Explanation: It's explained in the text: send_is_NPC is a flag to custom mails (not ordinary mails sended by players) and extra_log_info is a optional argument that writes a little extra text on Log ROws when the player picks the attachment of this mail.

 

int mail_removeitem(struct map_session_data *sd, Related_Log_Info* info, short flag);
int mail_removezeny(struct map_session_data *sd, Related_Log_Info* info, short flag);

void mail_getattachment(struct map_session_data* sd, Related_Log_Info* info, int zeny, struct item* item);

Explanation: All the cases are already handled I don't think do you need to edit any of this. You can just put NULL because these functions are used only when mails are failed to be sent. There is just one time when we need to use mail_removeitem(sd,&info,1) in the place of common mail_removeitem(sd,NULL,0) and it's of course already handled in the diff. mail_getattachment() is when the player will pick the item so you always need to call this function with info correctly setted, actually it's handled on the diff but we don't know, the things can just change.

 

int pc_payzeny(struct map_session_data*,Related_Log_Info* info,int, enum e_log_pick_type type, struct map_session_data*);
int pc_additem(struct map_session_data*,Related_Log_Info* info,struct item*,int,e_log_pick_type);
int pc_getzeny(struct map_session_data*,Related_Log_Info* info,int, enum e_log_pick_type, struct map_session_data*);
int pc_delitem(struct map_session_data*,Related_Log_Info* info,int,int,int,short,e_log_pick_type);

int pc_cart_delitem(struct map_session_data *sd, Related_Log_Info* info, int n,int amount,int type,e_log_pick_type log_type);

int pc_setparam(struct map_session_data*,Related_Log_Info* info, int,int);

Explanation: If there's no one object related to the transaction (player, mob or npc) you can just put NULL on these parameters. If there's a object (but not the main player) that is related to that transaction so you need to create a Related_Log_Info object and set the related object informations using make_related_log_info() and then call the function with all information correctly setted on the Related_Log_Info. There's a lot of examples on the DIFF. This varies if is a player, mob or npc. If If I'm not mistaken, pc_cart_delitem(sd,&info..) is only used on vendings and pc_setparam(sd,&info...) is only used to "set Zeny" in scripts.

 

Functions that are your friends

void make_related_log_info(Related_Log_Info* info, int type, int char_id, int account_id, const char* npc_mob_name);
void null_related_log_info(Related_Log_Info* info);

If someday you want to create a new transaction or the SVN Update just created a new transaction on the source and you want to fill the related_Columns of this transaction so you can use these functions. There's a lot of examples in the DIFF on how to use it. If it's related to a mob, char_id and account_id = mob_id, if it's related to a npc, char_id and acc_id = 0 and npc_mob_name = name of npc. The 1st function catch info of log and put on the Related_Log_Info, the second just fill the Related_Log_Info with NULL (but sometimes it's needed to avoid crashs).

 

Data structures changed

 

struct item {
    int id;
    short nameid;
    short amount;
    unsigned short equip; // location(s) where item is equipped (using enum equip_pos for bitmasking)
    char identify;
    char refine;
    char attribute;
    short card[MAX_SLOTS];
    unsigned int expire_time;
    char bound, favorite;
    uint64 unique_id;
    int droppedBy_charid, droppedBy_accid;
};

When in the source you drop a item (map_addflooritem), you need to use droppedBy_charid and droppedBy_accid to write who dropped the item (or the mob).

 

Soo.. that's all folks! I worked hard here so that everyone who wants to use the system, able to do this. And use it without any problem in the future (or few problems) and being able to edit something from the source if necessary.

 

EDIT:

6. Updates

 

I explained how to do when there's a update that "conflits" with the Improved Logging System. But I will put here a update (example) and also because I think this one is important:

 

Revision 17142 (cashshop inside the game creation): cashshop.c IMPROVED LOG SYSTEM patch r17142.patch

 

It is at the same time a example how to create new logs with this system.

 

See ya!

Edited April 12, 2013 by rafoka

[Cydh]

nice idea.

[rafoka]

Good to see that someone appreciates it. It took some time to do because there are many changes in source. I've been thinking about optimizing the code and fix the diff to rAthena, but only worth it if someone else besides me is using, hehe.
 

[Angel]

I like that system, you save a lot of time filtering tables.
Thanks
 

[Thanna]

it would be good for server admins

[rafoka]

Today I spent some time cleaning up the code and making the system more coherent. And there was also a recent update of the auction messages that left my diff quickly outdated.

I will test the changes I made, move to a new version of rAthena and then I post the diff here. See ya.

 

EDIT: Hello Guys,

HUmpfff! FInally i edited all the topic, put examples and explained how to how to use the system modified functions, so it does not break in the future.

 

NOTE: About revision 17170:

 

If you experienced messages like ??#0 in the related_npc_name field (picklog) after making an auction, you need to increase the value of CHAR_MAX_MSG to 250 or higher,
this is a rAthena bug of revision 17170 and I think it will be fixed soon.

Edited March 6, 2013 by rafoka

[Mark]

Sistema MUITO bom!! Parabéns Rafoka!!

[gunvald]

I'm getting this error when recompiling with latest rathena svn:

 

 

 

1>i:\rathena\src\map\script.c(10233): warning C4047: 'function' : 'Related_Log_Info *' differs in levels of indirection from 'int'
1>i:\rathena\src\map\script.c(10233): warning C4024: 'pc_delitem' : different types for formal and actual parameter 2
1>i:\rathena\src\map\script.c(10233): error C2198: 'pc_delitem' : too few arguments for call
1>i:\rathena\src\map\script.c(10268): warning C4133: 'function' : incompatible types - from 'item *' to 'Related_Log_Info *'
1>i:\rathena\src\map\script.c(10268): warning C4047: 'function' : 'item *' differs in levels of indirection from 'int'
1>i:\rathena\src\map\script.c(10268): warning C4024: 'pc_additem' : different types for formal and actual parameter 3
1>i:\rathena\src\map\script.c(10268): error C2198: 'pc_additem' : too few arguments for call
 

 

[rafoka]

I'm getting this error when recompiling with latest rathena svn:

 

 

 

1>i:\rathena\src\map\script.c(10233): warning C4047: 'function' : 'Related_Log_Info *' differs in levels of indirection from 'int'
1>i:\rathena\src\map\script.c(10233): warning C4024: 'pc_delitem' : different types for formal and actual parameter 2
1>i:\rathena\src\map\script.c(10233): error C2198: 'pc_delitem' : too few arguments for call
1>i:\rathena\src\map\script.c(10268): warning C4133: 'function' : incompatible types - from 'item *' to 'Related_Log_Info *'
1>i:\rathena\src\map\script.c(10268): warning C4047: 'function' : 'item *' differs in levels of indirection from 'int'
1>i:\rathena\src\map\script.c(10268): warning C4024: 'pc_additem' : different types for formal and actual parameter 3
1>i:\rathena\src\map\script.c(10268): error C2198: 'pc_additem' : too few arguments for call
 

 

Well, you can also read what I've said in 5. Changed Functions (how to handle with the new parameters!).

 

But for now I will help you.

You should add a NULL in the pc_delitem, pc_additem functions on the second parameter!

 

Like this:

 

before: pc_delitem(sd,i,...)

after: pc_delitem(sd,NULL,i,...)

 

Do the same for pc_additem (put a NULL in the second parameter).

 

Always, if there's missing a parameter error on a function and there isn't a player/mob/npc related in this transaction that you want to log on pc_additem or pc_delitem so you should add the NULL on the second parameter.

 

Hope that this explains well

[gunvald]

Thank you!! :3

[XeroBall]

rathena 17271 patch error please edit problem plz

[rafoka]

rathena 17271 patch error please edit problem plz

Sorry, but I'm trying to show to you in the tutorial How to Handle Patch Errors. You aren't supposed to take rathena 17271 e apply my patch on it, you need to take rathena 17170, apply the patch and then update and then apply my patch of section 6. Finally you should verify by errors on compile and read the section 5 to handle these errors. If I do the way you are saying I'll need to update the .patch file all the time and it's not nice.

Edited April 20, 2013 by rafoka

[Prevalence]

any news?

[Sunzuke]

this is great. why is this not implemented in rA? 

[EriN_KillerSoul]

Hello everybody,

...

 

Can use for hercules?

Edited August 17, 2013 by Capuche
Removed long bad quote >>

[nndsl]

nice

Edited August 26, 2013 by nndsl

[Psyz]

Still functional in the new version?

@rafoka