INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database Session Connection Audit - LOG_OFF_TIME

Database Session Connection Audit - LOG_OFF_TIME

(OP)
Hello All,
I am trying to report sessions connection to the database withing the last 24 hours to include all of the following columns AND LOG_OFF_TIME:

CODE --> SQL

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       --p.spid,
       --s.lockwait,
       --s.status,
       --s.service_name,
       --s.module,
       s.machine,
       s.terminal,
       --s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
       s.last_call_et AS last_call_et_secs
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser; 

Here is sample output:
CPM clarit 1 410 RTP001 PDXWCLRTP001 13-NOV-2016 12:04:20 1394125
CPM clarit 70 190 LRTP001 PDXWCLRTP001 13-NOV-2016 12:11:41 1393681
CPM clarit 194 970 LRTP001 PDXWCLRTP001 14-NOV-2016 07:03:07 1325798

How can I modify the code to generate the report for the last 24 hours, and include LOG_OFF_TIME in the columns?

Thanks

RE: Database Session Connection Audit - LOG_OFF_TIME

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
--p.spid,
--s.lockwait,
s.status,
--s.service_name,
--s.module,
s.machine,
s.terminal,
--s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
TO_CHAR(s.logon_Time + (s.last_call_et/86400),'DD-MON-YYYY HH24:MI:SS') AS logoff_session_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
and s.logon_Time + (s.last_call_et/86400) >= sysdate - 1
ORDER BY s.username, s.osuser;
ORDER BY s.username, s.osuser;

Bill
Lead Application Developer
New York State, USA

RE: Database Session Connection Audit - LOG_OFF_TIME

(OP)
Thanks, Bill.

I was wondering how I can make this a 24 hr. script. That is, generate session report like this for all the session that happened/connected in the past 24hrs.

I will like to generate this report without using resource consuming fine grain auditing.

RE: Database Session Connection Audit - LOG_OFF_TIME

any session that was active in the last 24 hours is eligible for you report (as I understand your needs) The where clause

and s.logon_Time + (s.last_call_et/86400) >= sysdate - 1
says that if the current session time (for active sessions) or the logoff time (for inactive sessions) is greater then or equal to 24 hours ago from NOW then display it. If this is not what you want then explain your requirements

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close