Query to extract hour / timestamp from sys.dba_audit_trail
I'm struggling to create a query that aggregates logins by hour from sys.dba_audit_trail.
I was messing with timestamp like this at first but this only gives me logons from the past hour:
I need logons grouped by hour
I was messing with timestamp like this at first but this only gives me logons from the past hour:
SELECT A.OS_USERNAME
,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
AND TIMESTAMP >= TO_TIMESTAMP('2012/08/24 11:00:00', 'YYYY/MM/DD HH24:MI:SS')
GROUP BY A.OS_USERNAME
ORDER BY 1I need logons grouped by hour
ASKER
That is reaaaaally close to what I need
returns like this
SELECT
TRUNC(TIMESTAMP, 'HH24') HOUR
,A.OS_USERNAME
,COUNT(*)
FROM SYS.DBA_AUDIT_TRAIL A
WHERE A.ACTION_NAME = 'LOGON'
GROUP BY A.OS_USERNAME
,TRUNC(TIMESTAMP, 'HH24')
ORDER BY 2, 1returns like this
8/13/2012 9:00:00 AM00:00
01:00
02:00
03:00
....
22:00
23:00ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
FROM sys.dba_audit_trail a
WHERE a.action_name = 'LOGON'
GROUP BY a.os_username, TRUNC(timestamp, 'hh24')
ORDER BY 1,2