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

Cud it be that you "disabled" the Log Tables - means there are no records added at your table.

Damn I wasn't online for days/weeks (doesn't remember right) what the heck did they do with RageZone ;o it looks totally messy - the last layout was pretty much better actually ^^

adding SS of my log, everytime i run the query got nothing as result

and yes, new layout feels "weird", but is not big deal

Well lets do it like that - look at the latest record in the table (datetime), afterwards log into your char, then have a look at the table again if there's a new record with the EventID 4 (= Login, 6=Logout)
+ post your _AddCharEvent procedure into the thread here or through PM or add me add Skype w/e, it's your choice - but I gotta mention that e.g. GlorySRO just demonstrated yesterday again that it works ^^

Ty ,,,,,

i re test it later.

i bet my sql is screwed.... idk why cant even easy things like convert int to ip dont works too... result is always 1 Y_Y

oh well. i try it later and post the result

Quote:

---

Originally Posted by Caosfox

i re test it later.

i bet my sql is screwed.... idk why cant even easy things like convert int to ip dont works too... result is always 1 Y_Y

oh well. i try it later and post the result

---

PHP Code:

---

Declare @SDynIP varchar(max),

@DynWIP int = -1815775300; -- The Data2 column value (mostly only EventID 4 and 6 contain the IP)

exec @SDynIP = SRO_VT_ACCOUNT.dbo.split_ip @DynWIP;

SELECT @SDynIP 


---

e.g. -1815775300 --> 188.119.197.147

holly cow, ip think worked perfect ^^. thx

Quote:

---

Originally Posted by Caipi

Well lets do it like that - look at the latest record in the table (datetime), afterwards log into your char, then have a look at the table again if there's a new record with the EventID 4 (= Login, 6=Logout)
+ post your _AddCharEvent procedure into the thread here or through PM or add me add Skype w/e, it's your choice - but I gotta mention that e.g. GlorySRO just demonstrated yesterday again that it works ^^

---

cant find on my db _addcharevent , the only one i find writing to logeventchar table is :

PHP Code:

---

ALTER   procedure _AddLogChar 
@CharID  int,
@EventID  tinyint,
@Data1  int,
@Data2  int,
@strPos  varchar(64),
@Desc  varchar(128)
as
 declare @len_pos  int
 declare @len_desc int
 set @len_pos = len(@strPos)
 set @len_desc = len(@Desc)
 if (@len_pos > 0 and @len_desc > 0)
 begin 
  insert _LogEventChar values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos, @Desc) 
 end
 else if (@len_pos > 0 and @len_desc = 0)
 begin  
  insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @strPos)
 end
 else if (@len_pos = 0 and @len_desc > 0)
 begin  
  insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values(@CharID, GetDate(), @EventID, @Data1, @Data2, @Desc)
 end
 else
 begin
  insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values(@CharID, GetDate(), @EventID, @Data1, @Data2)
 end 


---

btw, to test it, im running it from as querry on sql manager, not from php. maybe that is the problem?

Sry, didn't look the correct name up, it's _AddLogChar ^^

It's supposed to work in SQL directly as well

Dont worry man, you helping a lot

give me 1-2 days (im making some servers for my friends) and im going to recheck it

Quote:

---

Originally Posted by Caipi

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 


---

---

This one gives me an error on execution after i altered the DB names: "Wrong Length Parameter passed to the Substring Function" while using MSSQL 2005, with MSSQL 2008 it seems to be working fine!

Quote:

---

Originally Posted by Caipi

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 


---

---

This one shows onlin eusers, but only 25~30 even tho there are more online .. the last time i tried it there were 60 ppl online but it showed only 25 users..

Seems to be working properly on SQL '08, apparently SQL '05 has some issues with it ;x

Put your faith in the light.

Quote:

---

Originally Posted by Caipi

Seems to be working properly on SQL '08, apparently SQL '05 has some issues with it ;x

---

Not working in SQL '08 R2.
Any ideas ? It shows only 20-40 users when there are 700 + online.

thank u