Database Session Connection Audit - LOG_OFF_TIME 1

[tekpr00]

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:

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

 

[Beilstwh]

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

 

[tekpr00]

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.

 

[Beilstwh]

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