Technicians
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 extract a list of technicians who haven't handled any tickets during a specific time duration (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Query: select distinct aau.user_id as "Technician User ID" , aau.FIRST_NAME as "Technician Name", ac.emailid as "Technician EmailID" from aaauser aau left join sduser sdu on aau.user_id = sdu.userid ...
Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...
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", ...
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", ...
Query to get technician details(domain, associated account, email) from builds (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT aau.first_name "First Name", sdu.lastname "Last Name", AaaContactInfo.EMAILID "Email Address", sdo.name "Associated Sites", adef.org_name "Associated Accounts", df.domainname "Domain Name" ...
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 display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...
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 technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...
Query to show Technicians' login name, email, role and last login (PGSQL & MSSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT AaaUser.FIRST_NAME "First Name", AaaUser.last_name "Last Name", adef.org_name "Account Name", sdo.name "Site Name", AaaContactInfo.EMAILID "E-mail", sduser.jobtitle, ar.name "Role", ...
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...
Query to find technicians with login enabled along with sites and account associated (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query reports and execute this query. select aau.User_id,aau.first_name "Technician Name", aal.name "Login Name", aal.Domainname "Domain",sdo.name "Associated ...
Query to show technician total tickets and SLA violated percentage. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select aaau.first_name "Tech Name", count (wo.workorderid) "Total No. of Requests/Incidents", count(case when wos.ISOVERDUE='1' THEN 1 ELSE NULL ...
Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON ...
Query to list Technician Availability Chart Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute the below query MSSQL SELECT cre.FIRST_NAME "Created By", LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date", avail.FIRST_NAME "Technician on ...
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 for disabling survey link when accessing non login url (or) requires login for filing survey
update globalconfig set paramvalue='true' where parameter like '%Requireslogin%' and category like '%survey%'
Query to show technicians and associated groups (Not working )
Doesnt work in builds from PGSQL (14300) ERROR: missing FROM-clause entry for table "helpdeskcrew" SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" ...
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 ...