Skip to main content
Solved

Is there an application table to track user login?

  • December 6, 2022
  • 3 replies
  • 754 views

Forum|alt.badge.img+2
  • Do Gooder (Employee)
  • 1 reply

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?

Best 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

View original
Did this topic help you find an answer to your question?

3 replies

NickPorter
Superhero (Customer)
Forum|alt.badge.img+18
  • Superhero (Customer)
  • 331 replies
  • December 6, 2022

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


CallumW
Superhero (Partner)
Forum|alt.badge.img+15
  • Superhero (Partner)
  • 128 replies
  • Answer
  • December 6, 2022

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


Forum|alt.badge.img+1

Hi, 

How do we track this in IFS Cloud? 

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

Best regards, 

Kasuni/


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings