SQL Script - Checking the size of the logs / deleting

List and order table starting from the highest count of log types.

Written by Fernando

Last published at: February 27th, 2024

Query Description

List and order table starting from the highest count of log types

Query Results

Queries

For versions before 7.1

SELECT
        'LogChildType'=CASE
        WHEN LogChildType = 0 THEN 'DeviceInventory'
        WHEN LogChildType = 1 THEN 'Connect'
        WHEN LogChildType = 2 THEN 'Disconnect'
        WHEN LogChildType = 3 THEN 'Lock'
        WHEN LogChildType = 4 THEN 'Unlock'
        WHEN LogChildType = 5 THEN 'UnlockAttemptFail'
        WHEN LogChildType = 6 THEN 'GuiStart'
        WHEN LogChildType = 7 THEN 'GuiEnd'
        WHEN LogChildType = 8 THEN 'DeviceLogin'
        WHEN LogChildType = 9 THEN 'DeviceLogoff'
        WHEN LogChildType = 10 THEN 'ConnectorLogin'
        WHEN LogChildType = 11 THEN 'ConnectorLogoff'
        WHEN LogChildType = 12 THEN 'ConnectorResourceList'
        WHEN LogChildType = 13 THEN 'ConnectorErrors'
        WHEN LogChildType = 14 THEN 'ResourceLaunch'
        WHEN LogChildType = 15 THEN 'ResourceEnd'
        WHEN LogChildType = 16 THEN 'ResourceLaunchFail'
        WHEN LogChildType = 17 THEN 'ActiveProfile'
        WHEN LogChildType = 18 THEN 'BrowserLinkSelect'
        WHEN LogChildType = 19 THEN 'LdapPasswordChange'
        WHEN LogChildType = 20 THEN 'PowerAction'
        WHEN LogChildType = 21 THEN 'SecurityCentreStatus'
        WHEN LogChildType = 22 THEN 'WindowsUpdateStatus'
        WHEN LogChildType = 23 THEN 'WindowsFirewallStatus'
        WHEN LogChildType = 24 THEN 'WifiAdapterStatus'
        WHEN LogChildType = 25 THEN 'VMDetectionStatus'
        WHEN LogChildType = 26 THEN 'ProcessActionApply'
        WHEN LogChildType = 27 THEN 'ProcessActionRelease'
        WHEN LogChildType = 28 THEN 'LocalApplicationLaunch'
        WHEN LogChildType = 29 THEN 'AEPAllow'
        WHEN LogChildType = 30 THEN 'AEPDeny'
        WHEN LogChildType = 31 THEN 'SessionActionApply'
        WHEN LogChildType = 32 THEN 'SessionActionRelease'
        WHEN LogChildType = 33 THEN 'SEPStop'
        WHEN LogChildType = 34 THEN 'SEPNotify'
        WHEN LogChildType = 35 THEN 'ValidationToolResult'
        WHEN LogChildType = 36 THEN 'DeviceInventoryInstalledWindowsUpdate'
        WHEN LogChildType = 37 THEN 'UrlBlocked'
        WHEN LogChildType = 38 THEN 'UsbDeny'
        END,COUNT(LogChildType) AS 'Count'
        FROM Logs
        GROUP BY LogChildType
        ORDER BY 'Count' DESC

For versions 7.1, 7.2 and 7.3

SELECT 
        'LogTypeName'=CASE
        WHEN LogType = 0 THEN 'DeviceInventory'
        WHEN LogType = 1 THEN 'Connect'
        WHEN LogType = 2 THEN 'Disconnect'
        WHEN LogType = 3 THEN 'Lock'
        WHEN LogType = 4 THEN 'Unlock'
        WHEN LogType = 5 THEN 'UnlockAttemptFail'
        WHEN LogType = 6 THEN 'GuiStart'
        WHEN LogType = 7 THEN 'GuiEnd'
        WHEN LogType = 8 THEN 'DeviceLogin'
        WHEN LogType = 9 THEN 'DeviceLogoff'
        WHEN LogType = 10 THEN 'ConnectorLogin'
        WHEN LogType = 11 THEN 'ConnectorLogoff'
        WHEN LogType = 12 THEN 'ConnectorResourceList'
        WHEN LogType = 13 THEN 'ConnectorErrors'
        WHEN LogType = 14 THEN 'ResourceLaunch'
        WHEN LogType = 15 THEN 'ResourceEnd'
        WHEN LogType = 16 THEN 'ResourceLaunchFail'
        WHEN LogType = 17 THEN 'ActiveProfile'
        WHEN LogType = 18 THEN 'BrowserLinkSelect'
        WHEN LogType = 19 THEN 'LdapPasswordChange'
        WHEN LogType = 20 THEN 'PowerAction'
        WHEN LogType = 21 THEN 'SecurityCentreStatus'
        WHEN LogType = 22 THEN 'WindowsUpdateStatus'
        WHEN LogType = 23 THEN 'WindowsFirewallStatus'
        WHEN LogType = 24 THEN 'WifiAdapterStatus'
        WHEN LogType = 25 THEN 'VMDetectionStatus'
        WHEN LogType = 26 THEN 'ProcessActionApply'
        WHEN LogType = 27 THEN 'ProcessActionRelease'
        WHEN LogType = 28 THEN 'LocalApplicationLaunch'
        WHEN LogType = 29 THEN 'AEPAllow'
        WHEN LogType = 30 THEN 'AEPDeny'
        WHEN LogType = 31 THEN 'SessionActionApply'
        WHEN LogType = 32 THEN 'SessionActionRelease'
        WHEN LogType = 33 THEN 'SEPStop'
        WHEN LogType = 34 THEN 'SEPNotify'
        WHEN LogType = 35 THEN 'ValidationToolResult'
        WHEN LogType = 36 THEN 'DeviceInventoryInstalledWindowsUpdate'
        WHEN LogType = 37 THEN 'UrlBlocked'
        WHEN LogType = 38 THEN 'UsbDeny'
        WHEN LogType = 39 THEN 'SEPStart'
        WHEN LogType = 40 THEN 'AMPBlocked'
        WHEN LogType = 41 THEN 'DEPBlocked'
        WHEN LogType = 42 THEN 'AuthSuccess'
        WHEN LogType = 43 THEN 'AuthFail'
        END,Logtype,COUNT(LogType) AS 'Count'
        FROM LogsEvent
        GROUP BY LogType
        ORDER BY 'Count' DESC

