Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

User usage reports - do they exist?

SOLVED
Go to solution
Highlighted
Dana G Smith
Super Collector

User usage reports - do they exist?

I have been searching for a report or log that will give me user information such as number of times they have logged into the system for a period of time. I do know of the field "Last_Logon_Date" in KNTA_USERS table. Is there another table used for logging?
8 REPLIES
Sascha Mohr_1
Esteemed Contributor
Solution

Re: User usage reports - do they exist?

Hello Dana,
you will find this information in the table knta_logon_attempts.
Regards
Sascha
Dana G Smith
Super Collector

Re: User usage reports - do they exist?

Perfect! Thanks so much.
Dana G Smith
Super Collector

Re: User usage reports - do they exist?

Just a note on this table. Only the unsuccessful attempts are kept over time. Unfortunately, the succeeded attempts are only kept temporarily. Not sure of the pattern of what is kept. But it's only about a weeks worth of past signons.

This file is not going to help find the usage of a user over a certain period of time, unfortunately.
Greg_Shrout
Collector

Re: User usage reports - do they exist?

Don't know if this will help, but I have a "Users Not Using" query for this kind of analysis:
SELECT max(la.creation_date) LAST_LOGIN,
round(sysdate - max(la.creation_date)) DAYS_FROM_LOGIN,
u.authentication_mode,p.product_name LICENSE_NAME, (u.first_name || ' ' || u.last_name) FULL_NAME,
u.username USERNAME,u.email_address,
u.user_id USER_ID,
u.creation_date CREATION_DATE
FROM KNTA_USERS u, KNTA_LOGON_ATTEMPTS la,
knta_products p, knta_user_products up
WHERE up.user_id=u.user_id
AND ((u.end_date is NULL) OR (u.end_date > sysdate))
and p.product_id = up.product_id
AND u.USER_ID = la.USER_ID (+)
GROUP BY u.user_id, u.username, u.email_address,u.first_name, u.last_name, u.authentication_mode, p.product_name, u.creation_date
order by days_from_login desc
Dana G Smith
Super Collector

Re: User usage reports - do they exist?

Thanks. This is similar to a query I use that will group users that have signed on < 30 days, < 60 days or over 60 days ago.

I really am looking for information that will tell me that resource managers are in the tool on an average of 99 times month. Unless there is a log that keeps track of all the successful times a user has signed on, I don't believe I can gather this data.
Sascha Mohr_1
Esteemed Contributor

Re: User usage reports - do they exist?

Hello Dana,
I spent some time in the DB, the table I mentioned seems to be cleaned up by PPM periodically and I don't know a server.conf paramter to control this.
Maybe you can derive the information you need from some transaction tables in PPM (like kcrt_transactions). This way you won't capture "readers", of course.
MoteSantosh
Occasional Advisor

Re: User usage reports - do they exist?

Hi Smith,

If you set the fallowing parameters in the server.conf file you can gather more information on Logon Attempts.

ENABLE_LOGON_ATTEMPTS_CLEANUP Default: TRUE OR FALSE

LOGON_ATTEMPTS_CLEANUP_INTERVAL Default: 18000

DAYS_TO_KEEP_LOGON_ATTEMPT_ROWS determines how many days to keep
records of all logon attempts. Default: 14 Days

Note: setting the parameter to large numbers will degrade the performance.

You can find more information on this parameters in Sysadmin.pdf doc.

Thanks
Santosh
Dana G Smith
Super Collector

Re: User usage reports - do they exist?

unless I change the setting to retain data more than 14 days, the data is not available.

Closing discussion
//Add this to "OnDomLoad" event