• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4390
  • Last Modified:

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:
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 1

Open in new window


 I need logons grouped by hour
0
Mark_Co
Asked:
Mark_Co
  • 2
  • 2
1 Solution
 
sdstuberCommented:
S ELECT   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 1,2
0
 
Mark_CoAuthor Commented:
That is reaaaaally close to what I need
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, 1

Open in new window


 returns like this
8/13/2012 9:00:00 AM

Open in new window

but I need this:
00:00 
01:00 
02:00
03:00 
....
22:00
23:00

Open in new window

0
 
sdstuberCommented:
format it however you want

to_char(TRUNC(timestamp, 'hh24'),'hh24:mi')
0
 
Mark_CoAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now