Video Screencast Help
Protect Your POS Environment Against Retail Data Breaches. Learn More.
Endpoint Management Community Blog

Automatically Close Ticket After 24 Hours in Resolved Status

Created: 03 Apr 2008 • Updated: 03 Apr 2008 • 13 comments
Tenacious Geo's picture
+5 5 Votes
Login to vote

We desired for tickets that are completed by workers to be set to the Resolved status and then if the contact did not respond within 24 hours the ticket will automatically go to Closed status. Here is a simple way to do it that may be useful to Helpdesk beginners.

This can't be done with an Incident Rule within Helpdesk because those rules are evaluated when the ticket is saved. Therefore, an NS notification policy is the best place to do it. The notification policy can be run on a specific schedule to accomplish the task needed.

Here is the query I used for the notification policy called "Set Resolved to Closed after 24 hours".

SELECT  
workitem_number AS 'Ticket_Number'  
FROM  
HD_workitem_current_view  
WHERE  
workitem_status_lookup_id = '400'  
AND  
DateDiff(hh,workitem_modified_on,GetDate()) > 24

The notification policy uses an "Edit Incident Automated Action" that executes once per row, "Incident" is set to "%DS:Ticket_Number%", "Comment" is set to "Auto-close after 24-hours in Resolved status with no customer feedback.", and "Status" is set to "Closed".

This is a nice way to give your customers a window of opportunity to make sure their ticket is completed to their satisfaction before it gets all the way closed and completes its lifecycle. Enjoy!

Best regards,
George

License: AJSL
By clicking the download link below, you agree to the terms and conditions in the Altiris Juice Software License
Support: User-contributed tools on the Juice are not supported by Altiris Technical Support. If you have questions about a tool, please communicate directly with the author by visiting their profile page and clicking the 'contact' tab.

Comments 13 CommentsJump to latest comment

dkerr's picture

We have something similar in place, but we do a two phase approach. Basically, all workers "resolve" tickets, then we let the users close them through a task that is shown to them. For our notifications, we send an e-mail on setting status to resolution, then after 2 business days we send another reminder that their ticket will close if we don't hear from them. On the 3rd business day we close the ticket. To do this, we use a function we added.

create function dbo.F_GET_WORK_DAYS
	(
	@START_DATE		datetime,
	@END_DATE		datetime
	)
returns int
as
/*
Find the total workdays (Monday to Friday) between two dates,
including the start and end date.
*/

begin

declare @LAST_WEEK_START	datetime
declare	@WHOLE_WEEKS		int
declare	@WHOLE_WEEK_WORKDAYS	int
declare @LAST_WEEK_START_DW	int
declare @LAST_WEEK_DAYS		int
declare @LAST_WEEK_WORKDAYS	int
declare @WORKDAYS		int

set @WHOLE_WEEKS = (datediff(dd,@START_DATE,@END_DATE)+1)/7

set @WHOLE_WEEK_WORKDAYS =  @WHOLE_WEEKS*5

set @LAST_WEEK_START = dateadd(dd,@WHOLE_WEEKS*7,@START_DATE)

set @LAST_WEEK_START_DW = datediff(dd,'17530101',@LAST_WEEK_START)%7

set @LAST_WEEK_DAYS = datediff(dd,@LAST_WEEK_START,@END_DATE)+1

set @LAST_WEEK_WORKDAYS = @LAST_WEEK_DAYS -
	case
	when @LAST_WEEK_DAYS = 0	then 0
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 6 	then 2
	when @LAST_WEEK_START_DW = 0 and @LAST_WEEK_DAYS > 5 	then 1
	when @LAST_WEEK_START_DW = 0 				then 0
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 5 	then 2
	when @LAST_WEEK_START_DW = 1 and @LAST_WEEK_DAYS > 4 	then 1
	when @LAST_WEEK_START_DW = 1 				then 0
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 4 	then 2
	when @LAST_WEEK_START_DW = 2 and @LAST_WEEK_DAYS > 3 	then 1
	when @LAST_WEEK_START_DW = 2 				then 0
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 3 	then 2
	when @LAST_WEEK_START_DW = 3 and @LAST_WEEK_DAYS > 2 	then 1
	when @LAST_WEEK_START_DW = 3 				then 0
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 2 	then 2
	when @LAST_WEEK_START_DW = 4 and @LAST_WEEK_DAYS > 1 	then 1
	when @LAST_WEEK_START_DW = 4 				then 0
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 1 	then 2
	when @LAST_WEEK_START_DW = 5 and @LAST_WEEK_DAYS > 0 	then 1
	when @LAST_WEEK_START_DW = 5 				then 0
	when @LAST_WEEK_START_DW = 6 and @LAST_WEEK_DAYS > 6 	then 2
	else 1
	end

