IFS History User Login log

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.

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.

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.

Try this: select * from ifsapp.history_log_attribute

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.

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.

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.

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.

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.

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

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

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?

Dear @StevenHsu 

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

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!