New tool - SERVERSTATS
 

This new tool is being coded and will allow a server admin to check things like -


/1/ User accounts and details
/2/ Items dropped per zone
/3/ Items owned by users
/4/ NPC's in each zone
/5/ Coins owned by users
/6/ Change User's server privelages
/7/ Summary of who used what #emu commands

...

Since this is an extensible tool, it will allow one to add more queries.

Since this is early in the dev stage for the program, I am asking for more queries that you want added.

This will be a straight forward reporting tool, with limited write back to db functionality.


GeorgeS

Update -

I also am adding a polling tool to show a line graph of selected accounts/characters with plots of level and pp over time. So if there's a character who went from 1-60 in a day, you will know.

The tool will poll LOGS and parse their contents so you will have a snapshot of who used what GM commands.

You will also be able to change GM status, class,race, delete accounts and so on.

...change what zones are bootable, see how many players are on and where they are playing...

GeorgeS

Sounds awesome, George. Can't wait =)

George, are you coding this into the worldUI, or it's going to be a standalone thing ?

it would be really nice if it was coded into the worldui. but we can only hope.

there have been a couple of good utils latley the people seem determined to code outside of the worldui.

I have decided not to integrate it to the emu directly, but I did consider it. I was going to add a special #command. This tool will attempt to minimize sql server resources, but certain sql commands should be run sparingly - for example running the script that returns all items dropped in a zone.

Ok, so I looked at the cpp profile struct and managed to decode everything there. I'm doing the coding right now on the accounts admin part and should have a read only version ready in a few days.

So far it's going well and looking good.

GeorgeS

Allowing it to view and edit player coin would be a GODSEND.

Thank you, so much for doing this tool, I just know that it's going to be awesome!

Ok, the read/only part of the tool is done, including viewing/sorting all player owned coin and banked coins too - these and many other parts of the binary struct data are going to be read/write too.

GeorgeS

Out of curiosity, what are you using to create this?

I ask because I have created a script to convert the entire profile into a multi-dimensional array in PHP (about 14KB worth of code). It was a pain in the butt for me, so I would hate to see others go through the same thing.

On a side note, I have been thinking about how to edit the profile field, but the checksum is confusing me, mainly because it doesn't match what crc32() in PHP calculates it to. But then again, I've kinda put that one on the back burner.

GeorgeS, I think roja was talking about the perl/html Emu Management scripts that come with the emulator world. Those pages haven't seen a pixel change in the year I've been here - though I have customized quite a few for my own purposes (and soon as I get time to clean up my slop, I hope to share them back to the core devs).

Is it possible to do this kind of editor/viewer in the perl/html?

George... I love you. I can't wait for this.

Dax

The tool will not be a comprehensive profile editor, just a few selected byte values like last name, coins etc..

Actually I'm using VB, and to convert the entire profile blob came from an idea where I pulled the blob from the database and saved it to a binary file. The file is NOT loaded into memory, but accessed by a byte pointer. Then edited values are saved and the binary file dumped back to the DB.
Actually it's easy.

The tool does much more editing, but account management is a small portion of it's purpose. You can copy toons from one account to another, and even delete and modify toon data stored in the character_ table.

Right now you can
- find out what items drop in a zone by NPC (done)
- Identify if an item drops or is sold or player made (done)
- view NPC in a zone by level/min max/avg levels,spawngroup (done)
- account profile and character editing (as discussed before) (1/2 done)
- modify loot drops by selected npc's (not done)
- tag/poll/monitor accounts for suspicious behaviour (not done)
- view/parse #commands by users (log file parser) - (not done)
- more as time permits..

GeorgeS

WOOT!!!!!!!!

/bows

PLEASE, can you post this?

It would be invaluable.

*chomps at bit*

Dax

I just created an entry in the Wiki: ConvertProfile

I commented out all of the unknown fields, but did add in the "code" to read them into the array.

I would also like to note that you will need to add in a value for the $db variable, which allows the connection to the database (I use another included file that stores the db login info for all of the pages I use).

Maybe I don't understand this, but wouldn't it be easier to just to create a format and unpack() the whole binary struct ? Especially if you want to repack it and update the DB.

I got about 1/3 of the way through creating the format before I got bored and moved on to something more interesting.

I too took apart the player profile bit by bit, so I could understand it. It's pretty easy to see where things are using the struct, or at least that's where I see the values. And yes, it is huge and mind boggling. I was looking for one variable, which I never found. ;)

