Many useful query for Admin

[levinthan9]

Just the code you want in Query Analyzer

Delete no member Guild ( guild have 1 GM only ) :

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where EXISTS (
SELECT m.G_Name,count(*) as memb from GuildMember m
where guild.g_name=m.G_name
group by m.G_Name
having count(*) <2
)

Delete guildmember with no guild ( when you edited wrong then make bug )

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guildmember
where NOT EXISTS (
SELECT * from Guild
where guild.g_name=guildmember.G_name
)

Top 10 Guild ( as members )

SELECT top 10 g.g_name,count(all m.g_name)as thanhvien from Guild as g join Guildmember as m
on g.g_name like m.g_name
group by g.g_Name
having count(*) > 20
order by count(*) DESC

Top 5 guild ( as resets all members in guild )

select top 5 g.g_name,sum(c.resets) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc

Top 5 online time

select top 10 c.name,resets,s.onlinehours from character c full join memb_stat s
on c.accountid=s.memb___id
where (c.accountid in ( select top 10 memb___id from memb_stat
order by onlinehours desc ))
and (c.name in ( select top 1 c.name from character c where c.accountid=s.memb___id order by resets desc))
order by onlinehours desc

Top average resets (per player in guild ).Edit 10 to value you want . That's value of min member of a guild.

select top 5 g.g_name,sum(c.resets)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc

rename account

select * from accountcharacter
where id='9242085'

update accountcharacter
set id='ha1710'
where id='9242085'

select * from accountcharacter
where id='ha1710'
-----------------------
select * from character
where accountid='9242085'

update character
set accountid='ha1710'
where accountid='9242085'

select * from character
where accountid='ha1710'
----------------------
select * from memb_info
where memb___id='9242085'

update memb_info
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_info
where memb___id='ha1710'
----------------------
select * from memb_stat
where memb___id='9242085'

update memb_stat
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_stat
where memb___id='ha1710'
-----------------------
select * from warehouse
where accountid='9242085'

update warehouse
set accountid='ha1710'
where accountid='9242085'

select * from warehouse
where accountid='ha1710'

delete account with nomore connect from 2007-06-01

delete from memb_info
where memb___id in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from accountcharacter
where id in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from character
where accountid in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from warehouse
where accountid in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete account that no connect ( blank account ) or longtime no connect

delete from memb_info
where memb___id not in (
select memb___id from memb_stat)

delete from accountcharacter
where id not in (
select memb___id from memb_stat)

delete from character
where accountid not in (
select memb___id from memb_stat)

delete from warehouse
where accountid not in (
select memb___id from memb_stat)

Character not in account :

select accountid,character.name from character where accountid in (
select accountid from character
where name not in (select gameid1 from accountcharacter where id=accountid )
and name not in (select gameid2 from accountcharacter where id=accountid )
and name not in (select gameid3 from accountcharacter where id=accountid )
and name not in (select gameid4 from accountcharacter where id=accountid )
and name not in (select gameid5 from accountcharacter where id=accountid )
)

delete guild , guildmember that wrong :

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where g_master not in ( select name from character )
delete from guildmember
where name not in ( select name from character )
delete from guildmember
where G_Name not in ( select G_name from guild )

back up server :

--trunkating
BACKUP LOG muonline WITH NO_LOG

USE [Muonline]
GO
--shrinking
DBCC SHRINKDATABASE(N'Muonline')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\muonline_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+
Convert( varchar , datepart( month , Getdate() ) ) + '-'+
Convert( varchar, datepart( day , Getdate() ) ) + '.bak'
--saving backup with name and date.
BACKUP DATABASE [Muonline] TO DISK = @dateBackup WITH NOFORMAT, NOINIT, NAME = N'Muonline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Sorry my bad English . If you think my guide useful , please take a look here to help my problem . Im sadly with it :

