Go Back   EQEmulator Home > EQEmulator Forums > Development > Development::Tools

Development::Tools 3rd Party Tools for EQEMu (DB management tools, front ends, etc...)

Reply
 
Thread Tools Display Modes
  #1  
Old 10-28-2019, 09:54 PM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default A Few Queries for Matching Spells to Mobs...

I was trouble-shooting a server crash and wanted to see if I could tie it to a specific spell or spell effect being used..so, I wrote a few queries.


Find list of spell effects used by npcs in a given zone (currently, filtered by `goodEffect`):
Code:
SELECT @zone_short := 'dranik';
SELECT @good_effect := '0';

SELECT `a`.`effectid1` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid2` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid3` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid4` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid5` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid6` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid7` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid8` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid9` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid10` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid11` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
UNION
SELECT `a`.`effectid12` `effect_id` FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
ORDER BY `effect_id`;

Find list of spells that use a given effect within a zone (currently, filtered by `goodEffect`):
Code:
SELECT @zone_short := 'dranik';
SELECT @good_effect := '0';
SELECT @effect_id := '340';

SELECT `a`.* FROM `spells_new` `a`
INNER JOIN `npc_spells_entries` `b` ON `b`.`spellid` = `a`.`id`
INNER JOIN `npc_spells` `c` ON `c`.`id` = `b`.`npc_spells_id`
INNER JOIN  `npc_types` `d` ON `d`.`npc_spells_id` = `c`.`id`
INNER JOIN `spawnentry` `e` ON `e`.`npcID` = `d`.`id`
INNER JOIN `spawn2` `f` ON `f`.`spawngroupID` = `e`.`spawngroupID` AND `f`.`zone` LIKE @zone_short
WHERE `a`.`goodEffect` = @good_effect
AND @effect_id IN
(`a`.`effectid1`,`a`.`effectid2`,`a`.`effectid3`,`a`.`effectid4`,`a`.`effectid5`,`a`.`effectid6`,`a`.`effectid7`,`a`.`effectid8`,`a`.`effectid9`,`a`.`effectid10`,`a`.`effectid11`,`a`.`effectid12`)
GROUP BY `a`.`id`;

Find list of npcs that cast a given spell within a zone:
Code:
SELECT @zone_short := 'dranik';
SELECT @spell_id := '11839';

SELECT `a`.* FROM `npc_types` `a`
INNER JOIN `spawnentry` `b` ON `b`.`npcID` = `a`.`id`
INNER JOIN `spawn2` `c` ON `c`.`spawngroupID` = `b`.`spawngroupID` AND `c`.`zone` LIKE @zone_short
INNER JOIN `npc_spells` `d` ON `d`.`id` = `a`.`npc_spells_id`
INNER JOIN `npc_spells_entries` `e` ON `e`.`npc_spells_id` = `d`.`id` AND `e`.`spellid` = @spell_id;

Each query feeds the next one..but, the criteria will have to be manually updated.
__________________
Uleat of Bertoxxulous

Compilin' Dirty

Last edited by Uleat; 10-28-2019 at 10:23 PM.. Reason: tweaked effect_id query
Reply With Quote
  #2  
Old 10-28-2019, 10:05 PM
chrsschb's Avatar
chrsschb
Dragon
 
Join Date: Nov 2008
Location: GA
Posts: 905
Default

Quote:
I was trouble-shooting a server crash and wanted to see if I could tie it to a specific spell or spell effect being used..so, I wrote a few queries.
So did you find any?
__________________
Clumsy's World: Resurgence
Clumsy's World [2006-2012]
Reply With Quote
  #3  
Old 10-29-2019, 01:32 AM
Uleat's Avatar
Uleat
Developer
 
Join Date: Apr 2012
Location: North Carolina
Posts: 2,815
Default

Still looking


Had a crash fighting at the same point in dranik that I had a couple of years ago - same mob was involved.

Just not sure it was him that caused it...
__________________
Uleat of Bertoxxulous

Compilin' Dirty
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 03:30 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3