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

Reply


Terms & ConditionsCookie settings