Hi,
Is there anyone that can show me how to create a Quick Report that shows last login date and time of all Users in IFS10? I have asked a lot of my fellow consultants and no one seams to know how to create it.
- Community
- IFS Solutions
- Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)
- Quick report showing last login of all Users
Quick report showing last login of all Users
- January 21, 2022
- 22 replies
- 2989 views
22 replies
- Hero
- 217 replies
- January 21, 2022
One idea would be to add History Logging for LU FndClientLogon. On INSERT of fields like Directory_Id and Session_Created.
I just did that, logged off and back on, and my username (Directory_Id in that LU) was there along with when the session started (Session_Created).
So, if you set up logging then query History_Log and History_Log_Attribute, you can get the last session creation time by user.
You can configure this particular history to delete after X number of days, and your report would be for last login within that timeframe. If no user record in history, it means they did not log in within the last X days. You can LEFT OUTER JOIN Fnd_User to the History data, and make a parameter in the Quick Report for a specific user or wildcard list of users.
If you need more help I can form some queries next week…
Another idea would be to create a Custom Event on FND_CLIENT_LOGON and store login records in your own Custom LU. Then report from that. But in this case, History Logging may be a more elegant solution.
Good luck!
Joe Kaufman
- Hero
- 217 replies
- January 21, 2022
And one more thing: You probably only have to log DIRECTORY_ID, because ObjVersion will have a timestamp in it that matches when the session was created. That will simplify your join from Fnd_User to History_Log and History_Attribute_Log.
Thanks,
Joe Kaufman
- Hero
- 217 replies
- January 21, 2022
Here is a sample Quick Report query that asks for a wildcard-able User ID parameter (case-insensitive), and accounts for the user not entering anything (grabs everything in that case):
SELECT HLA.New_Value AS "User ID", MAX(TO_DATE(HLA.ObjVersion, 'YYYYMMDDHHMISS')) AS "Last Login"
FROM History_Log_Attribute HLA INNER JOIN History_Log HL
ON (HLA.Log_Id = HL.Log_Id) AND (HL.LU_Name = 'FndClientLogon')
WHERE (HLA.Column_Name = 'DIRECTORY_ID')
AND UPPER(HLA.New_Value) LIKE UPPER(NVL('&UserID', '%'))
GROUP BY HLA.New_Value
Works great! This makes sure you are only looking at History data for the FndClientLogon LU history, and only going after the DIRECTORY_ID field. Appears to run fairly quickly even though History_Log_Attribute is a bit sparse on indexes…
You can modify the above to join to FND_USER, or just use as-is to see all logins. Not sure if you want to see active users who have not recently logged in.
Let me know if this helps or if you have questions! I might implement this myself after seeing if anyone is interested...
Joe Kaufman
- Superhero (Partner)
- 566 replies
- January 21, 2022
- Hero
- 217 replies
- January 21, 2022
You need to enable Foundation1 visibility:

