Query to Pull out Login and LogOut History and to Find User Status

Purpose

To generate the report which contains Login and LogOut History of the users and to find out the user status currently.

Query 1

Below query provides the users login and logout history of the users.

SELECT au.first_name as "Technician",concat(au.FIRST_NAME,au.LAST_NAME) as "Full name", LONGTODATE(acs.OPENTIME) as "Login Time",
LONGTODATE(acs.CLOSETIME) as "Logout Time" from
aaauser au LEFT JOIN
aaalogin al on au.USER_ID = al.USER_ID
inner join aaaaccount ac on al.LOGIN_ID = ac.LOGIN_ID
left join aaaaccsession acs on ac.ACCOUNT_ID = acs.ACCOUNT_ID
ORDER BY ac.ACCOUNT_ID ;

Query 2 :

By checking the Table structure and data, can be able to find login & logout history but could not able to find status change history. Hene below query can be used to generate the users' status currently with the status changed time. Comparison of periodically scheduled reports can provide clear analysis of users status changes.

SELECT au.first_name as "Technician",concat(au.FIRST_NAME,au.LAST_NAME) as "Full name",tsd.STATUSNAME as "Current Status",LONGTODATE(tcs.EXECUTEDTIME) AS "Status changed time" from AaaAccount aaaacc INNER JOIN aaalogin aaal ON aaaacc.LOGIN_ID=aaal.LOGIN_ID INNER JOIN aaauser au on au.user_id=aaal.user_id INNER JOIN TechnicianStatus tcs on aaal.USER_ID=tcs.TECHNICIANID INNER JOIN techstatusdefinition tsd ON tcs.STATUS=tsd.STATUSID;