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

HP PPM 7.5, user last login / logon report

Highlighted
CJ301
Acclaimed Contributor

HP PPM 7.5, user last login / logon report

newbie question, how can I generate a report that has the user name and their last login date? This is so I can figure out who uses PPM a lot versus who don't really need access.
5 REPLIES
Sascha Mohr
Esteemed Contributor

Re: HP PPM 7.5, user last login / logon report

With an SQL like:
SELECT USERNAME, FULL_NAME, LAST_LOGON_DATE
FROM knta_users_v
WHERE ENABLED_FLAG = 'Y'
ORDER BY LAST_LOGON_DATE DESC
Raj Ghimire
Occasional Visitor

Re: HP PPM 7.5, user last login / logon report

I also sometimes utilize a query like below to check who haven't logged into the application for a while and wasting the liceses. Might be of some help for you as well. Check it out:
---------
SELECT
distinct KU.USERNAME,KLA.CREATION_DATE Last_Logged_On
FROM
knta_users ku, knta_logon_attempts kla
WHERE
KU.USER_ID = KLA.USER_ID
AND KU.END_DATE is null
and KLA.CREATION_DATE < sysdate-365
order by KLA.CREATION_DATE asc
-------
Also, if you need to check on pretty recent activity, you could utilize "Server Logon Report" which can be run through Workbench under Sysadmin->Server Tools->Admin Tools.
Thanks.
-Raj
Sascha Mohr
Esteemed Contributor

Re: HP PPM 7.5, user last login / logon report

Hi,
we're using quite something similar as Raj and for the same reason. A report automatically deactivates users who haven't logged in for x months (scheduled wekly).
Chandru M
Occasional Contributor

Re: HP PPM 7.5, user last login / logon report

Hi,

Please find below query which will be useful to generate a report. Kindly let me know if this is useful.

SELECT MAX (nla.creation_date) last_login,
ROUND (SYSDATE - MAX (nla.creation_date)) days_from_login,
np.product_name product_name,
(nu.first_name || ' ' || nu.last_name) full_name,
nu.username username, nu.user_id user_id,
nu.creation_date creation_date
FROM knta_users nu,
knta_logon_attempts nla,
knta_user_products nup,
knta_products np
WHERE nup.user_id = nu.user_id
AND ((nu.end_date IS NULL) OR (nu.end_date > SYSDATE))
AND np.product_id = nup.product_id
AND nu.user_id = nla.user_id(+)
-- and (np.product_name like '%Change Management%'
-- or
-- np.product_name like '%Demand Management%')
GROUP BY nu.user_id,
nu.username,
nu.first_name,
nu.last_name,
np.product_name,
nu.creation_date
ORDER BY 2 DESC

Regards,
Chandru
CJ301
Acclaimed Contributor

Re: HP PPM 7.5, user last login / logon report

Thanks for all the replies. I'm going to give them all a try.

//Add this to "OnDomLoad" event