Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL & MSSQL:
SELECT AaaUser.FIRST_NAME "Technician Name",
aci.emailid "Email ID",
LONGTODATE(AaaUser.createdtime) "Technician Created Time",
SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser
left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
inner JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=AaaUser.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
LEFT JOIN aaaaccount aaa on aaa.login_id=AaaLogin .login_id
WHERE (SDUser.STATUS = 'ACTIVE') group by AaaUser.FIRST_NAME, aci.emailid, sduser.status, AaaUser.createdtime
For builds greater than 10.6 please use below query:
SELECT AaaUser.FIRST_NAME "Technician Name",
aci.emailid "Email ID",
LONGTODATE(AaaUser.createdtime) "Technician Created Time",
SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser
left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
inner JOIN portaltechnicians ON SDUser.USERID=portaltechnicians.userID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=AaaUser.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
LEFT JOIN aaaaccount aaa on aaa.login_id=AaaLogin .login_id
WHERE (SDUser.STATUS = 'ACTIVE') group by AaaUser.FIRST_NAME, aci.emailid, sduser.status, AaaUser.createdtime
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 show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show time spent by each technician in a ticket (MSSQL & PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE ...