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 "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
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,
- 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
- 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
- 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 ADSelfService Plus?
Related Articles
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 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
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 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 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 ...