Automatically closing resolved calls after x days

Ok, so let’s say that you’ve implemented ITIL in your organisation and one of the requirements is that incidents are RESOLVED by the Service Desk and then CLOSED by the client. This is easy enough to implement in Service Desk Express by just creating a new User-Defined Status of RESOLVED that is flagged to “Stop The Clock” as shown below. (Incidentally, you need to create a custom version of the User-Defined Status form to add the “Stop The Clock” tick box to it.)

New Resolved Status

New Resolved Status

The issue however, is that your clients are not closing their calls and consequently management want the RESOLVED calls to be CLOSED automatically after 2 days of inactivity. This is possible to do with business rules BUT it’s horrible because your job queue fills up with all the tickets that are awaiting closure and as such, identifying failed rules becomes a nightmare. So, an alternative solution.

If you are using Service Desk Express 9.1 or above then salvation is here with the introduction of the Integration Engine. In this post I am going to use the Integration Engine to build a package that will do what we’ve mentioned above:

So we fire up the Integration Engine (http://appservername/integrationconsole by default) and add a new package called Close Resolved Calls. We then create a single step called the same thing as shown below:

Closing Resolved Calls 01

Closing Resolved Calls 01

When then customise the step as follows:

Initiator:

We use the Scheduler initiator as we want this function to run in the background without human intervention. In this example I have set it to run every hour starting at midnight.

Closing Resolved Calls 02

Closing Resolved Calls 02

Source:

Now this is where it gets interesting as we need to specify a query that is going to get us all calls that are RESOLVED (I’ve added this as a User-Defined Status), which means they are open at the moment, that haven’t had any activity in the last two days. I am defining inactivity here as no Incident Details records created. The query to do this is as follows:

SELECT I.[Incident #] FROM [_SMDBA_].[Incident] I WHERE (SELECT Top 1 DATEDIFF(dd, ID.[Date], GETDATE()) FROM [_SMDBA_].[Incident Details] ID WHERE ID.[Incident #] = I.[Incident #] ORDER BY ID.[Date] DESC) > 2 AND I.[Status ID:] = 'RESOLVED' ORDER BY I.[Incident #]

As we want to run a specific query we need to use an ODBC source as opposed to SDE (or at least I find it easier than using the filter option).

Closing Resolved Calls 03Closing Resolved Calls 04Closing Resolved Calls 05Closing Resolved Calls 06

Target:

So now we have a source of all the incidents that are open, with a status of RESOLVED, and no incident details in the last 2 days. Now we specify the Target which in this case is the Incident table in Service Desk Express. Notice how the Insert/Update method is set to Update only, the business rules are being used, and we are updating on Incident #.

Closing Resolved Calls 07Closing Resolved Calls 08Closing Resolved Calls 09Closing Resolved Calls 10

Closing Resolved Calls 11

Closing Resolved Calls 11

Mapping:

So finally, all we need to do is map the [Incident #] together and hard-code the [Status ID:] = ‘CLOSED’.

Closing Resolved Calls 12

Closing Resolved Calls 12

And that’s it. Every hour it will pick up any calls that meet the criteria (incidents that are open, with a status of RESOLVED, and no incident details in the last 2 days) and it will close them. If you have a business rule that fires on update of an incident to a status of CLOSED then this will automatically fire as well. This is another huge plus of the Integration Engine.

You can download the final package here.

As always, keep the feedback coming (whether it be positive or negative). Hope it was useful.

Advertisements

17 thoughts on “Automatically closing resolved calls after x days

  1. Great article. Thanks for the tip. I am using this idea, but I also want to create a sub-step that will create an Incident Detail record for the record that is being closed. How do I do this? When I get to the “Mapping” section in the substep, I cant find out how to map the Incident number to the Incident number returned from my ODBC connection. I only see “ExecuteWhen” in the Source Column. Do you have any experience doing this?

  2. Another thing I noticed was that this SQL Query doesnt subtract weekends, so that means that If I “RESOLVE” a ticket on Friday evening, it can be automatically closed via the Integration Package on Monday, instead of not-counting the weekend and closing it on Wednesday (3 business days).

  3. Hi Ryan,

    All you need to do in the Mapping section is to select Parent Source from the drop down list underneath the word Source Column and then you should see Incident #. Hope this makes sense.

    Regards,

    Alan

  4. Hi Ryan,

    If you wanted to do that you would need to change the SQL to something like:

    SELECT I.[Incident #] FROM [_SMDBA_].[Incident] I WHERE (SELECT Top 1 DATEDIFF(dd, ID.[Date], GETDATE()) – (DATEDIFF(ww, ID.[Date], GETDATE())*2) FROM [_SMDBA_].[Incident Details] ID WHERE ID.[Incident #] = I.[Incident #] ORDER BY ID.[Date] DESC) > 3 AND I.[Status ID:] = ‘RESOLVED’ ORDER BY I.[Incident #]

    The only caveat with this is it won’t work if the last incident detail is on a weekend so you need to be a bit careful with it.

    Regards,

    Alan

  5. OK. That makes sense. Would there be a better way to write the query so that it can use my Work Schedule (which excludes weekends)? (Much like you can choose a Work Schedule in a business rule)

  6. Alan,
    one thing not covered in your post is how you go about resolving the incident – you can’t use the close dialog box because Resolved is not available and the form is not customisable. We are trying to only make the resolution field updateable on resolve in an effort to improve ticket data integrity. Would a CSBR that changes the incident form on select of Resolved status be the way to go?
    cheers,
    Simon

  7. Hi Simon,

    Again, apologies for the tardy response. In this scenario you need to make the Status field NOT read-only so that users can change it to Resolved. You can then have a CSBR that on change of Status, with a new status of Resolved, show the Incident Resolution field.

    Regards,

    Alan

  8. Alan,
    Thanks for the feedback. A further problem with using the Integration Engine to close incidnets is that surveys are not being created. Have you come accross this at all?
    Cheers,
    Simon (thommo)

  9. Hi Simon,

    Just to check the obvious but have you ticked the box in the Target Adapter to Use Business Rules? If you have and it still doesn’t work there is nothing to stop you creating a business rule that on closure of incident create an Activated Survey.

    Hope this helps.

    Regards,

    Alan

  10. Hi Alan,
    yes the Use Business Rules checkbox is checked. A little bit concerned on the use of a business rule that we could end up doubling up on surveys.
    Any ideas on the conditions of the rule would be appreciated.
    Cheers,
    Simon (thommo)

  11. Hi Simon,

    Under what conditions do you normally survey? Essentially you are just going to replicate this BUT only when the incident is closed by the account the Integration Engine uses e.g. SYSTEMACCOUNT. Does that make sense.

    Regards,

    Alan

  12. Hi Alan,
    Yes it makes sense, I guess the business rule could be the interim solution while we fix whatever it is that is causing the out of the box survey functionality not to trigger. We survey 100% of incidents closed by certain groups. There are two surveys, one is for a specific group. The other survey is for a bunch of groups. Then there are a number of groups that have no survey associated. I was thinking we look for Incident Update, Closed By = Integration Engine User, where sequence survey associated with assigned to group is not NULL. Having a bit of trouble crafting the dblookup for this last bit though.
    Cheers,
    Simon

  13. Hi Simon,

    You shouldn’t have to look at the Incident Update as there is a Closed By field in the Incident View and that would make the DB Lookup much easier. If you do have to use it then consider a MATH(SELECT…) as it might make the db lookup a little easier.

    If you still having trouble let me know and when I get back from Denmark I will see what I can do to help.

    Regards,

    Alan

  14. Dear Alan,
    Great work! Can you post also the Oracle “translation” of your SQL code please? I have tried to adapt your code but I am stuck at “Top 1” command.

    Kind regards,
    Lucian

    • @Lucian…

      I am no Oracle expert…in fact the number of Oracle queries I have ever written you could count on both hands 🙂 However, having looked on Google (really not quite sure what we did before that) I believe I may have a query that should work. However, for whatever reason, when I try and paste it here WordPress wont allow me to save it :-(. I will drop you an email with the query and you can let me know if it works.

      Regards,

      Alan

  15. Hi,

    We have an information field.

    I know IE will allow some simple string manipulation on source field.

    Can you tell me how I would add an information line to the information string. Something like this:

    Information + Char(13) + Char(10) + “Closed”

  16. Dear Alan,

    Went through your post following is my environment

    BMC SDE v10 with cummalative patch 282.

    I am not able to see the field “Stop Clock” as mentioned in your screenshot on the Status from List administration. Can you please let me know what can i do to view this filed ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s