set @WORKDAYS = @WHOLE_WEEK_WORKDAYS + @LAST_WEEK_WORKDAYS

return @WORKDAYS

end

We put this in the Altiris database. It basically is used to compute time between two dates. Our Notification Policy query looks like this:

select  *   
from dbo.hd_workitem_current_view    
where dbo.f_get_work_days(workitem_modified_on, GetDate()) >2   
and workitem_status_lookup_value like 'Resolved'   
			

As you can see it basically passed the modified on date as well as what the current time is and looks for any value greater then 2. This allows us to utilize business days for computations.

+2
Login to vote
dfnkt_'s picture

I am continually impressed by the extensibility of the Helpdesk solution.

0
Login to vote
johnquinn's picture

I'm really a Noob (to use a World of Warcraft term)when it comes to SQL and stuff.

I have like this in place already, but I have it closing the tickets outright after 72 hours. This works OK, but I would much rather use the business day approach.

I see your function, but I was wondering where do I put it to use it.

+2
Login to vote
jongolf07's picture

I have a couple of questions regarding the add function inserted in the database. First are there concerns with future upgrades. We are hesitant when modifying code in the database.
Secondly, can this be modified to use hours instead of days. The reason for the second question is we are looking at escalation. After XX amount of hours we are wanting to escalate an incident. If we use normal business hours. M - F 8-5, a ticket can be placed on Friday at 4pm and come Monday at 8am when the rule runs, it gets escalated because it calculated in the weekend time. Any thoughts on this?
Thanks,

0
Login to vote
johnquinn's picture

I've been toying around with doing this very idea as I begin to build our formal helpdesk. I wanted to have the workers Resolve rather than close the tickets.

I had gotten as far as generating a notification to the user that the ticket had been resolved but the initial approach I was taking was to have 2 links in the e-mail; one for the user to confirm the issue is resolved and close the ticket and the other to indicate it was not yet resolved.

I got the second working even to the point it would go back to the guest console and allow the user to post new comments and then when they submitted, the ticket went back to Open status.

I was having trouble getting the outright close the ticket one to work how I liked, but upon seeing this approach of auto closure, it makes more sense and will be easier to implement.

I will certainly use both of these to set this up.

Good job.

+2
Login to vote
dfnkt_'s picture

Nice George! I'm sure a lot of people had this on their radar but hadn't dug into it yet. Good to see some SQL..I am strangely fond of it for some reason, I think it's because of its power *evil grin*

+4
Login to vote
Tenacious Geo's picture

*George joins in unison with an evil SQL grin accompanied by a maniacal laugh by running the following statement in his brain*

SELECT *
FROM dbo.laugh_lookup
WHERE laugh_lookup_value = "maniacal"

Results:
"MUAHAHAHAHAHAHA!!!"

We should start the EVIL SQL SOCIETY ENCLAVE (ESSE). Then we can start the VERY AMAZING TRANSACTSQL ORGANIZATION (VATO). Together, ESSE VATO will be unstoppable!!!

-Geo

-4
Login to vote
seema's picture

Sir/Mam
It is very very user full for me. but I am facing same problem with that.

I was import this attachment. Edit Resolved status is 550 in the imported query and apply. it working also but suddenly it automatically stop after 5 days .Again delete and installed the same but still is am not using this . pls pls help me

-2
Login to vote
pwilson's picture

I use the auto-close Task, but everytime it runs it replaces the title of the original ticket with "Set Resolved to Closed after 24 hours". How do i get the ticket to auto-close and retain its original title?

+2
Login to vote
Tenacious Geo's picture

This rule only modifies the comment and status fo the ticket, so maybe you accidentally set it to edit the title as well?

-Geo

-2
Login to vote
Oscar2564's picture

George,

You saved my bacon. I used your query and it work great for me. I created a 24 hour and 48 hour to auto-close waiting and resolved incidents.

Thanks again,

Oscar

Oscar H. Castañeda
Sr. Solutions Architect
XCEND Group Inc. -- Symantec Platinum Partner

-2
Login to vote
Tenacious Geo's picture

Oscar, great to hear your bacon is safe! :D

Feel free to thumbs-up the original post if you are so inclined. The points are nice to spend on Amazon! ;-)

-Geo

0
Login to vote