Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2 rsolanki 1 
/* User Login Trail */
2 
SELECT DECODE( lt.ENUM_EVENT,
3 
	   		   1,  'LIMG_LOGON_SUCCESS',
4 
			   -1, 'LIMG_LOGON_FAIL',
5 
			   0,  'LIMG_LOGOFF',
6 
			   2,  'LIMG_SESSION_EXPIRE') AS event_icon,
7 
	   lt.USER_NAME,
8 
	   usr.USER_ID,
9 
	   usr.FULL_NAME,
10 
	   lt.CLIENT_IP,
11 
	   app.APP_ID,
12 
	   app.APPLICATION_NAME,
13 
	   lt.STAMP,
14 
	   lt.COMMENTS
15 
  FROM (
16 
 
17 
		/* Successful Login, Logoff and Session Expire */
18 
		SELECT lt.*
19 
		  FROM LOGIN_TRAIL lt
20 
		 WHERE lt.USER_NAME = :USER_NAME
21 
		   AND lt.ENUM_EVENT != -1
22 
 
23 
		UNION
24 
 
25 
		/* Login Fail from this user IP */
26 
		SELECT ltf.*
27 
		  FROM LOGIN_TRAIL ltf,
28 
		  	   (
29 
				SELECT ltf.CLIENT_IP
30 
				  FROM LOGIN_TRAIL ltf
31 
				 WHERE ltf.USER_NAME = :USER_NAME
32 
				   AND ltf.CLIENT_IP IS NOT NULL
33 
			   ) userip
34 
		 WHERE ltf.CLIENT_IP = userip.CLIENT_IP
35 
		   AND ltf.ENUM_EVENT = -1
36 
 
37 
  	   ) lt,
38 
  	   APPLICATIONS app,
39 
	   USERS usr
40 
 WHERE lt.APP_ID = app.APP_ID (+)
41 
   AND lt.USER_NAME = usr.USER_NAME (+)
42 
ORDER BY lt.STAMP DESC