3 Replies Latest reply: Feb 3, 2013 6:08 AM by Jonathan Lewis RSS

    login logout information in oracle11g

    shipon_97
      In Oracle 11g, I cannot get user's login/logon information properly . when a user logon and then logout from his session then I got the below information using the below specified sql query :

      select username,userhost,timestamp,action_name,logoff_time from dba_audit_trail where
      timestamp between to_date('03-FEB-2013 09:00:00','DD-MON-YYYY hh24:mi:ss') and
      to_date('03-FEB-2013 23:00:00','DD-MON-YYYY hh24:mi:ss') and
      action_name in ('LOGOG','LOGOFF') and username='TEST' order by timestamp desc


      ----
      USERNAME, USERHOST, TIMESTAMP, ACTION_NAME, LOGOFF_TIME

      "TEST",, 2/3/2013 12:18:09 PM, "LOGOFF",``` 2/3/2013 12:18:09 PM

      "TEST",, 2/3/2013 12:17:57 PM, "LOGOFF", 2/3/2013 12:17:57 PM
      ---

      Here in the output, during LOGON ,the output shows same time in the TIMESTAMP as well as LOGOFF_TIME fields . Also the LOGOUT moment, it also shows the same time in TIMESTAMP and LOGOFF_TIME fields . For this reason I cannot find out the proper information of a user's login/logout time .

      Would any body help me , how I can find the login/logout information in Oracle11g environemtnt .
        • 1. Re: login logout information in oracle11g
          Osama_Mustafa
          "You would enable auditing in the database and audit connects. This will record the login/logout activity into the audit trail. The audit trail is either a database table OR the system event log (OS specific as to where that is). If you are using the database table, creating a flat file is trivial (simple query and spool from sqlplus)."

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
          • 2. Re: login logout information in oracle11g
            JohnWatson
            The auditing information looks comprehensible to me. You do need to change your predeicate though, this
            action_name in ('LOGOG','LOGOFF')
            should read
            action_name in ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
            • 3. Re: login logout information in oracle11g
              Jonathan Lewis
              shipon_97 wrote:
              In Oracle 11g, I cannot get user's login/logon information properly . when a user logon and then logout from his session then I got the below information using the below specified sql query :
              select username,userhost,timestamp,action_name,logoff_time from dba_audit_trail where 
              timestamp between to_date('03-FEB-2013 09:00:00','DD-MON-YYYY hh24:mi:ss')  and 
              to_date('03-FEB-2013 23:00:00','DD-MON-YYYY hh24:mi:ss') and
              action_name in ('LOGOG','LOGOFF') and username='TEST' order by timestamp desc
              It would be more convenient to query dba_audit_session - probably using subquery factoring with materialization, then join two copies to itself by sessionid, copy one with the LOGON action, and copy 2 where the action_name != 'LOGON'.

              It's interesting to see from the link to AskTom (ca. 2004, at least) a simple repeated connect user/pw from sql *plus could give you one logon record with lots of logoff records, though - I hadn't noticed that before.

              Regards
              Jonathan Lewis