EQExtractor2
 

I've been tooling around with Visual C# 2010 the past couple of days and one thing led to another and I wrote an EQ Packet decoder and extended it to dump out SQL statements for Doors and NPC Spawns.

I collected data for the revamped highholdpass and freeport west zones and thought I'd post the SQL for anyone interested:

<Link removed. This data is now incorporated into the PEQ database>

Note that this is just doors and static spawns, no grids or anything, and the mobs will spawn wherever they happened to be when I collected the data. It's all basic stuff, but I thought I'd throw it out there.

Although this seemed to work fine, in West Freeport I could really notice that some NPC's were cut off beyond a certain radius. NPC's from out by commonlands and the temple were non-existent, as well as some other NPC's in the outer perimeter.

Are you in the middle of developing this packet collector further hence just the SQL files?

Thanks for this Derision.

It would help a lot if you could give me a #loc where you are standing, so I can go on live and compare the mobs I collected with what are on live and see what are missing.

It maybe that Live only sends mobs in a certain radius of the player, so I need to run around the zone to get them all.

This is my first C# program of any size, and certainly isn't anywhere near release quality (hard coded file names, very fragile etc.). I just thought it was cool to see spawns in those
two revamped zones which is why I posted the .SQL for them.

Aye I was just wondering where you were on this because I could have alot of fun with it.

And yes, I am pretty positive for lag reasons there is a certain radius that you cache info wise. But from what it looked like in freeport is that you packet collected from the center. Basically every mob outside the same radius is missing. I would just run your query to check it out, I pulled it up on a MQ2 map read to see all the mob positioning. And yes, I only use MQ for map purposes.

Yeah, on live they implemented an anti-MQ2Map feature a while back where they now only send data on NPCs within a certain radius.

Well, that is only halfway true. It does help cut down the bandwidth usage and the usefulness of ShowEQ and MQ2 Maps. But, they do still send zone-wide spawns, they just don't send position updates after that point, and maybe not even new spawns, I am not sure. But, if you zone into Blightfire for example, you will get spawns for the entire zone on ShowEQ, so I am pretty sure it still sends all of them on the initial zone in.

I had a look at freeportwest with MQ2Map and also moved around the zone and I didn't see any obvious missing spawns.

As Trevius says, Live seems to send all the spawns when you zone in, then for mobs within a certain radius, you get rapid position updates for them (with deltas),
and for mobs outside that radius, you get less frequent updates.

I've extended my collector to create grids. Here is an updated .SQL for freeportwest with grids:

<Link removed. This data is now incorporated into the PEQ database>

You will see that for mobs inside the city, near to where I was, e.g the patrolling knights and guards, there are lots of grid entries, because the position updates come every couple
of seconds, whereas for the mobs outside the gates (snakes, bats and the like), there are considerably fewer updates.

Automatic grids created from packet collects are never going to be much good for that reason, although it does give the world builder a starting point I suppose.

Anyway, I guess the next step is for me to get the tool to the point where it is ready for release as an executable at least, so other people can get to play with it.
Probably have time to do that next weekend.

I should add that while in theory it should be possible to have it record a live EQ stream as you are playing, the way I have it setup is to use capture files collected with Wireshark.
So you go to char select, start Wireshark capturing, zone in with a character, come back 20 minutes later stop the capture, save the captured packets and run the extractor
on the .pcap file.

This version of highpasshold would be good to replace the old highpass for the SoF/SoD that currently has problems with it. I tried replacing it with the new version in PEQ database. Only problem I am having is the zone point to zoning out of HighPassHold. I can zone in from Kithicor, Keep and East Karana but I can not get the zone out to work.

Derision, would you please please please packet collect the doors/objects for Oldhighpass? Could that be doable? Or are you getting closer to a working version of this for others?

Is oldhighpass accessable on Live ? It seems like it should be part of Seeds Of Destruction, but I don't see it listed here: http://everquest.allakhazam.com/db/z...of+Destruction

