Project and Portfolio Management Practitioners Forum
cancel

Last Login Date of Users

SOLVED
Go to solution
Highlighted
bugra
Trusted Contributor.

Last Login Date of Users

Hello, I want to create a report which has information about last login date of users. I have approx. 310 users and I am using PPM 9.10. I want this for all 310 users.

 

How can I create this ?

 

 

User Name     Last Login Date

-------                -----------------------

A                       14/09/2011

B                      10/08/2010

4 REPLIES
shijubch
Respected Contributor.
Solution

Re: Last Login Date of Users

use this query

 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 (+)
group by nu.user_id, nu.username, nu.first_name, nu.last_name,
np.product_name, nu.creation_date
order by 2 desc

Jim Esler
Acclaimed Contributor.

Re: Last Login Date of Users

You can include the following if you want to limit the output to successful login attempts:

 

  and nla.success_flag(+) = 'Y'

Elux_Leslie
Regular Contributor.

Re: Last Login Date of Users

This is exactly what I need too but I keep getting the following error: 

 

SQL exception:
ROR-00923: FROM keyword not found where expected

 

Anyone else have issues?

bugra
Trusted Contributor.

Re: Last Login Date of Users

I am not sure this solution solves your problem but it may helpful.

 

Also, I encountered invalid character error in the first try. The problem is solved by deleting hidden last character after desc.