Cha1tu
asked on
can i identify the often some login requests in oracle auditing?
Hi,
i want to know the some often login users in the database. how many users and also how much time they are spending in database,once they login to the database.i want to know the time difference between login and logout..i want to know the how much time the user spending on the database.
for example when query this sql statement i am getting the results like this
SQL> select os_username,
2 username,
3 decode(returncode,'0','suc cessful',' 1005','Fai led Null',
4 '1017','Logon Failed',returncode) pass_fail,
5 to_char(timestamp, 'MM/DD/YY HH24:MI:SS') timein,
6 to_char(logoff_time,'MM/DD /YY HH24:MI:SS') timeout
7 from dba_audit_session;
OS_USERNAM USERNAME PASS_FAIL TIMEIN TIMEOUT
---------- ----------- ------------ ----------------- -----------------
TEST successful 03/08/11 10:03:56 03/08/11 10:03:56
USER1 TEST successful 03/08/11 10:19:34
USER1 TEST successful 03/08/11 10:19:37
TEST successful 03/08/11 10:19:37 03/08/11 10:19:37
DEV successful 03/08/11 10:47:23 03/08/11 10:47:23
USER2 DEV successful 03/08/11 12:06:36
USER2 DEV Logon Failed 03/07/11 17:10:27
OS_USERNAM USERNAME PASS_FAIL TIMEIN TIMEOUT
---------- ----------- ------------ ----------------- -----------------
USER2 DEV successful 03/10/11 15:56:06
USER2 DEV successful 03/10/11 15:56:08
USER2 DEV successful 03/10/11 15:58:26
i want to know the some often login users in the database. how many users and also how much time they are spending in database,once they login to the database.i want to know the time difference between login and logout..i want to know the how much time the user spending on the database.
for example when query this sql statement i am getting the results like this
SQL> select os_username,
2 username,
3 decode(returncode,'0','suc
4 '1017','Logon Failed',returncode) pass_fail,
5 to_char(timestamp, 'MM/DD/YY HH24:MI:SS') timein,
6 to_char(logoff_time,'MM/DD
7 from dba_audit_session;
OS_USERNAM USERNAME PASS_FAIL TIMEIN TIMEOUT
---------- ----------- ------------ ----------------- -----------------
TEST successful 03/08/11 10:03:56 03/08/11 10:03:56
USER1 TEST successful 03/08/11 10:19:34
USER1 TEST successful 03/08/11 10:19:37
TEST successful 03/08/11 10:19:37 03/08/11 10:19:37
DEV successful 03/08/11 10:47:23 03/08/11 10:47:23
USER2 DEV successful 03/08/11 12:06:36
USER2 DEV Logon Failed 03/07/11 17:10:27
OS_USERNAM USERNAME PASS_FAIL TIMEIN TIMEOUT
---------- ----------- ------------ ----------------- -----------------
USER2 DEV successful 03/10/11 15:56:06
USER2 DEV successful 03/10/11 15:56:08
USER2 DEV successful 03/10/11 15:58:26
ASKER
@mrjoltcola: Awesome ..Thanks for ur response
ASKER
@mrjoltcola:
SQL> select username, sum(logoff_time - timestamp) from dba_audit_session group
by username
2 ;
USERNAME SUM(LOGOFF_TIME-TIMESTAMP)
------------------ --------------------------
test 0
dev 0
prod 0
stage 0
admin 0
Can you see this results..i suppose to get the sum results as digits right? but why here getting all '0' s .
can please tell me that ?
SQL> select username, sum(logoff_time - timestamp) from dba_audit_session group
by username
2 ;
USERNAME SUM(LOGOFF_TIME-TIMESTAMP)
------------------ --------------------------
test 0
dev 0
prod 0
stage 0
admin 0
Can you see this results..i suppose to get the sum results as digits right? but why here getting all '0' s .
can please tell me that ?
Oops, sorry. I forgot how dba_audit_session table works. You have to self-join the logon record to the logoff record, I believe. Try these.
-- Total logon time ever for each user (within audit history)
select username, trunc(sum(logoff_time - logon_time) * 24 * 60) minutes from
(select username, timestamp logon_time, sessionid from dba_audit_session where action_name = 'LOGON') logon
join
(select logoff_time, sessionid from dba_audit_session where action_name = 'LOGOFF') logoff
on logon.sessionid = logoff.sessionid
group by username
-- Average logon time per session
select username, trunc(avg(logoff_time - logon_time) * 24 * 60) minutes from
(select username, timestamp logon_time, sessionid from dba_audit_session where action_name = 'LOGON') logon
join
(select logoff_time, sessionid from dba_audit_session where action_name = 'LOGOFF') logoff
on logon.sessionid = logoff.sessionid
group by username
ASKER
@mrjoltcola:
here its giving the results like
USERNAME MINUTES
test 9
dev 0
prod 3
stage 15
admin 0
Thats fine can you please tell me how to find userhost and terminal from which server
Thanks
here its giving the results like
USERNAME MINUTES
test 9
dev 0
prod 3
stage 15
admin 0
Thats fine can you please tell me how to find userhost and terminal from which server
Thanks
ASKER
need to identify from where. Also, may need to look at only certain accounts and possibly compare time for spikes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
-- Distinct users who have logged in
select count distinct username from dba_audit_session
-- Total time spent by users
select username, sum(logoff_time - timestamp) from dba_audit_session group by username