complex pl/slq procedure

[Clone05]

Hiya all,

Having a bit of a problem with a procedure.

We have a table that contains multiple fields, the three that I am interested in are call username, session and creation_date.

When a user logs in, a trigger inserts a row containing the users username (username), the date and time (creation_date) and the string 'STORESESSION' (session). When the user logs out, it inserts the users username (username), the date and time (creation_date) and the string 'LOGOUT' (session).

OK, here the bit that is bugging me. I need to create a procedure that when I input a start date and time, and a time period, shows me all the users that where logged on during that time (including any users that logged on/off). The query would need to list the username, the date/time of logged in and (if known) the date time of logged out.

Any ideas?

Thanks

Clone05

 

[SantaMufasa]

Clone,

Here is some SQL*Plus and SQL that should take care of business for you (Be sure to copy and paste into a file/script, the execute the script):

set verify off
accept period_beg prompt "Enter period start date/time ('yyyy-mm-dd hh24:mi:ss'): "
accept period_end prompt "Enter period end date/time ('yyyy-mm-dd hh24:mi:ss'): "
select username
, to_char(login,'yyyy-mm-dd hh24:mi:ss')login
, to_char(logout,'yyyy-mm-dd hh24:mi:ss')logout
from logaudit
where login between to_date('&period_beg','yyyy-mm-dd hh24:mi:ss')
and to_date('&period_end','yyyy-mm-dd hh24:mi:ss')
or
logout between to_date('&period_beg','yyyy-mm-dd hh24:mi:ss')
and to_date('&period_end','yyyy-mm-dd hh24:mi:ss')
/

If your problem is that you need this in a STORED PROCEDURE, then just take the SELECT statement and make it into a CURSOR. If you don't know how to do that, then please advise in a reply.

Dave
Sandy, Utah, USA @ 19:30 GMT, 12:30 Mountain Time

 

[Clone05]

Thank you Dave,

Unfortunately it’s not that easy, I didn't really explain it well and also the rules have just changed. I now no longer need to restrict it by a time period.

What I now need is a list of usernames and the date\time they logged on and the date\time they logged off.

So for example, user smith logged on at 11.30, logged off at 11.35, logged back on at 11.40 and then finally logged off at 12.30. Then I ran this report, it should bring back two logins for smith, one detailing him logging in at 11.30 and logging off at 11.35 and the second reporting him logging on at 11.40 and logging off 12.30

so

USERNAME CREATION DATE SESSION
SMITH 20/10/2003 11:30 STORESESSION
SMITH 20/10/2003 11:35 LOGOUT
SMITH 20/10/2003 11:40 STORESESSION
SMITH 20/10/2003 12:30 LOGOUT

would return something like

USERNAME LOGIN LOGOUT
SMITH 20/10/2003 11:30 20/10/2003 11:35
SMITH 20/10/2003 11:40 20/10/2003 12:30

I would appreciate any assistance you could give.

Thanks,

Clone05

 

[SantaMufasa]

Clone,

I disagree when you say, "...It's not that easy..."; in fact, it's even easier ! Unless I am mistaken, here is your code according to your specs:

select username
, to_char(login,'yyyy-mm-dd hh24:mi:ss')login
, to_char(logout,'yyyy-mm-dd hh24:mi:ss')logout
from logaudit
order by username, login;

¿Sí/No?

Dave
Sandy, Utah, USA @ 08:44 GMT, 01:44 Mountain Time

 

[SantaMufasa]

Clone,

Sorry, I see your point: the table has changed from what you first told me, so the LOGIN and LOGOUT are not two columns on the same AUDIT_LOGIN row...they are two separate rows. That, too, is fine. I'll have code for you in a few minutes.

Dave
Sandy, Utah, USA @ 08:49 GMT, 01:49 Mountain Time

 

[Clone05]

Dave,

Sorry, the login\logout are both recorded within one column, the Session coloumn. So the only way that I can see to extract the data, is to either join the tables or to run multiple searches/selects on them (ie return the username, creation date for all entries in the session column that equal STORESESSION and then use this to match the logouts againist, all entries in the session column that equal LOGOUT


Clone05

 

[SantaMufasa]

Clone,

Here is your "new, improved" code:

select	 login.username
	,to_char(login.creation_date,'dd/mm/yyyy hh24:mi:ss') login
	,to_char(logout.creation_date,'dd/mm/yyyy hh24:mi:ss') logout
from logaudit2 login,logaudit2 logout
where login.sess = 'STORESESSION'
  and login.username = logout.username
  and logout.sess = 'LOGOUT'
  and logout.creation_date =
	(select min(creation_date)
		from logaudit2 inner
		where creation_date > login.creation_date
		  and sess = 'LOGOUT')
/

USERNAME                       LOGIN               LOGOUT
------------------------------ ------------------- -------------------
SMITH                          20/10/2003 11:30:00 20/10/2003 11:35:00
SMITH                          20/10/2003 11:40:00 20/10/2003 12:30:00

But, there is a flaw in your data provisioning: you need to also store Oracle's unique sessionID with your data. Without it, you don't know for sure which "LOGOUT" matches which "STORESESSION". It is very reasonable to open multiple concurrent sessions with the following Login/Logout times:

11:30 - 11:50
11:45 - 11:48

But with the attributes you provide (sans SessionID), the only programmatical presumption we can make is that the 11:48 LogOut is for both the 11:30 and the 11:45 session.

So, although my code works, it can be reliable only if 1) you prevent concurrent sessions, or 2) you store the Oracle-generated SessionID in the table.

Dave
Sandy, Utah, USA @ 09:19, 02:19 Mountain Time

 

[Clone05]

Dave,

Thank you for this.

We do prevent users from having concurrent sessions, so this would be great.

Thank you

Clone05

 

[sem]

Another variant:


select distinct username,
decode(session, 'STORESESSION',creation_date,lag(creation_date) over (order by username) ) "IN",
decode(session, 'LOGOUT', creation_date, lead(creation_date, 1) over (order by creation_date)) "OUT"
from logaudit

Unfortunately without DISTINCT it doubles each session.

Regards, Dima

 

[sem]

Oops, played with windows and copied wrong query. Should be:

select distinct username,
decode(session, 'STORESESSION',creation_date,lag(creation_date) over (partition by username order by creation_date) ) "IN",
decode(session, 'LOGOUT', creation_date, lead(creation_date, 1) over (partition by username order by creation_date)) "OUT"
from logaudit

Regards, Dima