[SQL] LogDB, 100 last Drops + Details

[Caipi]

To include it to your e.g. homepage if you've got the SHARDLOG DB fully running :)

USE /*#*/SRO_VT_SHARD_INIT/*#*/
BEGIN
    SELECT TOP 100 
        chart.CharName16 as Player, 
        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 

Change only the DB names within /*#*/ <-
(Log_DB and SRO_VT_SHARD_INIT)


Example:







EDIT:

The Version with x-Minutes ago instead of "Date"..

UUSE /*#*/SRO_VT_SHARD_INIT/*#*/ 
    SELECT TOP 100  
        chart.CharName16 as Player,  
        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,  
        cast(DATEDIFF(MINUTE,cast(elog.EventTime as datetime),GETDATE()) as varchar(MAX)) + ' minute(s) ago.' as 'Time'
    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 

[MentaL]

[tamer153]

nice !

[meowlicious]

very useful. tyvm!

[3d2000]

Thanks :)

i think this will not work form me if i deleted the Logs

[kaperucito]

Amazing query!

[megaman963]

Caipi, Can't beat you !! :D

you are awesome !