[SQL] On/Offline-Char-Overview; Table

Oh well some server tried it out already and said that it's working correctly and fine so I'm gonna "release" it if anyone has searched for such a fancy extra :)

Working one (w/o temp tables, can be directly added to php):

PHP Code:

---

SELECT         
            chart.CharName16 as 'Player', 
            CASE WHEN MAX(elog.EventID) = 4 THEN 'Online' WHEN MAX(elog.EventID) = 6 THEN 'Offline' ELSE '-' END as 'Status',
            CASE 
            WHEN MAX(elog.EventID) = 6 THEN '0'
            WHEN LEN(MAX(elog.EventTime)) > 0 THEN DATEDIFF(MINUTE,cast(MAX(elog.EventTime) as datetime),GETDATE()) ELSE '-' 
            END as 'Minutes',
            MAX(elog.EventTime) as Date
FROM        
            /*#*/SRO_VT_SHARDLOG.dbo._LogEventChar/*#*/ as elog
            JOIN /*#*/SRO_VT_SHARD.dbo._Char/*#*/ as chart on elog.CharID = chart.CharID    
WHERE        
            (elog.EventID = 4 OR elog.EventID = 6)
GROUP BY    chart.CharName16
HAVING        MAX(elog.EventID) = 4
ORDER BY    MAX(elog.EventID) asc 


---

You only have to edit the Database names to your ones within the /*#*/ <-


Old crap, an example of the sql syntax:

Spoiler:

At first create the needed Table at SRO_VT_SHARD through:

PHP Code:

---

USE [SRO_VT_SHARD]
GO
/****** Object:  Table [dbo].[_OnOffline]    Script Date: 02/29/2012 10:05:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_OnOffline](
    [CharID] [int] NULL,
    [CharName16] [varchar](max) NULL,
    [LatestRegion] [int] NULL,
    [PosX] [real] NULL,
    [PosY] [real] NULL,
    [PosZ] [real] NULL,
    [LastLogout] [smalldatetime] NULL,
    [CStatus]    [varchar](10) NULL,
    [RefreshDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF 


---

Afterwards create the procedure under SRO_VT_SHARD..

PHP Code:

---

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_OnOffline_UPDATE]    Script Date: 03/07/2012 17:46:27 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE  PROCEDURE [dbo].[_OnOffline_UPDATE]    
    
as

Declare @IDOn int, @IDOff int;

SET NOCOUNT ON

SELECT 
        CharID,
        CharName16,
        LatestRegion,
        PosX,
        PosY,
        PosZ,
        LastLogout
INTO
        #temp_OnOffline
FROM
        _Char
        
INSERT
INTO _OnOffline
    (
    CharID,CharName16,LatestRegion,PosX,PosY,PosZ,LastLogout,CStatus,RefreshDate
    )
SELECT 
    CharID,CharName16, LatestRegion, PosX,PosY,PosZ,LastLogout,
    CStatus='Offline',
    RefreshDate=GETDATE()
FROM     
    #temp_OnOffline
WHERE 
    not exists (SELECT CharID FROM _OnOffline WHERE CharID = #temp_OnOffline.CharID)



/*SET Online*/

UPDATE _OnOffline 
SET 
LatestRegion        = temp.LatestRegion,
PosX            = temp.PosX,
PosY            = temp.PosY,
PosZ            = temp.PosZ,
LastLogout        = temp.LastLogout,
CStatus            = 'Online',
RefreshDate        = GETDATE()
FROM _OnOffline as onoff WITH (NOLOCK)
JOIN #temp_OnOffline as temp on onoff.CharID = temp.CharID
WHERE        --(temp.LastLogout    = onoff.LastLogout) AND
        (onoff.CStatus        = 'Offline') AND
        (
        (temp.LatestRegion    != onoff.LatestRegion) OR 
        (temp.PosX            != onoff.PosX) OR 
        (temp.PosY            != onoff.PosY) OR
        (temp.PosZ            != onoff.PosZ)
        )    


/*SET Offline*/

UPDATE _OnOffline 
SET 
LatestRegion        = temp.LatestRegion,
PosX            = temp.PosX,
PosY            = temp.PosY,
PosZ            = temp.PosZ,
LastLogout        = temp.LastLogout,
CStatus            = 'Offline',
RefreshDate        = GETDATE()
FROM _OnOffline as onoff WITH (NOLOCK)
JOIN #temp_OnOffline as temp on onoff.CharID = temp.CharID
WHERE         (temp.LastLogout    != onoff.LastLogout) AND
        (onoff.CStatus = 'Online')

DROP TABLE #temp_OnOffline

SET NOCOUNT OFF 


---

So to call the procedure you can use..

PHP Code:

---

exec _OnOffline_UPDATE 


---

manually.


But I recommend you to take either a lil tool/program/batch/sqlcmd/SQL Job or a query like stated above to execute it automatically on your server.

The query-way is definitely not the best one but it naturally works as well.

