5 Replies Latest reply on Feb 15, 2011 2:07 PM by dmshimself

    Change all resolved status calls to closed, With SQL Script?

    Rookie

      Have just been told we have thousands of calls marked "resolved" and even more marked "closed", Ideally looking for a SQL script to run against the database to auto close these.

      At present our process' dont diferentiate between resolved and closed as such, but may be looking at introducing that in the future, so want to start afresh as it were...

       

      Help Appreciated

       

      Regards

       

      J

        • 1. Re: Change all resolved status calls to closed, With SQL Script?
          dmshimself ITSMMVPGroup

          Depending on the version of software you have, the 'safe' approach would be to bulk update a query with the incidents you want to close and run Close.  You could then set up a regualr schedule to do this as per the the article :- http://community.landesk.com/support/docs/DOC-6499/version/7

          1 of 1 people found this helpful
          • 2. Re: Change all resolved status calls to closed, With SQL Script?
            Rookie

            Apolgies we're using Service Desk 7.3 and yeah, I figured this would be the easiest solution....looking at the report against "resolved" however there are 60,000 records that need changing to closed, and I would imagine this would bring our the DB to a standstill...When doing a bulk action, is there a way limit the amount of records changed? I cant seem to find where I can filter or narrow the changed calls

            • 3. Re: Change all resolved status calls to closed, With SQL Script?
              dmshimself ITSMMVPGroup

              If you have 7.3, looking forward I'd use that article as it uses the background service to run the actions and you can scheudle this to occur in the wee small hours of the morning.

               

              BUT (and it's a big but!) I've found that if you have lots of items that need to be closed, the background service tends to time out.  I've fiddled with several parameters but I've never found an ideal solution, other than letting the scheduled job time out and then restarting the background service again.  It will eventually catch up and there is a flag on the scheduled job which tells you when it has finished.

               

              So initially to break the back of the work I would use a bulk closure.  It still has it's own range of issues with large numbers of items and you will need to run the query a few times, but they are manageable.  Create a special query to grab the incidents you want and add a date range criteria to limit the ones to a specific date range.  Run the bulk update in a dev environment first, just to make sure all is well.  (I have been asked by one client to re-open a number of incidents they bulk closed by acident!)

               

              You will probably find that the bulk closure gives odd errors as it gets towards the end of the list.  Ignore these, re-run the query and the numbers should be less.  Keep going.  If you do see the errors I've alluded to, I'd recommend reporting these to your local support team.

               

              Anyway, once you have the numbers down to something reasonable, kick off the scheduled job and don't worry about it again apart from one thing.  Keep an eye on the scheudled job log as it tends to grow; nothing to worry about initially, but something that needs management later on.

              • 4. Re: Change all resolved status calls to closed, With SQL Script?
                Rookie

                To the "Change Approval Board" then....wish me luck

                 

                Thanks for help

                • 5. Re: Change all resolved status calls to closed, With SQL Script?
                  dmshimself ITSMMVPGroup

                  No worries - be aware that the person closing the incidents will be the person running the bulk update from query.  It should make someones ServiceDesk stats look interesting.