PDA

View Full Version : New tool - SERVERSTATS


GeorgeS
10-09-2007, 02:58 AM
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

GeorgeS
10-09-2007, 01:31 PM
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

Amra
10-09-2007, 02:46 PM
Sounds awesome, George. Can't wait =)

Theeper
10-10-2007, 05:32 AM
George, are you coding this into the worldUI, or it's going to be a standalone thing ?

rojadruid
10-10-2007, 03:40 PM
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.

GeorgeS
10-12-2007, 01:28 PM
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

gernblan
10-12-2007, 06:07 PM
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!

GeorgeS
10-12-2007, 07:46 PM
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

AndMetal
10-16-2007, 08:39 PM
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.

John Adams
10-17-2007, 10:51 AM
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?

TheLieka
10-17-2007, 03:14 PM
George... I love you. I can't wait for this.

Dax

GeorgeS
10-18-2007, 02:30 AM
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

gernblan
10-22-2007, 03:48 AM
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


WOOT!!!!!!!!

/bows

gernblan
10-22-2007, 03:48 AM
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.

PLEASE, can you post this?

It would be invaluable.

TheLieka
10-22-2007, 02:59 PM
*chomps at bit*

Dax

AndMetal
10-22-2007, 05:43 PM
PLEASE, can you post this?

It would be invaluable.

I just created an entry in the Wiki: ConvertProfile (http://www.eqemulator.net/wiki/wikka.php?wakka=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).

Theeper
10-23-2007, 03:00 AM
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.

John Adams
10-23-2007, 03:11 AM
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. ;)

gernblan
10-23-2007, 12:49 PM
I just created an entry in the Wiki: ConvertProfile (http://www.eqemulator.net/wiki/wikka.php?wakka=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).

Excellent work so far!

gernblan
10-23-2007, 12:50 PM
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. ;)

And you too, Sir! :)

GeorgeS
10-23-2007, 06:42 PM
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

AndMetal
10-24-2007, 08:20 AM
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.

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 (http://www.eqemulator.net/wiki/wikka.php?wakka=EQEmuDBSchemacharacterprofile) ), but I'm having some trouble understanding it.

Theeper
10-24-2007, 03:27 PM
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.

GeorgeS
10-25-2007, 02:32 AM
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

GeorgeS
11-04-2007, 12:44 PM
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

TheLieka
11-09-2007, 09:19 AM
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/5717/serverstatserrordv6.png

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

Dax

moydock
11-09-2007, 10:08 AM
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

TheLieka
11-09-2007, 02:45 PM
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

GeorgeS
11-09-2007, 04:41 PM
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

TheLieka
11-10-2007, 10:35 AM
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

GeorgeS
11-10-2007, 12:34 PM
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

Aramid
11-11-2007, 02:19 AM
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.

GeorgeS
11-12-2007, 04:36 PM
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

GeorgeS
11-12-2007, 07:01 PM
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

GeorgeS
11-12-2007, 07:11 PM
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

Aramid
11-12-2007, 10:45 PM
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

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>

GeorgeS
11-13-2007, 03:12 AM
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

Aramid
11-13-2007, 10:18 PM
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!!!

TheLieka
11-14-2007, 07:46 AM
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

Wooot! Downloading it now! I'm giddy!

Dax

John Adams
11-14-2007, 11:26 AM
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.

GeorgeS
11-14-2007, 02:35 PM
Well sorta figured it out last night - but anyway the option is still there to get rid of those unplayed characters. Also, no Blob data may exist which means no race either

GeorgeS

TheLieka
11-18-2007, 08:42 AM
I've been using this tool a lot lately, and I am very impressed. This is yet another great contribution. Thanks again George!

Dax

GeorgeS
11-22-2007, 03:05 AM
I'ts great people like the tool. Since there's no help file, does this program need one, or is it pretty straight fwd to figure it all out?

...the monitoring tool is pretty fun to look at as characters are updating their levels and coin!

GeorgeS

TheLieka
11-24-2007, 05:36 AM
I think it's pretty straight forward, no need for a help file. I know it sounds goofy, but I have gotten a lot of use out of the "reference" sections as well.

Dax

TheLieka
12-02-2007, 08:44 AM
I love this tool George, I use it all the time. How hard would it be to make it possible to view / modify / search the contents of player corpses?

That would be really nice.

Dax

GeorgeS
12-03-2007, 03:13 AM
Yes, I believe that's possible. My player_corpses table is empty, so could you send me a sample (DL link perhaps) sql dump of the player_corpses table I can work with?

..glad you're enjoying the program.

Thanks
George

TheLieka
12-03-2007, 04:32 AM
PMed you a link.

Thanks,
Dax

GeorgeS
12-04-2007, 03:09 AM
Thanks for the table data. I am going to go thru the c++ source today and figure out the Blob struct data in that table.

GeorgeS

fdraven
12-15-2007, 10:02 AM
George when i run the npc edit tool i get runtime error 91 object variable or with block variable not set? Any ideas on how to correct this?
Fdraven

GeorgeS
12-16-2007, 05:06 AM
This is for the NPC editor and not serverstats tool?
Describe exactly how to reproduce this error, otherwise I cannot fix the issue.

GeorgeS

fdraven
12-16-2007, 05:39 AM
I click on the spawn builder and that error pops up, wont let me into the program at all. Other things like the stat side of it tell me there is an error in db.ini, but it opens.
Fdraven

GeorgeS
12-17-2007, 05:13 PM
Ok, I am lost, what tool is this for (need exact name of exe) not sure which tool spawn builder refers to. I have many many tools.

GeorgeS

TheLieka
01-26-2008, 05:39 AM
Any luck on parsing the corpse data?

Sabyre
03-19-2008, 07:13 PM
I get an error trying to run this:

Runtime error -2147467259 (80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What am I doing wrong? I registered the OCX and changed the db.ini settings.

This is on a Win2K box

GeorgeS
03-20-2008, 02:09 AM
You will need the MyODBC drivers from my site or from MySql's
Install and then it should work.

GeorgeS

Sabyre
03-20-2008, 03:18 AM
Yeah, I just figured that out. Thanks.

Got a question, I am trying to edit loot on a_shadowknight's in Befallen. It would seem that with the editor it saves loot depending on the mobs name and not the ID. There are 5 different "a_shadowknights" in Befallen on live and they drop different keys. When I try to modify what they each specifically drop in the editor it saves it to all of the "a_shadowknights" and not each independantly.

There a way around this?

I am trying to get 1 shadowknight to drop splintered wooden key, 2 others to drop charred bone key, and 2 others to drop the last key. Unfortunately I have found it to be all or nothing.

GeorgeS
03-20-2008, 01:46 PM
No, it does not use npc name, but lootable id. All the a_shadowknight use lootable=85 (thus all get same drops)

Solution = create new lootables for each

-best to use my npc and loot editor for this
GeorgeS

Sabyre
03-20-2008, 03:41 PM
Thanks so much, I really appreciate the help. In retrospect I should have been able to figure that out.