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", 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) 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
To change the date limit, please use the template shown below,
1) Example to get 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>
New to ADSelfService Plus?
Related Articles
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 list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to list account wise technicians and their respective support group details
Tested in: 14610 Compatible with: Both Postgres and MSSQL DB. Query 1: To return technicians and respective support group details from all accounts: SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support ...Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query is compatiable with version 10.6 and above: Query: Select au.FIRST_NAME "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...