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