We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

can i identify the often some login requests in oracle auditing?

Cha1tu
Cha1tu asked
on
Medium Priority
1,061 Views
Last Modified: 2013-12-18
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                          
Comment
Watch Question

Top Expert 2009

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

Author

Commented:
@mrjoltcola: Awesome ..Thanks for ur response

Author

Commented:
@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 ?
Top Expert 2009

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

Author

Commented:
@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

Author

Commented:
need to identify from where.  Also, may need to look at only certain accounts and possibly compare time for spikes.
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.