Excellent work so far!

And you too, Sir! :)

Progress to date -

Right now you can
- find out what items drop in a zone by NPC (done)
- Identify if an item drops or is sold or player made (done)
- view NPC in a zone by level/min max/avg levels,spawngroup (done)
- account profile, character del/moving/editing (as discussed before) (done!)
- tag/poll/monitor accounts for suspicious behaviour (done!)
- view/parse #commands by users (log file parser) - (not done)
- modify loot drops by selected npc's (not done)

- more as time permits..


The poll monitor works great as of tonight!
Next is the loot editor, then log parser, then it's out for release - perhaps by this weekend.

Account monitor detail -
The account montor looks at any number of selected accounts/toons for level and coin (delta values) over time. All data are saved to a log file and plotted real time as well.
You can select a polling interval in minutes, and the program then runs in the background and polls all night long for you.


GeorgeS

unpack() is certainly one way to do it. As a matter of fact, I started to work on a version of the same script to utilize unpack, but it can be difficult to modify if the profile blob ever changes, or if you make a mistake somewhere in the script. Not only that, but you would probably run into some issues when you have to create sub-arrays, like for bind points, item materials, item tints, AA's (which current has 240 sub-arrays), languages, spells in the spell book, memorized spells, skills, buffs, group members, tributes, disciplines, leadership abilities, bandoliers, and potion belts (whew!). The easiest way I've found so far to handle those is using for() statements. Otherwise, the script would be about 3x larger than it is right now. But, I do use it to convert data types (to float for example).

As far as efficiency, I'm not sure if unpack would take less time to process. I do know that, on the initial load (query and all), the page takes between 0.8 seconds & 1.3 seconds for PHP to process (including a Magelo-like character viewer), and on subsequent loads (after the query is cached), it takes between 0.3 seconds & 0.7 seconds to process. Of course, my server is a 433 MHz processor w/ 256MB of RAM that I had laying around.

As far as putting it back into the database, that's the easy part. There is a function, bin2asc(), listed on the page that converts the info from binary back to the ASCII format the blob is in. You can then inject it into the original $profileResult using substr_replace() or rebuild it from the array, and post it back into the database. The part I have been having trouble figuring out is the checksum, which I assume is the checksum of the blob, which I also assume prevents you from just injecting the changed info (although I haven't tested this yet). I've found how it is calculated in the source (see the checksum field in profile schema ), but I'm having some trouble understanding it.

Great work George. Interesting idea of plotting player data on graphs. Are you going to export that data to a custom table ?

AndMetal, I am not sure how else you can modify all the values and reliably put the binary data back together without first specifying the format. I agree looping through all those arrays is a real pain though. But, if you created an array of the individual formats and strung them together to make the format string, it wouldn't be that hard to modify if the struct changed. I did consider writing a script to parse the header file and write the format for me at one time :p~

Sorry to hijack your thread George.

Export to a new table - I could implement that easily. I was going to dump to a text file, but a table may be better. I'll look into that.

The graph is pretty neat. It allows you to check coin inventory or level of any characters over time.

I'm working on the log file parser today. This will enable one to see what #commands were used by any one in game. This is pretty easy, but the loot drop editor section will be a bit harder.


GeorgeS

Finished and uploaded the final version tonight.
http://66.159.225.58/eqemu/serverstats.zip

This release allows you to -
-view npc and their spawngroup in a zone
-View/Edit loot dropped in any zone
-Search for Items
-View and edit account data and associated toons
-Find which npc's dropped selected items
-Are selected items crafted or sold my merchants
-Account monitor - watch toons over time (level/coin)
-New loot editor (fully functional now)
-What #commands did your GM's or players use and when
-Class/Race Reference guide
-Edit Zone safe x/y/z, min level and min status. Other fields are read only
-Database backup tool. Backs up account/character_,faction_vales and inventory tables. You can also restore these tables if they break.


Although I've tested this with success on my MySQL server ver 4.x, you may encounter issues I did not. In any case, report any bugs to me.

Have fun with it. BTW - there's no help yet, but the tool's pretty straightfwd.

GeorgeS

First off, I really like this tool. The main issue I've had is with loading the accounts. It errors out on both my live and dev server databases (unfortunately, it doesn't let me scroll down while it's loading, and doesn't tell me which account is causing the issue, so I don't know). Here are the error messages that I get (different error for live and dev):

Live Database
http://img70.imageshack.us/img70/571...tserrordv6.png

Dev Database
(got to get the error for the dev database)

Dax

I'd really like to try this out but I can't get it working with Windows Vista Ultimate. Ran register_ocx with admin rights and restarted. No good so far.
I get, "Component 'TABCTL32.OCX' or one of its dependencies not correctly registered: a file is missing or invalid", when i try to run it.
Thanks

I'm running XP Pro, and mine was missing "mschrt20.ocx". I found and downloaded it online, then registered it, and all was well.

Dax

Thanks for the feedback. I updated the tool to register those ocx's in case you don't have them - now are provided in the zip

Also regarding the 3001 error. I'm getting this as the major bug reported right now. I am running XP and mysql 4.x and it works ok. Now I'm guessing you're all running mysql 5.x.

I'm just tempted to get vista just to help debug things like this. I'm almost certain it's the mysql handling of blobs causing this. Now I'm upgrading to mysql 5.x and will get this issue fixed asap.

GeorgeS

Thanks George. I just installed the new build on my dev server (Windows Server 2003 R2 SP2) and it needed mscomct2.ocx. Again, I just downloaded and registered it.

Dax

Ok regarding that 3001 error message some are getting -
I've installed mysql 5.x and the latest peq db. Still I get no error. So it's not to do with that.

What is possible is either the 'character_' or accounts table - if someone would send me their tables in sql format, it will eliminate some possibilities.

Those who's accounts load ok, let me know as well.

GeorgeS

George,

Just a note, as of the past few days, on your web page for all the great utilities, your link to the ServerStats 1.1b Util is pointing to the Quest Util link.

lol, I am constantly being told this, and the server always loaded serverstats.zip for me. Even the page source was correct, but that was from withinn the local network. Soo, I rebooted the server and now you have to tell me if it's working ok.
Most bizaar.

George

Good news. I loaded all 6000+ accounts from the abyss server db, and got the error! It was caused by missing profile blobs. For whatever reason, this can happen, and the program will now check for this and also suspicious level 0's (which you have the option to delete). Any character with a ??? in any field is presumed bad.

I'm adding the new version online now



When loading any data into the grid, you can sort any field. Just FYI. To monitor a toon's activity, you must first load accounts then select the toon's to add to the watch list
GeorgeS

I forgot to mention -
When loading any data into the grid, you can sort any field. Just FYI. To monitor a toon's activity, you must first load accounts then select the toon's to add to the watch list
When copying a toon, select the toon (this places it into a buffer) then select a new owner (character owner) then click on paste toon. If you select the same owner, you get a duplicated toon with a same/appended name. Thus you can copy toon from one account to another (aka migration)... (the toon, his inventory, coin,aa's (everything in profile) and factions are moved too!)

GeorgeS

George,

Still doing it, even after reloading the page. Here is what I get when viewing your page source of your download page from outside...

This should be serverstats.zip but it shows quest.zip

</font></font>&nbsp;<a href="quest.zip"><img border="0" src="graphics/folder_Item.gif" width="39" height="35"></a>
<b>
<font color="#FF0000" face="Tahoma">ServerStats 1.1c</font></b><font color="#FFFFFF" face="Tahoma"> , Latest
11/13/07&nbsp; - This tool is for server admin for
monitoring server activity and modifying accounts,toons,
and loot.<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ; It also serves as a general
tool for checking what NPC drops what and where, and
modifying loot for NPC's. You can fix stuck toons in
zones, monitor<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ; their account activity. <br>

Here is the correct line for quest.zip
</font>
&nbsp;<a href="quest.zip"><img border="0" src="graphics/folder_Item.gif" width="39" height="35"></a>
<font color="#FFFFFF" face="Tahoma">Everquest Perl Quest
Editor 1.1.4 , Latest 11/9/07 (updated quests)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ; Ever
wanted to create NPC quests easiiy?<br>
</font>

Thanks Aramid,

That helped me fix the problem. I reset the link from the webpage. Somehow the old link did not save, but should work now. Sorry for the confusion.


GeorgeS

Yep, working fine now George.

Once again, Thanks for all your great EQEmu tools!!!

Wooot! Downloading it now! I'm giddy!

Dax

GeorgeS, not sure if you know this, but I think those suspicious level 0's are toons that have never fully logged after creation. That's how they appear in my character_ table anyway. I think they are missing their race too, if memory serves.