--
Fri 08/15/1997
Funny, I just had to do that yesterday. Not from SYS.AUD$ but from
another table, but it's essentially the same.
/*
UserLogonViews.sql -- Copyright(c) 1997 Paul Passarelli
08/14/97 pmp .000 some views on the userlogon table
---------------------------------------------------------------------
CREATE TABLE userlogon
(
userlogonid number(8) constraint pk_userlogonid
primary key,
userid number(8) constraint
fk_userid_userlogon references userid DISABLE,
logondate date ,
logoffdate date ,
logonapplication varchar2(20) ,
logoffreason varchar2(10)
)
TABLESPACE USER_DATA
*/
-- This one is completed and or aborted sessions
create or replace view vw_userlogon
(
"User Name",
"Application used",
"Logoff Reason",
"Logon Date-Time",
"Duration of use"
)
as
select
useridname,
logonapplication,
logoffreason,
to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate",
to_char( to_date( to_char( 86400 * ( logoffdate - logondate ),'99999' ),
'SSSSS' ), '"Logoff after" HH24:MI:SS' ) "Duration"
from userid, userlogon
where userid.userid = userlogon.userid
and 1 > (logoffdate - logondate)
with read only
;
-- This one is active logons i.e. less than 1 day old
create or replace view vw_activeusers
(
"User Name",
"Application used",
"Logon Date-Time",
"Duration of use"
)
as
select
useridname,
logonapplication,
to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate",
to_char( to_date( to_char( 86400 * ( NOW - logondate ),'99999' ), 'SSSSS'
), '"Logged on for" HH24:MI:SS' ) "Duration"
from userid, userlogon, (select SYSDATE as NOW from DUAL)
where userid.userid = userlogon.userid
and 1 > (NOW - logondate)
and logoffdate is NULL
with read only
;
-- This one is stale logons i.e. more than 1 day old
create or replace view vw_inactiveusers
(
"User Name",
"Application used",
"Logon Date-Time"
)
as
select
useridname,
logonapplication,
to_char( logondate, '"Logon at" DD-MON-YY HH:MI AM') "logondate"
from userid, userlogon, (select SYSDATE as NOW from DUAL)
where userid.userid = userlogon.userid
and 1 < (NOW - logondate)
and logoffdate is NULL
with read only
;
/
One thing to keep in mind if the durations you're looking at are over 24hr
the format string 'SSSSS' is in the range of 0 to 86399. so you'd have to
do the math.
--Paul
---------|^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|---------
Legitimate replies must remove spam from my address.
---------|____________________________________________________|---------
Space. The final frontier. These are the voyages of the starship
Enterprise. Its five year mission to explore strange new worlds...
Bob Weber <bweber_at_onramp.net> wrote in article
<01bca985$3dd14280$f964020a_at_baweber>...
> Hi,
>
> Can anyone help me with getting a resonable value when subtracting two
> timestamps??
> I'm using the AUD$ table and I wan to get the amount of time the session
> lasted so I'm taking the logoff tiem and subtracting it from the time
> stampstamp, both are datetime values. Unfortunately I can't seem to get
> seconds and minutes out of the result.
>
> For Example:
> SELECT
> to_char(SYS.AUD$.LOGOFF$TIME,'hh:mm:ss'),
> to_char(SYS.AUD$.TIMESTAMP,'hh:mm:ss'),
> (SYS.AUD$.LOGOFF$TIME - SYS.AUD$.TIMESTAMP) SessionTime
> FROM SYS.AUD$
> WHERE SYS.AUD$.TIMESTAMP>'15-AUG-97'
>
> I would appreciate any thoughts!!
> Thanks,
> Bob
>
>
Received on Fri Aug 15 1997 - 00:00:00 CDT