For versions 7.4 and newer

SELECT 
        'LogTypeName'=CASE
        WHEN LogType = 0 THEN 'DeviceInventory'
        WHEN LogType = 1 THEN 'Connect'
        WHEN LogType = 2 THEN 'Disconnect'
        WHEN LogType = 3 THEN 'Lock'
        WHEN LogType = 4 THEN 'Unlock'
        WHEN LogType = 5 THEN 'UnlockAttemptFail'
        WHEN LogType = 6 THEN 'GuiStart'
        WHEN LogType = 7 THEN 'GuiEnd'
        WHEN LogType = 8 THEN 'DeviceLogin'
        WHEN LogType = 9 THEN 'DeviceLogoff'
        WHEN LogType = 10 THEN 'ConnectorLogin'
        WHEN LogType = 11 THEN 'ConnectorLogoff'
        WHEN LogType = 12 THEN 'ConnectorResourceList'
        WHEN LogType = 13 THEN 'ConnectorErrors'
        WHEN LogType = 14 THEN 'ResourceLaunch'
        WHEN LogType = 15 THEN 'ResourceEnd'
        WHEN LogType = 16 THEN 'ResourceLaunchFail'
        WHEN LogType = 17 THEN 'ActiveProfile'
        WHEN LogType = 18 THEN 'BrowserLinkSelect'
        WHEN LogType = 19 THEN 'LdapPasswordChange'
        WHEN LogType = 20 THEN 'PowerAction'
        WHEN LogType = 21 THEN 'SecurityCentreStatus'
        WHEN LogType = 22 THEN 'WindowsUpdateStatus'
        WHEN LogType = 23 THEN 'WindowsFirewallStatus'
        WHEN LogType = 24 THEN 'WifiAdapterStatus'
        WHEN LogType = 25 THEN 'VMDetectionStatus'
        WHEN LogType = 26 THEN 'ProcessActionApply'
        WHEN LogType = 27 THEN 'ProcessActionRelease'
        WHEN LogType = 28 THEN 'LocalApplicationLaunch'
        WHEN LogType = 29 THEN 'AEPAllow'
        WHEN LogType = 30 THEN 'AEPDeny'
        WHEN LogType = 31 THEN 'SessionActionApply'
        WHEN LogType = 32 THEN 'SessionActionRelease'
        WHEN LogType = 33 THEN 'SEPStop'
        WHEN LogType = 34 THEN 'SEPNotify'
        WHEN LogType = 35 THEN 'ValidationToolResult'
        WHEN LogType = 36 THEN 'DeviceInventoryInstalledWindowsUpdate'
        WHEN LogType = 37 THEN 'UrlBlocked'
        WHEN LogType = 38 THEN 'UsbDeny'
        WHEN LogType = 39 THEN 'SEPStart'
        WHEN LogType = 40 THEN 'AMPBlocked'
        WHEN LogType = 41 THEN 'DEPBlocked'
        WHEN LogType = 42 THEN 'AuthSuccess'
        WHEN LogType = 43 THEN 'AuthFail'
        END,Logtype,COUNT(LogType) AS 'Count'
        FROM LogsEvent2
        GROUP BY LogType
        ORDER BY 'Count' DESC

Deletes all AEP Deny logs older than 3 days:

Delete FROM LogsEvent2
where LogType = 30 and
[Logdate] < DATEADD(d, -3,getdate())

ThinScale Help Center