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 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 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 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 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 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 ...