Auto close request and send requestor notification if X status for X days
Hi,
I've looked at a few different methods but haven't found anything that is exactly what i need. I'm looking for a way to check every day for all requests with a status of 'HOLD - waiting on user to respond". If the status is over 10 days old and the support group is 'HELPDESK'. I t should then email the requester letting them know that they did not respond and the request was closed.
I have this query that I think is pretty close:
SELECT wo.WORKORDERID AS "Request ID" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID = woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID = qd.QUEUEID
WHERE ((std.STATUSNAME = 'HOLD - Waiting on End User to Respond'))
AND wo.ISPARENT='1'
AND qd.QUEUENAME = 'Helpdesk'
and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wos.last_tech_update/1000),'1970-01-01 00:00:00'),GETDATE()) > 10
This gets me the correct request ID. But I assume I will also need requestor email? How do I get that and have it send the notification to the requestor?
And then I put this into a deluge script like this. Can't seem to find a sample script that also contains the notification email. Or is there a better method, such as a python script?
But the above doesn't look like it handles the requestor notification. Or maybe there is an easier method?