I own 1 Muonline server . Last 2 week, my server has been flood . I dont know how the hacker attacked my server . This is a part of joinserver when being attacked :
Quote:
11:48:14a [dasxv] (222.253.205.187) User Login
RecvQ Count : 279
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 276
RecvQ Count : 275
11:48:18a [flowea] (222.253.205.187) User Login
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 275
RecvQ Count : 274
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:22a [contnue] (222.254.31.212) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:23a [Pizza] (192.168.1.10) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
RecvQ Count : 269
RecvQ Count : 268
RecvQ Count : 267
RecvQ Count : 266
11:48:24a [henrr1972] (123.22.113.137) User Login
RecvQ Count : 267
RecvQ Count : 266
RecvQ Count : 265
RecvQ Count : 264
[SubserverVIPR1][192.168.1.10:55903] GameServer Connect
RecvQ Count : 263
RecvQ Count : 262
RecvQ Count : 261
11:48:25a [trav103] (118.68.176.71) User Login
RecvQ Count : 263
As you see , when being flooded , join server notice much error " RecvQ Count" . At this time , all players playing get lag and dis and cant come back into game , just click on subserver bar and nothing happen ( i think because joinserver cant process at this time ) . Then i start to find solutions. I see that when turn off apache server , the flood stop immediately. So this's the first i got . Tried to change another web : mutoolz , muweb 0.8 , ... but all fail . still being flooded . Then i upgrade apacheserver to lasted version included lasted php , change webserver port ,mod_security2, mod_dosevasive for apache server, login box with .htaccess, setup security firewall ( comodo firewall pro , norton , kis , outpost firewall pro ,...) , block all port ( just open 44405 , 55901 , 55903 ) , setup ISA server to mornitor tcp-ucp connect , request , use all anti flood function ( limit TCP-UDP connection , http request per second ) but ALL FAIL . They nonstop flood my server from day to day ( except when i turn off my webserver ) . I tried search google about that but no result , i tried ask many people to help but they dont know what's my Muonline Joinserver :( . I know that my problem not really in Muonline Server Help . But i dont know what should i do now , where can i find help now . All my players going to crazy now , and me too .
If you got this problem too or know what i should do please help . Dont ask me to search anymore . I tried " recvq count" , "recvq" , "muonline recvq count" in google search but no results.
Thanks for reading .

[MentaL]

[kaspule]

thanks, very useful

[Pinoyrealm]

This guide is very usefull...

Thank you

[SETcover]

Nice ...

[G@LACT!CO]

try to make your own web page, simple and nice, but eficient...no install folder etc...directly..with no password protected etc..and user..cas that mean someone can acces it...if dosen't have...normal php like a banal html with a image in the center..noone can fload nothing at it...cas dosen't have acces...only you in the base files...i used to muweb and mutoolz, and got same problem...not flood.but people blast etc..and i maded my own web...clasic..normal and nice..but eficient...try too.

[didi3d]

the top 5 online don

[Jumeirah]

Very nice, these scripts are very good !

[G@LACT!CO]

[quote=didi3d;3283678]the top 5 online don

[WAIF]

tnx for the info..,..

[Goru]

Nice, script`s are good !

[Boossik]

Thanks!

[Boossik]

I need modificy this query:

Top 5 guild ( as resets all members in guild )

Quote:
select top 5 g.g_name,sum(c.resets) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc



I need this query modificy. I need create medium level. This look that: select all guildmembers(resets) and delete on player count.... 180 resets / delete on 30 players = medium level 6



Someone can create that query? :)

[levinthan9]

could your explain more please . i dont really understand your mean,

[Boossik]

I am necessary to calculate the middle level of guild. That common amount of players which in a guild divided by the amount of resets.
For example: 150 resets / we divide by 30 players = we get the middle level of guild = 50 resets

[levinthan9]

Wait a little . im busy now . i will give you later

[Boossik]

thanks, i w8 your :)

[levinthan9]

Here you got . Test it and reply results ( i didnt test )

select top 5 g.g_name,sum(c.resets)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc

Edit 10 to value you want . That's value of min member of a guild.

[vailoelle]

Somebody may to make me a reset scrip.To make res from 300-350 lv. and give point 300lv=301 points, 302lv=302 point.......350lv=350 point

and no AutoReset if somebody want :juggle::poster_ss I stop here

if(character level>300 AND character level<350) {
update [level up points] = 'character level',cLeve='1',Exp='0'
....
}

:sq_yellow

Help :busted_co

[levinthan9]

Somebody may to make me a reset scrip.To make res from 300-350 lv. and give point 300lv=301 points, 302lv=302 point.......350lv=350 point

and no AutoReset if somebody want :juggle::poster_ss I stop here

:sq_yellow

Help :busted_co

Sorry but i cant understand what you want. You want a query to run in Query Analyzer or a query to add in your autoreset script ?

[vailoelle]

Sorry but i cant understand what you want. You want a query to run in Query Analyzer or a query to add in your autoreset script ?

I want heroes in my server to may a make res in lv 300-350 and give point 300-350 ( but res to make in web NOT AutoRes).Whow to make this :sq_yellow

Sorry again for my English.

[levinthan9]

What web you are using ?

[vailoelle]

What web you are using ?

MuWeb 0.8.... if need I change web.

[levinthan9]

edit character.class.php . function reset($charactername)


this's 1 example that im using:

function reset($charactername)
{
          if ((isset($_SESSION['pass'])) && (isset($_SESSION['user']))); 
                     {
                           require("config.php");
                           $loginid = "$_SESSION[user]";


                           $online_check = $db->Execute("SELECT ConnectStat FROM MEMB_STAT WHERE memb___id=?",array($loginid));
                           $row2 = $online_check->fetchrow();

                           $result = $db->Execute("Select Clevel,Resets,Money,LevelUpPoint,class From Character where Name=?",array($charactername));
                           $row = $result->fetchrow();


                            $resetup=$row[1] + (1);
                            
                            if (($row[1]>=0) and ($row[1]<20))
                            {$resetlvlmustbe=100;
                             $resetmoeny=$row[2]-2000000;
                             $resetpt=200 * $resetup;                                                 
                            }
                            elseif (($row[1]>=20) and ($row[1]<40))
                            {$resetlvlmustbe=200;
                             $resetmoeny=$row[2]-5000000;
                             $resetpt=200 * $resetup;    
                                                         
                            }

$moneyreset=$row[2]-$resetmoeny;

                            if ($row2[0] != 0){ $error=1;
                                         show_error("Logout of game man"); 
                                                  }
                            elseif ($resetmoeny < 0){ $error=1;
                                         show_error("oh man . you must have  $moneyreset Zen to Reset!"); 
                                                    }

                            elseif ($row[0] < $resetlvlmustbe){ $error=1;
                                         show_error("your level must be  $resetlvlmustbe to reset! "); 
                                                                   }
                            if($error!=1){

                                         $sql_reset_script="Update character set [strength]='25',[dexterity]='25',[vitality]='25',[energy]='25',[leadership]='25',[MapNumber]='0',[MapPosX]='123',[MapPosY]='123',[clevel]='1',[experience]='0',[money]='$resetmoeny',[LevelUpPoint]='$resetpt',[resets]='$resetup' where name=?";
                                         $sql_reset_script2="UPDATE character Set [inventory]=CONVERT(varbinary(1080), null),[magiclist]= CONVERT(varbinary(180), null) Where name=?";


                                    $sql_reset_exec = $db->Execute($sql_reset_script,array($charactername));
                                    $sql_reset_exec2 = $db->Execute($sql_reset_script2,array($charactername)); 

[vailoelle]

edit character.class.php . function reset($charactername)


this's 1 example that im using:

Hi thanks for help , but don't work.Res dont work 1 pic.
2 pic only heroes "Lord" may to make res he is 350 lv another "Low level to reset".
I replace function reset($charactername) with you code (3 pic)

[levinthan9]

Wrong . You muse add my code in function reset($charactername) not function add_stats.
That's main code.

Inorder to make it work, you must edit your characters_manager.php file in inc folder.

Disable this things

//$resetmoeny=$info_result[4]-($muweb['resetmoney']);
//if($resetmoeny < 0){$reset_="$reset_no_zen";}
//elseif($info_result[2] < $muweb['resetlevel']){$reset_="$reset_no_level";}
//elseif($info_result[2] >= $muweb['resetlevel'])

Only let this :

$reset_="$reset_allow";