query reports
Query Report to show the leave details of the technicians in a report (PGSQL)
Columns : leaveid createdby createddate technician & technician id backup technician & backup technician id comments leavedate leavetype Query : SELECT tu.LEAVEID,tu.CREATEDBY, LONGTODATE(tu.CREATEDDATE) AS CREATEDDATE,tu.TECHNICIANID,au.first_name ...
Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...
Query to list Merged Requests (PGSQL)
Tested in Build : 14506 , PGSQL SELECT wo.WORKORDERID AS "Request Id", wo.TITLE AS "Subject", from_unixtime(wo.CREATEDTIME/1000) AS "Created Time", from_unixtime(wo.DUEBYTIME/1000) AS "Due Time", from_unixtime(wos.LAST_TECH_UPDATE/1000) AS ...
Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...
Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...
Query to retrieve the aging days details of requests
Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", req.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Date", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date", (FLOOR(wo.TIMESPENTONREQ / (1000 * 60 * 60 * ...
Query to retrieve worklog details
Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...
Asset Ownership History Report
Please use the below query to get the report on Asset Ownership History. SELECT r.resourcename AS "Workstation Name", ct.componenttypename AS "Product type", rs.displaystate AS "Asset State", aa.first_name AS "Assigned User", LONGTODATE(sh.starttime) ...
Query to get the Resolution overdue time and Response overdue time for each ticket.
Tested in 14620 Query: SELECT ad.ORG_NAME "Account", wo.WORKORDERID "Ticket ID", rtdef.NAME "Request Type", wo.RESOLVEDTIME "Resolved Time", pd.PRIORITYNAME "Priority", ( CASE WHEN wos.IS_FR_OVERDUE = '1' THEN ( CASE WHEN wo.RESPONDEDTIME = 0 THEN ...
Query to get technicians associated accounts, sites, groups and assigned roles.
Tested in 14620 Query: SELECT aaauser.FIRST_NAME AS "Name", aal.NAME AS "Login", aaacontact.EMAILID AS "E-Mail", deptTable.DEPTNAME AS "Department", sduser.JOBTITLE AS "Job title", STRING_AGG (arole.name, ',') "Assigned Role", (SELECT STRING_AGG ...
Query to get site's operational hour based report.
If ABC site's operational hour is 9AM to 6PM, When generating the query the report will give the request data that are created during the operational hours which is from 9 AM to 6 PM. SELECT wo.WORKORDERID AS "Request ID", lvd.LEVELNAME AS "Level", ...
Query to get the time spent in the “unassigned” status for each request before the request is assigned to a technician.
Time spent column format to DD:HH:MM:SS SELECT wo.WORKORDERID AS "Request ID", a.first_name as "Changed From",aa.first_name as "Changed To",longtodate(wot.ENDTIME) as "Changed ...
Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...
Query to find the number of follow up sent by technician
Use Case The query retrieves the count of number of conversation matching the keywords Sample Table DB: MSSQL & Postgres Query select wo.WORKORDERID "Request ID", longtodate(wo.CREATEDTIME) "Created Time", qd.QUEUENAME "Group", wo.TITLE "Subject", ...