Quick report showing last login of all Users

@KUNGTOM 

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

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

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

@sutekh137 Where in the History Log Configuration can I enable the logging on FndClientLogon?

@matt.watters 

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

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

Very nice! I will have to dig into that method and see how IFS knows last activity (must store it somewhere...)

Thanks,

Joe Kaufman

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

(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

@dsj 

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

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

@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

Hi @KUNGTOM ,

Can you try below ?

select username,

last_login

from SYS.DBA_USERS

This works for database authentication.

@ThushAsanka

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

Hi @sutekh137 ,

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,

@ThushAsanka

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

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

Hi Gents,

Did any of you came up with a solution for this. Would appreciate if shared.

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

@sutekh137 Thanks for details. It is very helpful

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

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

How do you call API functions in the IFS cloud version?