PHP Code:

---

SET NOCOUNT ON
Declare @int int;
SET @int = 1
while (@int <= 2)
BEGIN
exec _OnOffline_UPDATE
WAITFOR DELAY '00:00:05'
END
SET NOCOUNT OFF 


---

You've got to keep it executed, it'll run on and on till you cancel it (Endless loop), doesn't take really much ressources, an online server with around 800 ppl online executes it like that currently.

You gotta stick to the following bold part to use it correctly,
if the server is currently running and you want to start executing it, use at first UPDATE _OnOffline SET CStatus = 'Offline'.
When the server is offline and all chars are set to Offline already you can naturally start running it w/o setting the CStatus at first to Offline.
When the lil query is executed you shudn't stop it unless you gonna do the stuff stated above.

But naturally feel free to contribute or use your own invention to execute it.

Recommendation: Set your server offline, start the execution of the procedure and start it again (Only once need to make sure that all Players are offline.

BTW you can still execute other queries while having an endless loop running.

At least one possible way to get such a result.

It's proved and already in use at a Server

how it'll look like @ SQL:
http://s7.directupload.net/images/120307/vzlg4diw.jpg





#EDIT: Currently on improving the performance, will change it prospectively tomorrow
#EDIT: Optimized the Procedure

epic as always :D

Epic relase Caipi ;)

now i get a Caipi drink with ice for test it ? :D

greetz Kev

thanks :DD

Quote:

---

Originally Posted by InPanic Kev

Epic relase Caipi ;)

now i get a Caipi drink with ice for test it ? :D

greetz Kev

---

Heh, a Caipi-rinha consists mostly out of ice! ;o But +1 support the Pitú-producers :)

Quote:

---

Originally Posted by Caipi

Heh, a Caipi-rinha consists mostly out of ice! ;o But +1 support the Pitú-producers :)

---

Hey Caipi ... Not sure if you coded this or someone else.

But is it possible to add "logged" ip address to a column.

Not using reg_ip since that is the account creation ip from the web.

I'm looking to log and active list of online users with IP addresses as well. This sql query is awesome but does everything I want except the "active" IP of that character. Could you make it possible ?

Well the problem is that there's no table which actually follows the IP changes of chars - show me a table/.txt file/whatever with the records of the IP's within and ima add it :)

This one was only possible in cause of the real-time changes of _Char, PosX/Y/Z and LastLogout.

whats the Php command to make a cronjob of it?

Creating SQL Server Agent Jobs


EDIT:

#Optimized the Procedure, got rid of the while loop within the SP, extraordinarily decreased the execution time.

If you tried it out before, change CREATE into ALTER and execute the procedure like stated above on your server.

error...

Quote:

---

Msg 208, Level 16, State 6, Procedure _OnOffline_UPDATE, Line 79
Invalid object name 'dbo._OnOffline_UPDATE'.

---

Quote:

---

Originally Posted by asi

error...

---

Ye cause the Stored Procedure wasn't created, I accidentaly parsed the ALTER version in the box above - try it now again and it'll work

thank you.

Quote:

---

Command(s) completed successfully.

---

thanks Caipi really useful

really usefull, but a bit unprecise .. i tested it with several chars on my test server and at first i set all chars to offline like you said, then i logged on to 1 char and it showed online in the table, then i logged off and logged in another char but the table said there wasnt any char online, after that i logged the char off and it showed online in the table?!

whats wrong with that ..

Quote:

---

Originally Posted by lemoniscool

really usefull, but a bit unprecise .. i tested it with several chars on my test server and at first i set all chars to offline like you said, then i logged on to 1 char and it showed online in the table, then i logged off and logged in another char but the table said there wasnt any char online, after that i logged the char off and it showed online in the table?!

whats wrong with that ..

---

I assume the 5 sec waiting thingy is the main problem - cause actually in theory everything should work fine.. ^^ Add me at Skype gonna try to make that thing work properly - unless there's another way to track online characters.

Quote:

---

Originally Posted by Caipi

I assume the 5 sec waiting thingy is the main problem - cause actually in theory everything should work fine.. ^^ Add me at Skype gonna try to make that thing work properly - unless there's another way to track online characters.

---

Yeah there is.
PuritySRO - Online Users

Take a look at the log database, its pretty easy, it also includes the ip ^^

Quote:

---

Originally Posted by Nubwaffle

Yeah there is.
PuritySRO - Online Users

Take a look at the log database, its pretty easy, it also includes the ip ^^

---

Hm well I dun have a running server on my laptop only the basic DB's with some data within - w/o logs actually.

But the pury thingy site.. Shows e.g. 390 entries on a total amount of 7 sites altough the counter is on PuritySRO: 446 / 1000 ^^

Quote:

---

Originally Posted by Caipi

Hm well I dun have a running server on my laptop only the basic DB's with some data within - w/o logs actually.

