Query to retrieve users login and logout history and find their current status

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 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;



                  New to ADSelfService Plus?

                    • Related Articles

                    • 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 ...
                    • 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 ...
                    • To find the details about number of users logged in to the application and other related informations

                      In order to find the details about the list of users logging time to host, their logout time and current status of their connections. Please use the below query, select aas.session_id "SessionID", aal.name "User Name", aas.user_host "User Host", ...
                    • 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 find users under blocked state

                      select * from sduser sd left join CI ci on sd.ciid=ci.ciid left join AaaUserContactInfo auc on sd.userid=auc.user_id left join AaaContactInfo aci on auc.contactinfo_id=aci.contactinfo_id where sd.status='blocked'