Then there appears a node for IFS Base Functionality, and FndClientLogon is under that. Configure the fields you want to grab on INSERT (just DIRECTORY_ID should suffice) and you are good to go!
Post if you have any other questions!
Joe Kaufman
- Ultimate Hero (Partner)
- 855 replies
- January 23, 2022
Hi Guys,
There’s an alternate to get the last login without history log using fnd_user_api.get_last_activity
A simple quick report to display last login of all users would be
select a.identity,
a.description,
a.active,
ifsapp.fnd_user_api.get_last_activity(a.identity) last_login
from ifsapp.fnd_user a
Cheers!
Damith
- Hero
- 217 replies
- January 23, 2022
Very nice! I will have to dig into that method and see how IFS knows last activity (must store it somewhere...)
Thanks,
Joe Kaufman
- Hero
- 217 replies
- January 23, 2022
Problem: looks like last activity is null for everyone. The method checks DBA_USERS, and the last_login field is null for everything. Any idea why that might be? I am checking our DEV environment…
Thanks,
Joe Kaufman
- Hero
- 217 replies
- January 23, 2022
(Just checked our PROD environment as well -- all last active values are null...)
Joe Kaufman
- Ultimate Hero (Partner)
- 855 replies
- January 24, 2022
(Just checked our PROD environment as well -- all last active values are null...)
Joe Kaufman
Hi Joe,
What authentication method are you using? seems that this only works for Oracle authentication.
Found this articles which might be helpful for you.
Last Login for user | IFS Community
List of past connected users in IFS | IFS Community
Cheers!
Damith
- Hero
- 217 replies
- January 24, 2022
I do not know what authentication we use -- it is just standard IEE login page. Is that Oracle auth? We are not using SSO in any way or any other sort of authenticator.
Thanks for the links! I think I’ll just stick with the History Log idea…
Thanks,
Joe Kaufman
- Do Gooder (Customer)
- 2 replies
- January 25, 2022
A simple quick report to display last login of all users would be.
SELECT t.user_id, max(r.modified_date) last_logon
FROM FNDRR_USER_CLIENT_PROFILE_TAB T, FNDRR_CLIENT_PROFILE_VALUE_TAB r
WHERE t.USER_ID not like 'IFS%'
and ordinal = 0
and fnd_user_api.Get_Active(user_id) = 'TRUE'
and r.profile_id (+) = t.profile_id
group by t.user_id
Thanks
Raj
- Hero
- 217 replies
- January 25, 2022
@Raj
Checking the Profile table -- nice!
One super-tiny wrinkle to using that so folks are aware -- the user’s Profile might not change when someone logs in (and stays in). I just logged in, and my “last logon” timestamp did not change. When I logged OUT, that is when it changed, as I assume that is when IEE saves my profile. The timestamp, in essence, isn’t really last logon”, then, but, “last time the user/s Profile was saved.”
That is a difference with the solution I present -- the timestamp in my method will be when the user last logged IN, while Raj’s solution is when the user last logged OUT (or did something else which changes the Profile). Just something to be aware of, each query has its strengths and weaknesses.
And something I should have noted previously -- neither of these methods will catch logins from Aurena. I just logged in there and neither query has a recent timestamp for the user I logged in under. Where might one find connection/login information for logins coming from Aurena?
Thanks,
Joe Kaufman
- Superhero (Partner)
- 334 replies
- January 25, 2022
Hi
Can you try below ?
select username,
last_login
from SYS.DBA_USERS
This works for database authentication.
- Hero
- 217 replies
- January 25, 2022
This query works in SQL Developer, but I get a “Table does not exist” error if I try to use this in a Quick report (or IAL Object).
Is there a way to make tables in the SYS schema available for use in a Quick Report, or am I doing something wrong?
The advantage of the query is that it shows last connection time for both IEE and Aurena -- so that is a big plus if we can get it working in a Quick Report (or IAL object which could then be queried by a Quick Report).
Thanks,
Joe Kaufman
- Superhero (Partner)
- 334 replies
- January 25, 2022
Hi
I think the problem is with the data type of the last login field, not the schema.
I just created a test quick report using the below query, converting the last_login to char, and successfully got the results. (I created and ran the report as app owner)
select username,
to_char(last_login)
from SYS.DBA_USERS
Thanks,
- Hero
- 217 replies
- January 25, 2022
You are right about the field causing issues, even under App Owner -- I also got your revised query to work in a Quick Report. Unfortunately, it only works for App Owner, so the Quick Report is not all that useful unless I wanted to start messing with GRANTs (which is likely a bad idea when dealing with “SYS.DBA” tables?).
The query is superior in all regards, especially since it takes both Aurena and IEE connections into account. But we do not usually deploy Quick Reports that only App Owner can run.
Any thoughts on other places to query that are available to regular users with more standard permission sets? We have lots of different ideas so far, but no silver bullet yet…
Thanks,
Joe Kaufman
- Hero
- 217 replies
- January 25, 2022
I found a more promising LU, FndSessionRuntime:
SELECT FSR.Fnd_User AS User_Id,
MAX(FSR.Last_Call) AS Last_Activity,
TO_CHAR(MAX(FSR.Last_Call), 'MM/DD/YYYY HH24:MI:SS') AS Timestamp
FROM Fnd_Session_Runtime FSR
GROUP BY FSR.Fnd_User
ORDER BY Last_Activity DESC
This appears to keep track of all activity, including logging in and logging off, so it is right up to the minute with a user being in the system. And it is updated from both IEE and Aurena client activity.
However, I am not sure when records are added, updated, and removed from this View, as it points to a table called “gv$session”, getting into some low-level data. I session records get cleaned up by a scheduled database task?, otherwise there would be way more than the 147 records I found when querying the View. So, the data would not be reliable to query directly, and I don’t know what history to log in order to capture latest activity.
The other problem is that FndSessionRuntime is not in the History Log Configuration tree even when Foundation1 objects are displayed. FndSession is there, but not FndSessionRuntime. The query under FndSession is very different from FndSessionRuntime, so I don’t know how reliable or persistent that is, either.
Anyone know how Session information is stored and how to keep it persistent? The Session seems to be the most reliable way to know someone is using the system, regardless of client, but I am not sure we can make that information available to something like a Quick Report...
Thanks,
Joe Kaufman
- Sidekick (Partner)
- 80 replies
- March 1, 2022
Hi Gents,
Did any of you came up with a solution for this. Would appreciate if shared.
- Sidekick (Partner)
- 80 replies
- March 3, 2022
Here is a sample Quick Report query that asks for a wildcard-able User ID parameter (case-insensitive), and accounts for the user not entering anything (grabs everything in that case):
SELECT HLA.New_Value AS "User ID", MAX(TO_DATE(HLA.ObjVersion, 'YYYYMMDDHHMISS')) AS "Last Login"
FROM History_Log_Attribute HLA INNER JOIN History_Log HL
ON (HLA.Log_Id = HL.Log_Id) AND (HL.LU_Name = 'FndClientLogon')
WHERE (HLA.Column_Name = 'DIRECTORY_ID')
AND UPPER(HLA.New_Value) LIKE UPPER(NVL('&UserID', '%'))
GROUP BY HLA.New_Value
Works great! This makes sure you are only looking at History data for the FndClientLogon LU history, and only going after the DIRECTORY_ID field. Appears to run fairly quickly even though History_Log_Attribute is a bit sparse on indexes…
You can modify the above to join to FND_USER, or just use as-is to see all logins. Not sure if you want to see active users who have not recently logged in.
Let me know if this helps or if you have questions! I might implement this myself after seeing if anyone is interested...
Joe Kaufman
- Superhero (Customer)
- 469 replies
- March 6, 2023
Hi Guys,
There’s an alternate to get the last login without history log using fnd_user_api.get_last_activity
A simple quick report to display last login of all users would be
select a.identity,
a.description,
a.active,
ifsapp.fnd_user_api.get_last_activity(a.identity) last_login
from ifsapp.fnd_user a
Cheers!
Damith
In fairness this just logs last activity rather than actual logon. I logged on at 9am this morning but this query shows the query I ran a few minutes ago not 9am.
Linda
Reply
Related Topics
Simple Excel pluginicon
Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)Simple Excel Plugin outputicon
Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)Simple Excel Plugin - Output File and Locationicon
Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)21R2 Excel report giving an erroricon
Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)Order report excel plug in erroricon
Framework & Experience (Infrastructure, Cloud, Integration, Dev Tools)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
Scanning file for viruses.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
OKThis file cannot be downloaded
Sorry, our virus scanner detected that this file isn't safe to download.
OKCookie 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
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.