Custom Query-Report for "Close Comments" not working anymore with 7.6 Build 7608
Dear ServiceDesk Plus-Team,
Some time ago, you provided me a custom Query to use for reporting the "Close Comments" written by the Users when they closed an Incident. This Query worked well in our ServiceDesk Plus installation of Version 7.0 Build 7022.
However, we have a new SD+ installation in use now, with the most recent
Version 7.6 Build 7608. Further, we moved to new Database to
MSSQL rather than MySQL. Unfortunately, now the provided Custom Query does not work anymore.
I'd appreciate, if you could provide me an update of below Query, that will work again in the newest Version of SD+ and with a MSSQL database. Thanks!
- SELECT DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000), '%d.%m.%Y %k:%i') "Created", wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", wos .close_comments "Close Comment", ti.FIRST_NAME "Technician" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (std.STATUSNAME = 'Closed') AND (wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_lastweek>) AND wot.THD_WOID=wot.WORKORDERID AND (wos.close_comments <> 'Aged Request was Closed.' AND wos.close_comments <> '')