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;
Compatibility :
Database : PGSQL (Tested at Build No : 13008)
New to ADSelfService Plus?
Related Articles
Query to retrieve users login and logout history and find their current status
REQUIREMENT: To generate a report that contains the login and logout history of users and to determine the current user status. TESTED IN: Builds 14503 (Postgres) QUERY 1: The following query provides the login and logout history of users. SELECT ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query to Pull out Login Hours
Purpose To generate the Total Login Hours per Technician. Query : select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as "Domain", email as ...Status change from history
This report is used to find who changed the status To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT wo.WORKORDERID "Request ID", ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...