Highlighted
Absent Member.
Absent Member.
668 views

Last Login Date of Users

Jump to solution

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

0 Likes
1 Solution

Accepted Solutions
Absent Member.. Absent Member..
Absent Member..

Re: Last Login Date of Users

Jump to solution

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

View solution in original post

4 Replies
Absent Member.. Absent Member..
Absent Member..

Re: Last Login Date of Users

Jump to solution

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

View solution in original post

Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Last Login Date of Users

Jump to solution

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

 

  and nla.success_flag(+) = 'Y'

Highlighted
Absent Member.
Absent Member.

Re: Last Login Date of Users

Jump to solution

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?

0 Likes
Highlighted
Absent Member.
Absent Member.

Re: Last Login Date of Users

Jump to solution

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.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.