Introduction
User Log Reports provides the detailed information about the users’ login details along with their history. These reports are more accurate and also provides the details of the user, their logon time, logoff time, the computer from which they logged on, the domain controller they reported, etc., along with their logon history. It also provides the details of Currently Logged On Users and Frequently Logged on Users.
To Capture User Login Details
The following information can be captured in the Database.
- Login Date
- Login Time
- Session Id
- System IP Address
- Host Name
- User Name
Steps To Follow
Step1:
Table Creation
<<table_name>> (column_names) {Based on the column Requirement}
Step2:
Create Page Hidden Items P0_SESSION_ID, P0_USER_ID for getting Session id as well as User id.
P0_SESSION_ID :
SELECT v (‘session’)
FROM DUAL
Step3:
Creation of Insertion Process in the User Login Page
On Click of Login Button or Enter Button in Login Page the following process has to be created.
Insertion Process:
DECLARE
ln_session_id NUMBER;
lv_ip_address VARCHAR2 (30);
lv_login_date VARCHAR2 (100);
lv_login_time VARCHAR2 (50);
lv_user_role VARCHAR2 (50);
lv_current_schema VARCHAR2 (50);
lv_host VARCHAR2 (50);
BEGIN
/* To Select Session id */
BEGIN
SELECT v (‘session’)
INTO ln_session_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
‘Error on Selecting Session Id’ || SQLERRM
);
END;
/*To Select Login Date and Login Time*/
BEGIN
SELECT TO_CHAR (SYSDATE, ‘dd-Mon-yyyy’),
TO_CHAR (SYSDATE, ‘hh12:mi:ss PM’)
INTO lv_login_date,
lv_login_time
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20003,
‘Error on Selecting Log Date and Time’
|| SQLERRM
);
END;
/* To Select Ip Address */
BEGIN
SELECT SYS_CONTEXT (‘USERENV’, ‘IP_ADDRESS’)
INTO lv_ip_address
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20005,
‘Error on Selecting Ip_address’ || SQLERRM
);
END;
/* To Select Host Name*/
BEGIN
SELECT SYS_CONTEXT (‘USERENV’, ‘HOST’)
INTO lv_host
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20005,
‘Error on Selecting Host’ || SQLERRM
);
END;
/*To Select Current Schema */
BEGIN
SELECT SYS_CONTEXT (‘USERENV’, ‘CURRENT_SCHEMA’)
INTO lv_current_schema
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20005,
‘Error on Selecting Current Schema’
|| SQLERRM
);
END;
/*To Select User Role*/
BEGIN
SELECT user_role
INTO lv_user_role
FROM homes_fnd_users
WHERE user_id = :p0_user_id;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-2007,
‘Error on Selecting User Name’ || SQLERRM
);
END;
/*Insert Data to Corresponding Table*/
BEGIN
INSERT INTO homes_inv_user_log_details
(session_id, user_role, user_name,
current_schema, HOST, log_id,
login_date, login_time, ip_address
)
VALUES (ln_session_id, lv_user_role, :p0_user_id,
lv_current_schema, lv_host, homes_log_details.NEXTVAL,
lv_login_date, lv_login_time, lv_ip_address
);
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20006,
‘Error on While Inserting records’
|| SQLERRM
);
END;
END;
Step4:
Interactive Report Creation
Create a new page with Interactive Report that shows all the Login Details.
To Capture User Logout Details:
The following information will be captured in the Database.
- Logout Date
- Logout Time
- Session Id
- System IP Address
- Host Name
- User Name
Step1:
Creation of New Image
Upload designed or downloaded images to Apex Workspace.
Step2:
Button Creation
In Edit Page remove Navigation Bar Entry Label, instead create a new Button in the Global Page (Zeroth Page) in your Apex Application.
- Now create a new Region (Template=> No Template & Display Point => Page Template Region Position 8)
- Create new Buttons (which is used in application) in this Region only. This Button will be displayed throughout the application.
- Change the button style => Images.
- Call the image from Apex Workspace,
#WORKSPACE_IMAGES#<<workspaceimage_name>>
Region Creation
Button Creation
Navigation Bar Logout:
Set Navigation Bar Logout Condition as NEVER.
Step 3:
Creation of Dynamic Action
Since it is not possible to create a Process in Global Page, create Dynamic Action.
- Execute PLSQL Code:
Create the following action On Click of Logout Button.
DECLARE
lv_logout_date VARCHAR2 (100);
lv_logout_time VARCHAR2 (100);
BEGIN
/* To Select Logout Date and Logout Time */
BEGIN
SELECT TO_CHAR (SYSDATE, ‘dd-Mon-yyyy’),
TO_CHAR (SYSDATE, ‘hh12:mi:ss PM’)
INTO lv_logout_date,
lv_logout_time
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
‘Error on Selecting Logout Date & Time’
|| SQLERRM
);
END;
/* Update Logout Date and Logout Time*/
BEGIN
UPDATE homes_inv_user_log_details
SET logout_date = lv_logout_date,
logout_time = lv_logout_time
WHERE session_id = :p0_session_id;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20003,
‘Error on Updating Records’ || SQLERRM
);
END;
END;
- Execute Java script Code:
window.location=’&LOGOUT_URL.’
Step4:
Create a new page with Interactive Report that shows all the Login Details.
Session Out Time Capture :
Paste the below code into
Security Attributes = > Database Session.
Execute PLSQL Code:
BEGIN
/* To Select Session Out Date & Session Out Time*/
BEGIN
SELECT TO_CHAR (SYSDATE, ‘dd-Mon-yyyy’),
TO_CHAR (SYSDATE, ‘hh12:mi:ss PM’)
INTO lv_session_date,
lv_session_time
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20001,
‘Error on Selecting Logout Date’ || SQLERRM
);
END;
/* Update Session Out Date & Session Out Time When Session is Out*/
BEGIN
UPDATE homes_inv_user_log_details
SET session_date = lv_session_date,
session_time = lv_session_time
WHERE session_id = :p0_session_id;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20003,
‘Error on Updating Records’ || SQLERRM
);
END;
END;
Call To Action
For Oracle apex development and customization please do visit us..Our company website https://doyensys.com/
Conclusion
Capture login,logout,session,system details for user log report is
Possible by following above steps.
