SQL: Log Statistics (Top 100)

Version 1

    Purpose

     

    These commands show Log Statistics for various elements of EMSS, similar to the log stats info contained in ES.

     

    Overview

    --Run in sql management studio.

     

     

     

    -- Top user activity info, by user

    SELECT

    TOP 100 COUNT(LE.UserID) AS [COUNT],

    LE.UserID,

    MAX(LU.NTUserName) AS NTUserName

    FROM UPCCommon.dbo.LogEntry LE (NOLOCK)

    JOIN UPCCommon.dbo.LogAction LA (NOLOCK)

    ON LA.ActionID = LE.ActionID

    JOIN UPCCommon.dbo.LogUser LU (NOLOCK)

    ON LU.UserID = LE.UserID

    GROUP BY LE.UserID

    ORDER BY [COUNT] DESC

     

    -- Top computer activity info, by computer

    SELECT

    TOP 100 COUNT(LE.ComputerID) AS [COUNT],

    LE.ComputerID,

    MAX(LC.ComputerName) AS ComputerName

    FROM UPCCommon.dbo.LogEntry LE (NOLOCK)

    JOIN UPCCommon.dbo.LogAction LA (NOLOCK)

    ON LA.ActionID = LE.ActionID

    JOIN UPCCommon.dbo.LogComputer LC (NOLOCK)

    ON LC.ComputerID = LE.ComputerID

    GROUP BY LE.ComputerID

    ORDER BY [COUNT] DESC

     

    -- Top process name activity, by process name

    SELECT

    TOP 100 COUNT(LD.Value) as [COUNT],

    MAX(LD.Value) AS ProcessName

    FROM UPCCommon.dbo.LogEntry_To_LogData LE2LD (NOLOCK)

    JOIN UPCCommon.dbo.LogData LD (NOLOCK)

    ON (LD.DataID = LE2LD.DataID AND LE2LD.DataTypeID = 12)

    GROUP BY LD.Value

    ORDER BY [COUNT] DESC

     

    -- Top device activity, by device name

    SELECT

    TOP 100 COUNT(LD.Value) as [COUNT],

    MAX(LD.Value) AS DeviceName

    FROM UPCCommon.dbo.LogEntry_To_LogData LE2LD (NOLOCK)

    JOIN UPCCommon.dbo.LogData LD (NOLOCK)

    ON (LD.DataID = LE2LD.DataID AND LE2LD.DataTypeID = 8)

    GROUP BY LD.Value

    ORDER BY [COUNT] DESC

     

     

    Additional Information

     

    Affected Products

    EMSS