Skip to main content
Question

IFS History User Login log

  • September 18, 2024
  • 15 replies
  • 499 views

Forum|alt.badge.img+4

Hi all,

I would like to retrieve the login history records for users.
I've gone through some discussions in this community but haven't been able to figure it out yet.

I've already  set up in the window "Set History Log", but I'm unsure how to access the records.

I want to know where I can find the records after completing the setup? or I need to set in other window?

15 replies

Forum|alt.badge.img+5
  • Do Gooder (Customer)
  • 38 replies
  • September 18, 2024

Hi @StevenHsu ,

You can reach this data with PL/SQL,

select x.time_stamp,
       substr(x.keys, 1, instr(x.keys, '^') - 1),
       x.history_type,
       y.old_value,
       y.new_value
  from history_log x
  left join history_log_attribute_tab y
    on x.log_id = y.log_id
 where lu_name = '&LU_NAME'
   and y.column_name = '&COLUMN_NAME'
   --and x.username = 'XXX'
  -- and trunc(x.time_stamp) = to_date('03/01/2022', 'dd/mm/yyyy')

 

Best regards.


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 19, 2024

Hi @nafismertapaydin ,

It seems that I can't access the "history_log_attribute_tab" table.
An error message appears, as shown in the screenshot below.

 


Forum|alt.badge.img+5
  • Do Gooder (Customer)
  • 38 replies
  • September 19, 2024

Hi @StevenHsu ,

This is a warning about the permission set error.

You need to grant view permissions for this table to the user who is executing the query.


Link
Superhero (Customer)
Forum|alt.badge.img+23
  • Superhero (Customer)
  • 1265 replies
  • September 19, 2024

Try this: select * from ifsapp.history_log_attribute


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 19, 2024

Hi @StevenHsu ,

This is a warning about the permission set error.

You need to grant view permissions for this table to the user who is executing the query.

Hi @nafismertapaydin ,

Yes, I understand. From other posts, I learned that only IFSAPP can access views with '_tab' in their name, while regular users cannot. I’m curious to know how you managed to execute this.


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 19, 2024

Try this: select * from ifsapp.history_log_attribute

Hi @Link ,

Yes, I can access this table, and I've checked the data ,

but it seems there isn't any data resembling user login records inside.

 


Link
Superhero (Customer)
Forum|alt.badge.img+23
  • Superhero (Customer)
  • 1265 replies
  • September 19, 2024

Try this: select * from ifsapp.history_log_attribute

Hi @Link ,

Yes, I can access this table, and I've checked the data ,

but it seems there isn't any data resembling user login records inside.

 

Did you activate the logs for your table?

If yes, do any changes.

 


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 20, 2024

Hi @Link ,

I've currently only set up the part shown in the screenshot I attached .

When you mention enabling logs for the table, are you referring to this?

Or is there anything else I need to configure? I would appreciate your guidance.

 


Forum|alt.badge.img+5
  • Do Gooder (Customer)
  • 38 replies
  • September 20, 2024

Hi @StevenHsu ,

Did you try search below screens?

1.Solution Manager\Monitoring\History\History Log

2.Solution Manager\Monitoring\History\History Log Report

Best regards.


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 20, 2024

Hi @nafismertapaydin ,

Thanks , I’ve found log of FND_CLIENT_LOGON as the screenshot I attached .

I tried searching for my own data, but I didn’t see anything resembling login times. Although there’s a 'Time' field displayed above, it’s impossible that I logged into IFS at 5 AM since that’s before my work hours.

So, I’d like to ask if additional configurations are needed to capture users' login times.
Thank you for your help.


Link
Superhero (Customer)
Forum|alt.badge.img+23
  • Superhero (Customer)
  • 1265 replies
  • September 20, 2024

Dear @StevenHsu

yes, it is right.

But I think your process isn’t right.

Use the history_log_tab in your query. And the table name should be FND_CLIENT_LOGON_TAB there.

I reccomend you to crate an IAL and join these tables:

  1. HISTORY_LOG_ATTRIBUTE
  2. IFSAPP.HISTORY_LOG

 


Link
Superhero (Customer)
Forum|alt.badge.img+23
  • Superhero (Customer)
  • 1265 replies
  • September 20, 2024

Here an IAL example for the history log table:

Select hl.log_id,
hl.lu_name,
hl.table_name,
hl.time_stamp,
hl.keys,
hl.history_type,
hla.column_name,
hla.old_value,
hla.new_value,
hl.username,
IFSAPP.FND_USER_API.Get_Description(hl.username) AS "DESCRIPTION",
hla.objversion AS "OBJDATE"
From IFSAPP.HISTORY_LOG hl
Right Join IFSAPP.HISTORY_LOG_ATTRIBUTE hla
On hl.log_id = hla.log_id
Order by hl.log_id

 


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • September 23, 2024

Hi @Link ,

I’ve created an IAL Object and used your SQL but when I deploy it,

it shows an error message like attached below

But it can work when I use this SQL statement in the window SQL Query Tool.

 

Could it be that the error is due to the settings in my parameters or my component?

Could you provide some guidance on how to solve this issue?


Link
Superhero (Customer)
Forum|alt.badge.img+23
  • Superhero (Customer)
  • 1265 replies
  • September 30, 2024

Dear @StevenHsu 

you start the name of the IAL with a number. Please use a character like C_HISTORY_LOG.


Forum|alt.badge.img+4
  • Author
  • Sidekick (Customer)
  • 17 replies
  • October 21, 2024

Hi @Link ,sorry for replying to you so late. 
    I achieved my goal by following this article 
    https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/quick-report-showing-last-login-of-all-users-17753
    

select a.identity,
       a.description,
       a.active,
       ifsapp.fnd_user_api.get_last_activity(a.identity) last_login
    from ifsapp.fnd_user a


    This SQL script provides the record of the user's last login. 
    My supervisor approves of it.

Regarding the IAL part, I'll make time to try it out later. Thanks again for your help!