E9.2 Getting the Last Logon and Logoff Details in 9.2 (F9312 / F9312T)

[richslaney]

Hi,
Were are on JDE EnterpriseOne 9.2 (9.2.6.3) on OCI and I have a Development issue and I wondered if anyone could help or has any ideas.

I've created a detailed 'User Password / Role / Object Inquiry' using multiple Power Forms and Subforms to provide a nice front end for CNC and Management to look at User related details and to also satisfy Auditor questions etc.

One of the elements of Information wanted to display in the Grid is the 'Last Logon Date and Time' and the 'Last Logoff Date and Time'to help decide if the user should be disabled etc.

I know F9312 has all the sign on history but this isn't a good fit to add into my Business View as there are many, many records per Action per day and I need the last Logon Time and the Last Logoff Time which are again spread over multiple records.

To be honest, I find it odd that JDE doesn't simply have a this info to the User in say the F0092, F00921 or F00922 or F98OWSEC.

So, I created a business function to get this at runtime of my Inquiry from F9312, but the performance was not acceptable when running though the Grid to load the forms. Also, we want it to be searchable so I wanted this to be in the Business View instead of fetching the value.

My solution was to create a new table which for a 'User' held a single record which contained the Users 'Last Logon Date and Time' and 'Last Logoff Date and Time' and therefore could be 'joined' into my business view. I created a UBE to also update my new table for all records at once, and then the Inquiry showed this info as required nicely, but I need the update of this data to be dynamic.

So, I then added Insert and Update Triggers on F9312 to call my new Business Function to get the info from F9312 and update / insert the details in my new table when F9312 is being updated.

Issue is that if I 'sign off' and 'sign on', and look in my new Table and there are no records created although F9312 has been updated to contain the information.

Therefore, I assume that F9312 is NOT being updated inside JDE and therefore the JDE triggers are not being executed.
Is this the case.. Is the logon Process outside of the JDE functionality and therefore Triggers in F9312 are pointless?

What about the F9312T as well? Does anyone know when that is created as that also seems to be updated with pretty much identical info a F9312 so wondering if it's created at the same time as F9312 or when inside JDE so maybe triggers may work?

I have turned on Debug logging and I don't see anything for 'F9312' or 'F9312T' in the logs again leading me to believe this is being done outside of the JDE infrastructure.

If there another function / process in JDE I could try to call my BSFN's to create my records when a user logs on instead of a Trigger on F9312?
I need something the User always does when logging on, as I want the load (tiny) of generating the Users 'Last Logon Date and Time' and 'Last Logoff Date and Time' to be on each user instead of having to process many thousands of records.

Open to suggestions (avoiding DB Triggers if I can) from you clever lot.

Many thanks,

Rich Slaney.
EnterpriseOne Developer

 

[jsebert]

https://www.jdetips.com/doclib/previews/1_security%20history%20for%20e1.pdf

Is security history turned on? We get over 60,000 records a day logged here. Be careful.

 

[JohnDanter2]

Hey Rich (yes it's me oooow)

Have you enabled object usage tracking? You're on 9.2 so do have it.
F98911 could help? OUUSER and then OUUTIME could help you see last time that a given user ran anything.
Not exactly what you want but close enough and free
There's F98912 too, maybe there is a 'log off' BSFN you could look for

https://www.oracle.com/webfolder/technetwork/tutorials/jdedwards/OBE/objectusagetracking/objectusagetracking1.html

 

[MFreitag]

So, I then added Insert and Update Triggers on F9312

i get goosebumps and a shiver reading this PLEASE. DON'T.

...but anyway, your business requirement isn't all too new in our world i guess.

I would probably say goodbye to an interactive and real-time view and would probably try to steer this into a daily or even weekly update. Spontanously maybe have a report or even orchestration search for a users last logon time in F9312 in a nightly or weekend run and write it to either a custom table or maybe one of the ULULnn colums in F0092.

Of course i don't know your environment, business or retention policies for data so this is just a quick idea. Never had good experiences with data hoarders vs. interacrtive do-it-all applications. This will never turn out good

 

[Kim Schmidt]

You could also consider adding an index to F9312 and/or F9312T using the primary key columns, but with the time-related columns in descending order. No need then to do a table or index scan through the whole thing. A Select/Fetch Next using that index should return your answer on the first fetch. Maybe even a simple Fetch Single may work. Just wait to deploy the new index until after hours if your table has tens of millions rows ;-)

 

[richslaney]

Hi all,
Thanks for your suggestions. I had confirmation from JDE on the Oracle Forum that the F9312 / F9312T are updated from the Foundation and not the Middleware and therefore outside JDE, hence why the Triggers aren't being executed. I have therefore removed the Triggers from F9312 and restored the table back to original.

I did create a UBE to build my User Last Logon / off Table as part of the design but it's quite slow due to the amount of data and the pretty rubbish design of the P9312 / F9312T. This was driven by the User (F0092) and then a new C BFSN I wrote was getting the Last Logon and Logoff details from F9312 and then I was updating my Table.

This is why I went for a Trigger to make it real time and shifting the volume from a single massive file lookup to a tiny one (my C BSFN works in 0.015 seconds) by USER when they Logon.

I've also created another UBE which is driven by a view over P9312T joined F00491 on the Environment as this vastly reduces the records returned and then I only select in the UPMJ = Today. This means I have a smaller UBE which can call my BSFN on a Level Break on the User.

So, it's not real time as I'd planned, but I can now run the Big update UBE at night or in the early hours, and maybe my single Day based UBE a few times per day so at least it's accurate to maybe hours per day... Good news I wrote a swanky new Task Scheduler which and I can put it them on that.

Thanks again for your help, it's appreciated.

(Nice to hear from you Mr Danter... Ooooooow)

 

[mdalton]

We get this data through the JMX on the WebLogic servers using a Logstash agent. If you have monitoring tools in-house you should take a look at their ability to monitor the JVM and JMX. There are a lot of options on the market in this space.

 

[BOster]

The risk/reward for login info is probably not worth the risk of this solution, but...

If you really want to capture real-time info for logon/logoff you could modify the BSFNs jdeInitEnvBSFN (B0000182) and jdeFreeEnvBSFN (B0000183). Both of these are called whenever a user session is created/destroyed. I lazy load some stuff that persists in memory for the length of the user's session that needs to be free'd during logoff so I modified jdeFreeEnvBSFN to do just that. Mod has been in place for almost 15 years with no ill effects (that I know of) . If you choose to go this route just be very, very, very careful. For example if you write to a table you need to make sure no errors are thrown and the process doesn't stop if something goes wrong - obviously a bug that causes something like a segfault would be really really bad. In fact if I were doing this (and younger, less risk adverse me would absolutely do this only because it could be done ) I would probably put some type of runtime kill switch in the jde.ini file to enable/disable it. In my case the risk was less because I only modified jdeFreeEnvBSFN, so if something were to go horribly wrong, at least its happening during session termination and not preventing the session from being created in the first place.