But the pury thingy site.. Shows e.g. 390 entries on a total amount of 7 sites altough the counter is on PuritySRO: 446 / 1000 ^^

---

I made each page list 62 characters, so 62 * 7 -> 434
EDIT: Nvm, i see it, page 2 is missing, but in the database everything is right anyways ^^
Let me fix it..

Quote:

---

Originally Posted by Nubwaffle

I made each page list 62 characters, so 62 * 7 -> 434
EDIT: Nvm, i see it, page 2 is missing, but in the database everything is right anyways ^^
Let me fix it..

---

Sure thing :)

Ah ye I see, _LogEventChar, Event ID 4 = Login, 6 = Logout as it's stated in the procedure. Well that's truly a way too easy ^^

Quote:

---

Originally Posted by Caipi

Sure thing :)

Ah ye I see, _LogEventChar, Event ID 4 = Login, 6 = Logout as it's stated in the procedure. Well that's truly a way too easy ^^

---

Yeah, and for the ip, use this:

Quote:

---

SRO_VT_ACCOUNT.DBO.split_ip(@Data2)

---

Quote:

---

Originally Posted by Nubwaffle

Yeah, and for the ip, use this:

---

jup I assumed that already :) But well I'm actually not the one who needs that Online stuff xD


EDIT: In case anyone wonders where the split_ip thingy is located in, it's ->
"SRO_VT_ACCOUNT\Programmability\Functions\Scalar-values Functions"

Quote:

---

Originally Posted by Caipi

jup I assumed that already :) But well I'm actually not the one who needs that Online stuff xD

---

Atleast some people can use this.
I fixed the online page :)

The Log DB is truly quite kewl.. check this out:

PHP Code:

---

USE SRO_VT_SHARD
BEGIN
    SELECT TOP 100 
        chart.CharName16 as Player, 
        elog.ItemRefID as ItemID, 
        ref.CodeName128 as ItemCode,
        CASE WHEN CHARINDEX('Opt',elog.strDesc) != 0 THEN SUBSTRING(elog.strDesc,(PATINDEX('%+%',elog.strDesc)),2) ELSE '+0' END as '+Amount',
        SUBSTRING(elog.strDesc,CHARINDEX('MOB',elog.strDesc),(CHARINDEX(',',elog.strDesc, CHARINDEX('MOB',elog.strDesc)))-CHARINDEX('MOB',elog.strDesc)) as Monster,
        CASE
            WHEN chart.CharName16 collate SQL_Latin1_General_CP1_CI_AS like '%'+(SUBSTRING(elog.strDesc,CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1,(CHARINDEX(']',elog.strDesc, CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1))-CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))-1))+'%'
                THEN '-'
            WHEN CHARINDEX('Var',elog.strDesc) != 0
                THEN UPPER(SUBSTRING(elog.strDesc,CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1,(CHARINDEX(']',elog.strDesc, CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1))-CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))-1))
            ELSE '-' 
        END as Info,
        elog.EventTime as 'Date'
    FROM _Items as items
        JOIN Log_DB.dbo._LogEventItem as elog on items.Serial64 = elog.Serial64
        JOIN _Char as chart on elog.CharID = chart.CharID
        JOIN _RefObjCommon as ref on elog.ItemRefID = ref.ID
    WHERE 
        elog.strDesc like '%MOB%' 
        AND elog.strDesc not like '%NPC%' 
        AND ref.CodeName128 not like '%ARCHEMY%'
        AND chart.CharName16 not like '%[GM]%'
    ORDER BY elog.EventTime desc
END 


---

EDIT:

Working Online Status thingy also to add it to your homepages..

PHP Code:

---

SELECT         
            chart.CharName16 as 'Player', 
            CASE WHEN MAX(elog.EventID) = 4 THEN 'Online' WHEN MAX(elog.EventID) = 6 THEN 'Offline' ELSE '-' END as 'Status',
            CASE 
            WHEN MAX(elog.EventID) = 6 THEN '0'
            WHEN LEN(MAX(elog.EventTime)) > 0 THEN DATEDIFF(MINUTE,cast(MAX(elog.EventTime) as datetime),GETDATE()) ELSE '-' 
            END as 'Minutes',
            MAX(elog.EventTime) as Date
FROM        
            /*#*/SRO_VT_SHARDLOG.dbo._LogEventChar/*#*/ as elog
            JOIN /*#*/SRO_VT_SHARD.dbo._Char/*#*/ as chart on elog.CharID = chart.CharID    
WHERE        
            (elog.EventID = 4 OR elog.EventID = 6)
GROUP BY    chart.CharName16
HAVING        MAX(elog.EventID) = 4
ORDER BY    MAX(elog.EventID) asc 


---

Gonna edit it as well on the 1st post :)

Thanks Caipi , I was waiting for this release a long time ago :)

Awesome , no words

Keep up the great work :)

dmn, im really bad at sql/php, cant make this work.
loged one char, run the query on sql server and nothing.. no char online