-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathaudit_sum.sql
86 lines (69 loc) · 2.83 KB
/
audit_sum.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: Query the audit log entries
--
-- Must be run with dba privileges
--
--==============================================================================
set linesize 130 pagesize 300
column username format a20 heading "DB User|name"
column action_name format a25 heading "Action|name"
column first_log format a25 heading "First|entry"
column last_log format a25 heading "Last|entry"
column entries format 999G999G999 heading "Audit|entries"
ttitle left "Audit Trail from time until time" skip 2
select to_char(min(timestamp),'dd.mm.yyyy hh24:mi:ss') as first_log
, to_char(max(timestamp),'dd.mm.yyyy hh24:mi:ss') as last_log
, count(*) as entries
from dba_audit_trail
order by 1
/
ttitle left "Audit Object Log entries " skip 2
select username
, action_name
, count(*) as entries
, to_char(min(timestamp),'dd.mm.yyyy hh24:mi:ss') as first_log
, to_char(max(timestamp),'dd.mm.yyyy hh24:mi:ss') as last_log
from dba_audit_object
group by username,action_name
order by 1
/
--------------------- Details ----------------------------
column action_count format 9G999G999 heading "Action|Count"
column os_username format a16 heading "User|Name"
column username format a15 heading "DB User|name"
column action_name format a20 heading "Action|name"
column instance_number format 99 heading "In|st"
column user_host format a11 heading "User|Host"
column first_log format a15 heading "First|entry"
column last_log format a15 heading "Last|entry"
break on instance_number
--COMPUTE SUM OF action_count ON first_log
ttitle left "Audit log summary overview " skip 2
select count(*) as action_count
, os_username
, (case when length(USERHOST) > 10 then '...'||substr(USERHOST,-8,10) else USERHOST end) as user_host
, username
, instance_number
, to_char(min(extended_timestamp),'dd.mm hh24:mi') as first_log
, to_char(max(extended_timestamp),'dd.mm hh24:mi') as last_log
, action_name
from dba_audit_trail
where extended_timestamp between sysdate -(1/4) and sysdate
-- username='GPI'
-- extended_timestamp between to_date('13.11.2014 12:19','dd.mm.yyyy hh24:mi') and to_date('13.11.2014 12:21','dd.mm.yyyy hh24:mi')
-- and USERHOST='---'
group by os_username
, (case when length(USERHOST) > 10 then '...'||substr(USERHOST,-8,10) else USERHOST end)
, username
, action_name
, instance_number
order by instance_number,username,action_name
/
clear break
clear computes
prompt
prompt ... for detail information call: "audit_rep.sql"
prompt ... for the space usage of the audit$ table call: "tab_space.sql aud$"
prompt
ttitle off