Query to show technician created time
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
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 LEFT JOIN customreport_details custrepdet ...Query report to extract Active Technician Login and IP Address
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 Logins", LONGTODATE(max(acs.OPENTIME)) as "Last Login ...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 ...Query to show tickets created based on shift time_PGSQL
Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...Query to find the time taken to assign a field
Use case The query will pull a report of when an additional field was first assigned a value DB: MSSQL Query Please replace additional field name with the Field Name Here. SELECT "wo"."WORKORDERID" AS "Request ID", "ad"."ORG_NAME" AS "Account", ...