How can I check user login times in 11g

Hi guys, which view I can use to check a particular user's login and logoff times.
nirvairghumanAsked:
Who is Participating?
 
sdstuberCommented:
eslect * from dba_audit_trail where action_name in ('LOGON','LOGOFF') and username = 'YOURUSER'
0
 
slightwv (䄆 Netminder) Commented:
If you don't have auditing turned on I believe you will need a system trigger to track this.

There might be other options that I do not know about.
0
 
nirvairghumanAuthor Commented:
Our auditing is on,
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
If you are auditing the session then you should be able to query the audit tables.

On mobile and cannot search for examples but there should be examples in the online docs on querying the audit trail.
0
 
schwertnerCommented:
I have used this simple approach (in the SYS schema):

1. Create the table to capture the LOGIN and LOGOUT timings.

DROP TABLE ICW_logonaudittable;

CREATE TABLE ICW_logonaudittable
(
  event       VARCHAR2(10),
  sid         NUMBER,
  serial#     NUMBER,
  timestamp   DATE,
  username    VARCHAR2(30),
  osuserid    VARCHAR2(30),
  machinename VARCHAR2(64),
  ip          VARCHAR2(20)
);
 


2. Create LOGON trigger at DATABASE level.
 
CREATE OR REPLACE TRIGGER logonauditing AFTER LOGON ON database
DECLARE
  machinename VARCHAR2(64);
  osuserid    VARCHAR2(30);
  v_sid       NUMBER(10);
  v_serial    NUMBER(10);
  v_ip        VARCHAR2(20);
   
  CURSOR c1 IS
    SELECT sid, serial#, osuser, machine, sys_context('userenv','ip_address')
      FROM v$session WHERE audsid = userenv('sessionid');
BEGIN
  OPEN c1;
  FETCH c1 INTO v_sid, v_serial, osuserid, machinename, v_ip;
  INSERT INTO ICW_logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
      user, osuserid, machinename, v_ip);
 
  CLOSE c1;
END;
/


3. Check the result:


set linesize 1000
set pagesize 1000
set trimspool on
set heading on
set underline '-'
column event format A9
column sid   format 99999
column serial# format 99999
column username format A20 wrap
column osuserid format A20 wrap
column machinename format A20 wrap
column timestamp format A30 wrap
select event, sid, serial#, username, osuserid,
        machinename,
       to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') as TIMESTAMP
from ICW_logonaudittable;
0
 
gajmpCommented:
schwertner:
      They have already enable AUDIT then why we need to maintain system level trigger and user defined audit table?
      I accept system audit table wont maintain IP address. but you have used that. I didnt think IP will be useful in all the cases.

      All the application will maintain application level audit that is another context.
0
 
sdstuberCommented:
>>> why we need to maintain system level trigger

there isn't a "need",  it's simply an alternate approach.


however in 11g the comment_text of dba_audit_trail may contain tns connect info that includes client ip/name

for example....

Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=57278))

it's not reliable for all sessions, but many
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.