Anyway, assuming it is a SoD zone and it has pre-req tasks for entrance, I don't have any way of getting in there.

I'll try and put a version of my tool out some time this weekend.

I had a feeling it wasn't either, I was about to check myself 8 /

I've uploaded the executable here: http://code.google.com/p/projecteqem...2.rar&can=2&q=

Prereqs: .NET Framework 4, a Live EQ account and Wireshark to capture the .pcap files that this program processes.

When capturing with Wireshark, ALWAYS start the capture with your character at char select, then zone-in once the capture has started.

The program will only process data for a single zone, so if you move to another zone, only the data for the first zone you entered after starting the capture will be processed.

This is still very much a work-in-progress, but feel free to report any bugs or suggestions here.

Tried it out on moors, works perfectly! You can see the end results in the next PEQ database :)

Haha, I tried it out on the same zone and yeah, it worked pretty awesomely. I have a few pcaps saved from random collects here and there and figure I might as well use those through this tool first, then will probably start collecting more zones later. Excellent work as always, Derision :D I can't wait to play with this a bit more. It will definitely save me a ton of time on doors and ground spawns at the very least.

Some other info that might be good to pull would be zone points and maybe even newzone for zone settings, since most of the new zones are just copy/paste of a single example in the zone table. Now that the zone table can have multiple entries for the same zone, I think we could just add an id field as the primary key.

There are 655 npc's in Dragonscale Hills. Simply amazing Derision. Wow.

I've done about 20 live zones so far at about 1-2 minute captures. I will share them when I get a decent collection.


One small suggestion: Having the option to add version type in the SQL create so you can capture and use those captures for different versions (Instances) for everything spawn2/objects/doors

Other than that this is phenominal.

Version 1.2 is up: http://code.google.com/p/projecteqem...2.rar&can=2&q=

Changes:

Version field in the UI to populate the version field of any tables that have a version column.

SQL to update the zone table from values in the OP_NewZone packet, as well as to insert zone_points is automatically generated at the top of the output file, but left commented out.

The 'findable' column in npc_types is now set correctly.

Looking forward to your Dragonscale Hills .SQL, Akkadius ... never been in that zone on live, so will be interesting to see it populated :)

Awesome work, Derision! You never cease to amaze me with all of the quality work you put out. We're lucky to have you!

I can't wait until I get home from work and can start collecting! All I need to do is install WireShark and then your application to get started?

The extractor app is written using C# 2010 Express and needs .NET Framework version 4 which you can get from http://msdn.microsoft.com/en-us/netf...k/default.aspx (Click on the Download .NET 4 link on that page).

I've populated about 20-25 zones from SoF to SoD. I will be more than glad to post them here. And I will continue to keep on populating.

Did you verify them in-game too? :) Anxious to see this in action!

Found a problem, spawngroup is using the npcid for its id instead of the one you specify. The funny thing is, spawnentry is using the proper spawngroupID (meaning, the one you specify, and not the npcid.) In my testing, all other IDs look to be OK.

Just a friendly word of warning, if you are building zones against the PEQ database, the program defaults often conflict with existing data, which is destroyed when you source in the output.

Nevertheless, this is the most exciting thing to come out in a long time for EQEmu in my mind.

Yes I did verify this, I've dumped all of them. Beauty of the program is too is that you can always recapture and redump.

I fully agree. I believe we should get together a collection of dumps and keep them posted somewhere.

Derision, I am very thankful of your contributions to this community. Likewise every other developer.

Another suggestion: Incrementing the ID's by adding a hundred or something for versions. For example, Zone 6 (Highkeep) Version 1, instead of 6000 for all the increments, have it be 6100 or something of that nature. Another thing, when you export another version, the door ID's need to increment differently, otherwise you are going to get conflicting doors. Version 1 needs to change the door ID increments to something like 1000 cause I know there are some zones with a couple hundred doors.

