SQL Script - Checking the size of the logs / deleting

Cleaning up database

Written by Fernando

Last published at: January 21st, 2025

Query Description

List and order table starting from the highest count of log types and at the bottom option to delete.

Query Results

List 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 7.5

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

For version 8.x

SELECT 
       'DeviceEventType'= CASE
       WHEN DeviceEventType = 0 THEN 'Device Inventory Change'
       WHEN DeviceEventType = 1 THEN 'Device Connected'
       WHEN DeviceEventType = 2 THEN 'Device Disconnect'
       WHEN DeviceEventType = 3 THEN 'Device Unlock Attempt Failed'
       WHEN DeviceEventType = 4 THEN 'Device Unlocked'
       WHEN DeviceEventType = 5 THEN 'User Logoff'
       WHEN DeviceEventType = 6 THEN 'User Logon'
       WHEN DeviceEventType = 7 THEN 'Process Security Object Denied'
       WHEN DeviceEventType = 8 THEN 'Process Security Process Allowed'
       WHEN DeviceEventType = 9 THEN 'Process Security Process Denied'
       WHEN DeviceEventType = 10 THEN 'Process Security Module Denied'
       WHEN DeviceEventType = 11 THEN 'Process Security Volume Denied'
       WHEN DeviceEventType = 12 THEN 'Process Security Registry Denied'
       WHEN DeviceEventType = 13 THEN 'Process Security Network Denied'
       WHEN DeviceEventType = 14 THEN 'Driver Protection Denied'
       WHEN DeviceEventType = 15 THEN 'User Authentication Success'
       WHEN DeviceEventType = 16 THEN 'User Authentication Failed'
       WHEN DeviceEventType = 17 THEN 'Session Start'
       WHEN DeviceEventType = 18 THEN 'Session End'
       WHEN DeviceEventType = 19 THEN 'Profile Activated'
       WHEN DeviceEventType = 20 THEN 'SEP Actions'
       WHEN DeviceEventType = 21 THEN 'SEP Notifications'
       WHEN DeviceEventType = 22 THEN 'Browser Link Select'
       WHEN DeviceEventType = 23 THEN 'Website Blocked'
       WHEN DeviceEventType = 24 THEN 'Power Action'
       WHEN DeviceEventType = 25 THEN 'Local Application Launch'
        END,DeviceEventType,COUNT(DeviceEventType) AS 'Count'
       FROM LogsSWADeviceEvents
       GROUP BY DeviceEventType
       ORDER BY 'Count' DESC

Delete queries

v7.x - Deletes all “AEP Deny” logs older than 3 days:

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

v8.x - Deletes all “Process Security Process Denied” logs older than 3 days:

Delete FROM LogsSWADeviceEvents
where DeviceEventType = 9 and
[Logdate] < DATEADD(d, -3,getdate())

ThinScale Help Center

Contact Us