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 ADManager Plus?

          New to ADSelfService Plus?

            • Related Articles

            • 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'
            • Query to find status change, its time and comments (MSSQL & PGSQL)

              Tested in Build PGSQL (14300) or MSSQL (14306) Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request ...
            • Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)

              Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...
            • Query to get the Login Hours of Technicians (Postgres)

              Tested in build PGSQL (14300) 1) To generate the total login hours per technician 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", ...
            • Query to show status changes in a ticket_ PGSQL

              Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", woh.OPERATION "Operation", LONGTODATE(wo.CREATEDTIME) CREATEDTIME, aau1.FIRST_NAME PERFORMEDBY, ...