Last Login for user

If ypu use database authentication, you should be able to create history login for FND_CLIENT_LPGON_TAB. It's this the table you used previously and caused problems?

If you are using Oracle Authentication and Audit Trail enabled, you could audit the logins. See https://minimalistic-oracle.blogspot.com/2013/11/how-to-enable-audit-on-create-session.html

To audit the logins:

audit create session whenever successful

(If you also want to audit failed login attempts):

audit create session whenever not successful

Then you could query the table dba_audit_trail for the information.

Adding some more to Sampath's reply,

• If you are using dba_audit_trail from DB level, The initialization parameters of the audit facility of Oracle can be set from "audit_trial" parameter,

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

•  Command to begin auditing connects (login) attempts is:

AUDIT SESSION;
AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

Disable Session Audit

NOAUDIT SESSION;

• Note to purging the logs time to time as well since this could fill up the datafile usage

Dependent how far you want to “look back” in time you could use fnd_session_runtime.

Seems to store a few days. 

select max(creation_time)
from fnd_session_runtime p
where p.fnd_user like '&USER'

Thanks all! We are using Azure AD for logon, does that affect the solutions you have provided? 

We need last login date, independent of when this was. 

@SORBJAHAU Azure AD keeps various logs. See if this helps: Sign-in activity reports in the Azure Active Directory portal.

For a full list of last logins in Apps9 and Apps10 you can simply use the following… I don’t believe any specific auditing needs to be turned on to enable this:

select username, last_login from SYS.DBA_USERS where account_status='OPEN' order by last_login desc;

However, based on the Author’s later reply the above query will not track Azure AD authenticated account logins.

Nick

We are also using Azure AD with conditional access to make our users authenticate with MFA when not on our known networks.

I use the Insight reports for the Enterprise Apps in AzureAD portal.

A user might touch one of many areas within the system. I attempted to capture all of these possibilities inside of one query, explicitly excluding the application owner, the IFS% users, and members of a particular user group.

Hi Rusiru,

FND_CLIENT_LOGON_TAB only works in the case of IEE. Can you please tell if there is any table that logs Aurena logons as well?

Regards,

Sahil Gupta

How can we get aurena logs. Getting from Azure is not so logical. 

Regards

Pankaj

Hello, 

Can you suggest if there is any parameter we can map for Audit log for Aurena logins.

Regards

Pankaj

We were doing this - but after update to Oracle 19C it all seems to have fallen over - the table has not been updated since, other than for IFSAPP and other system users.

Very strange

This changed with 12.2.

LAST_LOGIN was introduced with 12.1.0.2.

https://docs.oracle.com/database/121/REFRN/GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0.htm#fnsrc_d1205367e629

Starting with 12.2, when using proxy user authentication, only the proxy user will get their timestamp updated. With IFS Applications, that means IFSSYS will stay current, while the end user account will not get updated. That’s useless for us.

https://jolliffe.hk/2018/09/17/do-you-come-here-often-12-2-change-in-behavior-for-dba_users-last_login-with-proxy-authentication/

Also, as a side note, that column is “not populated when a user connects to the database with administrative privileges, that is, AS { SYSASM | SYSBACKUP | SYSDBA | SYSDG | SYSOPER | SYSRAC | SYSKM }.”

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_USERS.html#GUID-309FCCB2-2E8D-4371-9FC5-7F3B10E2A8C0

Is there a way to do this on Managed Cloud and without SSO enabled?

Morning @Sampath Sitinamaluwa @Charith Epasinghe @Rusiru Dharmadasa 

We are trying to setup IFSAPP usage report to see who and when used this account to access IFS in Apps 10? 

We had this working in Apps 8 by using IFSAPP.SERVER_LOG to capture logins via IFS client and DBA_AUDIT_TRAIL for non-IFS logins; so logins to database using tools such as PL/SQL.

SERVER_LOG is no more in Apps 10, so trying to replace this functionality with an equivalent in Apps 10.

DBA_AUDIT_TRAIL doesn’t show detail for all logins irrespective if through IFS client or direct to database. If through IFS, it seems to hide between the MW server which makes the connection so not possible to see the actual user who is logging in using IFSAPP. 

Note: The user executing the quick report will not have Administrator system privilege.

Please can you provide your guidance for best approach.

Thanks

Shaun

Hi @NickPorter in app8 is there a way we can get the last login date? last login is not available in SYS.DBA_USERS in app8. Thank you in advance.

You can use the Login Events form (iam_login_event_tab) in IFS Cloud for SSO users. It records the directory ID with a LOGON event type. 

This records the IAM clients also. 

Do you know how long the entries will persists in this table? Does this data reflect who is currently logged in?

I am also testing this. Looks to be controlled by the following system parameter. Defaults to 3 days only. I need it to be kept for 90 days. So need to extend the period.