QUERY 1:
The following query provides the login and logout history of 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 :
The following query can be used to generate the current status of users along with the time of status change. Regularly scheduled reports can be compared to provide a clear analysis of user 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;