SQL Script - Checking the size of the logs / deleting
Cleaning up database
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())