What would be your suggestion in order to keep everything compatible and to be able to submit it to PEQ?

Also, this just gets doors, zone entries, NPC entries in-game. What about adding in special attacks, spells, loot, etc? What's the easiest way to help with this?

He did say it was just an early version, I am sure he is going to add quite a bit more to it.

EDIT: For those who want it, here are some zones that I collected, not all of them because I did some for personal.

I wouldn't say this is PEQ standard, but doors are all there, spawns, pathing for about 1-4 minutes. Merchant lists as much as possible.

http://www.sendspace.com/file/ivch22

Oh, I understand that :) I was talking more procedurally, for submitting to PEQ. Would I need to hunt through my year+ of eqlog files and search for NPC names to find any spells they used and manually add them? Just wondering how this works?

Hopefully that is fixed in v1.3 (just uploaded it).

There's got to be a way we could manipulate as script to pull raw loot tables from Lucy. And modify from there.

Anyone able to get the rest or more of the sod zones? Would really love to see more sod stuff in eqemu.

Working on them

Gaeorn is working on that, actually. He did merchant lists, though there is a major bug with the output I need to talk to him about before the sql released.

Yep, Derision that fixed it, I can now build! I did find one more tiny problem, though. You'll need to add a doorid field next to version. The doorid currently always starts at 1, and that will conflict if the zone already has doors in another version.

To merge into PEQ is pretty tough, because for everything except for NPCs, we use the next available ID. If you feel comfortable, you can send any pcap logs you gather to me at npcswithoutfaces@gmail.com. Otherwise, I don't know a way for multiple people to successfully merge into PEQ, especially since our IDs are going to change QUICK now. SQLs won't help much in this case because the IDs will almost certainly conflict.

A loot table script would be phenomenal. I wouldn't mind helping with this.

I did wonder if I could set variables for the starting Insert IDs at the start of the generated SQL and just reference them with increments afterwards, so you could just change
a few variables at the start to the next free IDs. I didn't know if I could do that in SQL, but I just tested it with select statements, and it seems to be possible:

Output:
So maybe that is the way to go.

Yes! I think you got it! It would mean everybody would need to re-dump their SQLs to share, but it would also mean that with a couple changes all those files will be universal over any database.

How would it be if I removed all the INSERT ID fields from the Extractor UI, so you didn't have to worry about them when generating the SQL, and could just go in afterwards
and set them as required, i.e. I would put a template at the start of the generated SQL like:

I.e. you would just have to press the Load .pcap button, select the .pcap file, alter the check boxes if you wanted, not have to worry about IDs at that point, and then just click 'Generate SQL'.

That seems ideal to me. That way it's compatable with any database.

As for loot drops and everything, I found that Magelo is a better resource than Lucy. I don't know why or how, but it is. I think Magelo uses the same ID in items too.

edit: yeah it does. http://eq.magelo.com/item/1001

I prefer having the fields there, but a simple toggle box to toggle them enabled/disabled would work well. That way, people could toggle them off when collecting for PEQ or leave them on if collecting for their own DB. A simple toggle would make it nice and quick to generate one of each type of you wanted. I think it is nice to be able to see the actual range of numbers that the SQL is planning to use so it can be easily compared against the DB to make sure it will be ok.

I agree with Trevius. Although I would personally disable the IDs in the program, I think it should still be an option for others.

I don't know the details of how the tool works exactly, but it seems like it parses the .pcap when you first load it. Perhaps you could just give an option to generate a file of that parse, which could then be loaded and used to generate SQL from. Figured that may be another possible option for creating files anyone could use and share without worrying about private info. Another nice bonus to using a parse instead of some generated SQL is that a parse should be able to hold data that may not currently be set to be generated into SQL yet, so as the extractor tool is refined/expanded, those same parses could be ran through the tool again for an instant update with the latest features. This should prevent people from having to run their own .pcaps through the tool and sending them again. Instead, Cavedude could just run the parses through the tool, and have the latest with minimal work.