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