FND User Login History

Description

Categories
Enginatics
Repository
GitHub
Audit history of application user logins.

EBS user logon audit is controlled by profile 'Sign-On:Audit Level'.
The most detailed audit level setting is 'FORM'.
Unfortunately, this audit tracks access to individual forms only, but not to different JSPs (HTML / OAF / ADF Pages).
As a workaround, the report SQL also joins to icx_sessions, which contains a record for each login (in fact, it also stores a record for each access to the login page before login. These records are marked with guest='Y').
The function retrieved from icx_session however, just shows the latest OAF function accessed by the user, not all individual JSP functions accessed within that session.

SQL 123 lines

Parameters 10

ParameterTypeList of values SQL
Responsibility NameLOV
frv.responsibility_name=:responsibility
Form NameLOV
ffv.form_name=:form
User NameLOV
fl.user_id=xxen_util.user_id(:user_name)
Logged in within DaysNumber
x.start_time>=sysdate-:days
Date FromDate
x.start_time>=:date_from
Date ToDate
x.start_time<:date_to+1
Restrict to incremental dataLOV
x.start_time>=
(
select
fcr0.actual_start_date
from
fnd_concurrent_requests fcr,
fnd_concurrent_requests fcr0
where
fcr.request_id=fnd_global.conc_request_id and
fcr.parent_request_id=fcr0.request_id
) and
fl.user_id<>(select fu.user_id from fnd_user fu where fu.user_name='SWITZERLAND')
Incremental Alert ModeLOV
x.start_time>=
(
select
fcr0.actual_start_date
from
fnd_concurrent_requests fcr,
fnd_concurrent_requests fcr0
where
fcr.request_id=fnd_global.conc_request_id and
fcr.parent_request_id=fcr0.request_id
)
Exclude User NameLOV
fl.user_id<>xxen_util.user_id(:exclude_user_name)
Include GeolocationLOV
select
'xxen_util.geolocation(''http://ip-api.com/json/'',x.clientip,''country'') country,'||chr(10)||
'xxen_util.geolocation(''http://ip-api.com/json/'',x.clientip,''city'') city,' text
from
all_tab_columns atc
where
atc.owner='APPLSYS' and
atc.table_name='FND_LOGINS' and
atc.column_name='CLIENTIP'