Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
Query:
select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", max(sd.status) "Current Status", max(deptdef.deptname) "Department Name", max(sd.jobtitle) as "Job Title", max(sdorg.name) as "Location"
from aaauser "auser"
inner join portaltechnicians "ptech" on auser.user_id = ptech.userid
left join sduser "sd" on ptech.userid = sd.userid
left join userdepartment "userdept" on sd.userid = userdept.userid
left join departmentdefinition "deptdef" on userdept.deptid = deptdef.deptid
left join sitedefinition "sitedef" on deptdef.siteid = sitedef.siteid
left join sdorganization "sdorg" on sitedef.siteid = sdorg.org_id
left join aaalogin "alog" on auser.user_id= alog.user_id
left join aaaaccount "acc" on alog.login_id = acc.login_id
left join sdauthorizedrole "auth" on acc.account_id = auth.account_id
left join aaarole "arole" on auth.role_id = arole.role_id
group by auser.user_id, auser.FIRST_NAME, auser.createdtime
New to ADSelfService Plus?
Related Articles
Query to show tickets first assigned today irrespective of created date
MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...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 ...Query to show first assigned and re-assigned ticket irrespective of the created date ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request ...Query to list technician name, site, email and login name (MSSQL & PGSQL)
The below PGSQL query doesnt work in builds below PGSQL (14000) ERROR: missing FROM-clause entry for table "helpdeskcrew" Pgsql: SELECT HelpDeskCrew.TECHNICIANID "Technician ID", AaaUser.FIRST_NAME "Technician Name",AaaContactInfo.EMAILID "Email", ...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 ...