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','successful','1005','Failed 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

mrjoltcola

Something like this, I suppose. Extend to your needs:

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

Cha1tu

ASKER

@mrjoltcola: Awesome ..Thanks for ur response

Cha1tu

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 ?

mrjoltcola

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

Open in new window

Cha1tu

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

Cha1tu

ASKER

need to identify from where. Also, may need to look at only certain accounts and possibly compare time for spikes.

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial