General
Query to show overdue tickets with delay by days
Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...
Query to show SLA assigned to tickets
Working on Builds: 14500 and above Database: PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" ...
Query to show who actually violated the SLA (PGSQL & MSSQL )
Working on Builds 14500 and above. Tested in build PGSQL (14300) and MSSQL (14306) Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", max(wo.TITLE) "Subject", max(ti.FIRST_NAME) "Current Technician", max(std.STATUSNAME) "Request Status", ...
Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT pd.PRIORITYNAME AS "Priority" , count(wo.WORKORDERID) "Total Requests", count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed", count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE ...
Query to show SLA response and resolution success percentage
Last Tested on builds 14500 Databases: PGSQL & MSSQL: SELECT pd.PRIORITYNAME AS "Priority" , case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as ...
Query report for SLA report grouped by created time- (PGSQL )
Tested in Build PGSQL (14300) Go to Reports-New Query Report and execute this query. SELECT to_char(to_timestamp(wo.CREATEDTIME/1000),'DD/MM/YYYY') "Created Time", count(wo.WORKORDERID) "Total Request", count(case when ( std.ispending='0') THEN 1 ...
Query to know SLAs configured and its details (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL: select slad.slaname "SLA NAME", sdo.name "SITE", ad.org_name "ACCOUNT", slad.duebydays || ' Days,' || slad.duebyhours || ' Hours,' || slad.duebyminutes || ' Mins' "Resolved Within", ...