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?
Solved
Is there an application table to track user login?
+2Best answer by CallumW
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
+18This 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
+15Hi,
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
+1Hi,
How do we track this in IFS Cloud?
If anyone know please let me know. Thank you :)
Best regards,
Kasuni/
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.