[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

 

[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 !!

you are awesome !