donderdag 25 februari 2016

Query to find last login date for a user/responsibility

The following query may help to find users that haven't logged in for a long time or which responsibilities are actually used in the system.  You may filter as well on the count or date to print only users who haven't logged in for a year or not more than 10 times, etc.
The first query shows you per user when he logged in last, how long ago that was, how many times he logged in and whether he's an employee who's not terminated yet ..

I use two views. The second also fetches the last responsibility used. Note that per login you can have multiple records in fnd_login_responsibilities, but you can also have none ..

create or replace view xxx_user_counts_v
SELECT      U.User_Id
, U.User_Name
,       PX.Full_Name
,       (SELECT Count (D.Responsibility_Id)
         WHERE  D.User_Id = U.User_id) Nr_of_Responsibilities
,       (SELECT   PS.Actual_Termination_Date
         WHERE  PS.Person_Id = U.Employee_Id
         AND      PS.Period_Of_Service_Id =
            SELECT Max (PS2.Period_Of_Service_Id)
            WHERE PS2.Person_Id = U.Employee_Id
        ) Actual_Termination_Date
,           To_Char (Max (L.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date
,           To_Char (Max (LR.Start_Time),'DD-MM-YYYY HH24:MI:SS') Last_Login_Date_Resp
,           Round ((Sysdate-Max (L.Start_Time))) Days_Since_Login
,           Count (L.Login_Id) Nr_Of_Logins
,           Max (L.Login_Id) Last_Login_Id
FROM      FND_USER          U
,           FND_LOGINS                  L
--,           FND_RESPONSIBILITY_VL       R
,       PER_PEOPLE_X        PX
WHERE       U.User_Id                          = L.User_Id (+)
AND         L.Login_Id                   = LR.Login_Id  (+)
--AND         LR.Responsibility_Id       = R.Responsibility_Id (+)
AND         (U.End_Date IS NULL OR U.End_Date > Sysdate)
AND     U.EMPLOYEE_ID         = PX.Person_Id (+)
GROUP BY  U.User_Name
,       U.User_Id
,     PX.Full_Name
,       U.Employee_Id

create or replace view xxx_user_counts_v2
select c.*
, (select rl.responsibility_name
   , fnd_login_responsibilities r
   where r.login_id = c.last_login_id
   and r.responsibility_id = rl.responsibility_id
   and r.login_resp_id =
     select max (r2.login_resp_id)
     from fnd_login_responsibilities r2
     where r2.login_id = r.login_id
   ) last_resp_used
from xxx_user_counts_v c

Now you can do queries like 

Number of users who never have logged in, but do have an authorization

select count(*) from xxx_user_counts_v2 where nr_of_logins = 0 and nr_of_responsibilities > 0;

Number of users who haven't logged in for half a year and who have authorizations

select count(*) from xxx_user_counts_v2 where Days_Since_Login > 180 and nr_of_responsibilities > 0; 

Users who's employee record have been terminated, but still have access

select count(*) from xxx_user_counts_v2 where actual_termination_date < sydate;

1 opmerking:

Opmerking: Alleen leden van deze blog kunnen een reactie posten.