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
 

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>