Skip to main content

One of our App10 customers has a requirement to track user logins. This is part of an exercise, where they are looking to find out inactive accounts. Is there a way that we can fulfil their requirement through a SQL query (eg:- fnd_Sessions,Fnd_Client_Logon_Tab) without enabling DB auditing?

This has been asked and answered a lot in different ways before, and the exact answer varies depending on Oracle version and authentication method (AD/Azure or DB auth).

Suggest you search the Community. Here’s one example:

HTH,

Nick

Hi,

I use the following query.

SELECT a.identity "User ID",
a.description "User Name",
NVL(TO_CHAR(TRUNC(IFSAPP.FND_USER_API.Get_Last_Activity(a.identity)),'DD/MM/YYYY'), 'NEVER') "Last Login"
FROM IFSAPP.FND_USER a, SYS.DBA_USERS b
WHERE a.active = 'TRUE'
AND a.identity = b.username
AND b.DEFAULT_TABLESPACE = 'USERS'
AND (IFSAPP.FND_USER_API.Get_Last_Activity(a.identity) <= ADD_MONTHS(SYSDATE,-6) OR IFSAPP.FND_USER_API.Get_Last_Activity(a.identity) IS NULL)
ORDER BY IFSAPP.FND_USER_API.Get_Last_Activity(a.identity) ASC

As Nick has already mentioned it does vary on the type of authentication method.

The query above will work for DB authentication only.

 

I only check the previous 6 months, change this number or remove the line to change the time period you are checking for.

 

Callum

Hi, 

How do we track this in IFS Cloud? 

If anyone know please let me know. Thank you :)

Best regards, 

Kasuni/

Reply


Terms & ConditionsCookie settings