Home » RDBMS Server » Security » Logon/off audit using Audit_Trail (Oracle, 10g R2, XP)
Logon/off audit using Audit_Trail [message #468570] Fri, 30 July 2010 13:19 Go to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi Experts,

I'm pretty much new in using Audit_Trail and I would like to seek some help with regards to getting logon timestamp for our auditing process. In some website, it says to get the logon timestamp I have to select it from timestamp column of dba_audit_session but when I do this, some results has a logoff earlier than logon. Is timestamp column really the logon timestamp?

SELECT
returncode,
(logoff_time-timestamp) timeonline,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME,
action_name
FROM dba_audit_session;

RETURNCODE TIMEONLINE LOGON_TIME LOGOFF_TIME
---------- ---------- -------------------- --------------------
ACTION_NAME
----------------------------
0 -0.0374190 15-JAN-2010 14:50:53 15-JAN-2010 13:57:00
LOGOFF

0 -0.0416667 15-JAN-2010 14:56:50 15-JAN-2010 13:56:50
LOGOFF

0 -0.0416667 15-JAN-2010 14:56:50 15-JAN-2010 13:56:50
LOGOFF

0 -0.0408333 28-JAN-2010 15:29:51 28-JAN-2010 14:31:03
LOGOFF BY CLEANUP

0 -0.0408333 28-JAN-2010 15:29:51 28-JAN-2010 14:31:03
LOGOFF BY CLEANUP

0 -0.0408449 28-JAN-2010 15:29:52 28-JAN-2010 14:31:03
LOGOFF BY CLEANUP

0 -0.0409028 28-JAN-2010 15:29:57 28-JAN-2010 14:31:03

RETURNCODE TIMEONLINE LOGON_TIME LOGOFF_TIME
---------- ---------- -------------------- --------------------
ACTION_NAME
----------------------------
LOGOFF BY CLEANUP

0 -1.157E-05 24-MAR-2010 13:12:33 24-MAR-2010 13:12:32
LOGOFF

0 -1.157E-05 24-MAR-2010 13:12:33 24-MAR-2010 13:12:32
LOGOFF

0 .023993056 19-MAY-2010 15:15:12 19-MAY-2010 15:49:45
LOGOFF

0 1.1574E-05 19-MAY-2010 15:15:15 19-MAY-2010 15:15:16
LOGOFF

0 .060289352 21-MAY-2010 13:54:22 21-MAY-2010 15:21:11
LOGOFF

0 0 21-MAY-2010 13:54:25 21-MAY-2010 13:54:25
LOGOFF

Thank you,
GAN
Re: Logon/off audit using Audit_Trail [message #468572 is a reply to message #468570] Fri, 30 July 2010 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66776
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LOGON and LOGOFF records are the same ones for each session in audit trail.
If the session is not completed then the action is LOGON, if the session ended then the action is LOGOFF%.
You have the logon time in TIMESTAMP column as this is the time the record was created.
You have the logoff time in LOGOFF_TIME column which is updated when the session is destroyed (if it ended) unless it was not created because there was an error at connection time (then the RETURNCODE column gives you the reason).

Before your next question, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Fri, 30 July 2010 13:57]

Report message to a moderator

Re: Logon/off audit using Audit_Trail [message #468573 is a reply to message #468572] Fri, 30 July 2010 13:47 Go to previous message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi Michel,

That answered my question.

Thank you,
GAN

P.S. Sorry about the format.
Previous Topic: User security violations
Next Topic: How to find FGA,
Goto Forum:
  


Current Time: Sat Jan 18 17:06:59 CST 2020