Query to Pull out Login Hours

Query to Pull out Login Hours

Purpose 
      To generate the Total Login Hours per Technician. 

Query :
  1. 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 "Email", dept as "Department", LONGTODATE(act) as "Account Created At", rm as "Reporting Manager", vu as "VIP User" from ( select ac.ACCOUNT_ID as "ai", au.FIRST_NAME as "tn", CASE WHEN max(acs.CLOSETIME) is NULL THEN '-' ELSE CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)/3600000,0) AS varchar) || ' Hours ' || CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)%3600000/60000, 0) AS varchar) || ' Minutes' END as "lh", (max(acs.OPENTIME)) as "llt", al.NAME as "ln", al.DOMAINNAME as "domain", aci.EMAILID as "email", dd.DEPTNAME as "dept", (au.CREATEDTIME) as "act", repuser.FIRST_NAME as "rm", sd.ISVIPUSER as "vu" from aaauser au left join AaaUserContactInfo auci on au.USER_ID=auci.USER_ID left join AaaContactInfo aci on auci.CONTACTINFO_ID = aci.CONTACTINFO_ID inner join sduser sd on au.USER_ID=sd.USERID left join UserDepartment ud on sd.USERID = ud.USERID left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID inner join portalusers pu on sd.USERID=pu.USERID inner join PortalTechnicians pt on pu.ID = pt.ID inner 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 left join aaauser repuser on repuser.USER_ID = sd.REPORTINGTO where pu.STATUS = 'ACTIVE'  group by ac.ACCOUNT_ID, au.FIRST_NAME, al.NAME, al.DOMAINNAME, aci.EMAILID, dd.DEPTNAME, au.CREATEDTIME, sd.REPORTINGTO, sd.ISVIPUSER, repuser.FIRST_NAME order by au.FIRST_NAME) as loginhours order by tn
  2.  
Note : To Generate Login hours per Technician of only current month, Migrate to Reports > Login Reports >  Login Hours of Technicians in Current Month.

In additional, to generate the Total Login Hours per Technician between a range,
  1. 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 "Email", dept as "Department", LONGTODATE(act) as "Account Created At", rm as "Reporting Manager", vu as "VIP User" from ( select ac.ACCOUNT_ID as "ai", au.FIRST_NAME as "tn", CASE WHEN max(acs.CLOSETIME) is NULL THEN '-' ELSE CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)/3600000,0) AS varchar) || ' Hours ' || CAST(trunc(sum(CASE WHEN (acs.CLOSETIME = 0 and acs.STATUS='CLOSED') THEN 0 WHEN (acs.CLOSETIME = 0 and acs.STATUS='ACTIVE') THEN CAST(extract(epoch from now()) AS BIGINT)*1000 - acs.OPENTIME ELSE acs.CLOSETIME - acs.OPENTIME END)%3600000/60000, 0) AS varchar) || ' Minutes' END as "lh", (max(acs.OPENTIME)) as "llt", al.NAME as "ln", al.DOMAINNAME as "domain", aci.EMAILID as "email", dd.DEPTNAME as "dept", (au.CREATEDTIME) as "act", repuser.FIRST_NAME as "rm", sd.ISVIPUSER as "vu" from aaauser au left join AaaUserContactInfo auci on au.USER_ID=auci.USER_ID left join AaaContactInfo aci on auci.CONTACTINFO_ID = aci.CONTACTINFO_ID inner join sduser sd on au.USER_ID=sd.USERID left join UserDepartment ud on sd.USERID = ud.USERID left join DepartmentDefinition dd on ud.DEPTID=dd.DEPTID inner join portalusers pu on sd.USERID=pu.USERID inner join PortalTechnicians pt on pu.ID = pt.ID inner 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 left join aaauser repuser on repuser.USER_ID = sd.REPORTINGTO where pu.STATUS = 'ACTIVE' and (acs.OPENTIME between <from_lastmonth> and <to_thismonth> )  group by ac.ACCOUNT_ID, au.FIRST_NAME, al.NAME, al.DOMAINNAME, aci.EMAILID, dd.DEPTNAME, au.CREATEDTIME, sd.REPORTINGTO, sd.ISVIPUSER, repuser.FIRST_NAME order by au.FIRST_NAME) as loginhours order by tn

If you want to change the date limit, please use the template shown below

  1. Here is the example for getting this week data - acs.OPENTIME >= <from_thisweek> AND acs.OPENTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
Compatibility :
      Database : PGSQL (Tested at Build No : 13008)

        New to ADManager Plus?

          New to ADSelfService Plus?

            • Related Articles

            • Login frequency

              This report is used to find the Technician and Requester Login frequency. SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP Address", ...
            • 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 ...
            • Request created in out of business hours

              This report is used to find the request created out of business hours. Based on this report, resources can be allocated to manage the load. To make any changes to a query, refer to the KB article below. ...
            • Customized Login form

              The following steps can performed to customise the default login page with a different image background. 1. Save a new page as Custom.HTML under [ManageEngine\ServiceDesk\Custom\Login]. This will return a totally blank login page with only the login ...
            • Request not updated for 24 hours

              This report is used to find the pending request which are not updated for more than 24 hours. This report helps to find the reason why the request is pending and find out and analyze the root cause of the process and define actions to improve the ...