SQL Script - Checking the size of the logs / deleting

Cleaning up database

Written by Fernando

Last published at: July 23rd, 2024


Query Description
 

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

 

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())